Nov 6, 2018 - Learn how to copy data to multiple cells in Excel using the keyboard, the Fill. To Excel versions 2019, 2016, 2013, 2010, Excel Online, and Excel for Mac. Click and hold the fill handle in the bottom right corner of the cell that.
| | InformationHelpful? Why Not Donate.
Got any Excel Questions? Excel Help
FillHandle | also see Creating a Custom List
The Fill Handle in Excel ispossibly one of Excels most under utilized features. For those of you that don'tknow of it, it's the small black square in the bottom right of the activecell.
In it's simplest form it will increment any series of numbers. For example, ifyou type the number 1 in any cell and then the number 2 in a cell that adjoinsit, you can use the Fill Handle to increment up to any number desired. To dothis you simply select you two cells (Starting from the one with the number 1)and then hover your mouse pointer over the Fill Handle (until it changes to asmall black cross), left click and drag in the direction you want theincremented numbers to show. You can also do the same by entering any Startingnumber in any cell, selecting the cell, holding down the Ctrl key and thendragging down with the Fill Handle. If you do not hold down the Ctrl key Excelwill simply copy the same number.
The best bit about this feature is that we canalter the amount we increment by simply typing any two numbers we want eg 5 and10 would result in 5, 10, 15, 20, 25 etc. If you wanted a blank cell betweeneach number simply use the method below:
Type 5 in cell A1
Leave A2 blank
Type 10 in cell A3
Leave A4 blank
Select cells A1:A4
Drag down using the Fill Handle
The exact same principle applies todates, after alldates in Excelare only numbers (Serial Values).
The other thing that you can dowith the Fill Handle is drag it up or to the left to clear the contents of selectedcell(s). You can even insert or delete rows or columns by holding down the SHIFTkey while dragging the fill handle.
Lets assume you have a column ofdata in cells A1:A500 and you place a formula into cell B1. Normally you wouldcopy and paste the formula down to row 500, but instead of this try doubleclicking the Fill Handle. The whole thing is done for you and will stop atthe first blank cell in column A! The same thing happens if you put twodifferent numbers in cells B1 and B2, select both cells then double click theFill Handle. The other little known feature is Excels pop-up Fill menu you get whenyou right click on the Fill Handle and drag. Try this.
Type any number in any cell
Select the cell
Right click on the Fill Handle
Drag down and then release
Select Fill Series
By using this method you eliminatethe need for the secondary number. Now repeat steps 1 to 4 and for step 5 selectSeriesinstead of Fill Series. The option here are:
Series in: Determines whether the series is filled across selected rows or down selected columns. The contents of the first cell or cells in each row or column of the selection are used as the Starting values for the series.
Type (Linearor Growth): Creates a growth series or geometric growth trend. If the Trend box is cleared, a series is calculated by multiplying the value in the Step value box by each cell value in turn.If the Trend box is selected, the value in the Step value box is ignored, and a geometric growth trend is calculated based on the selected values. The selected original values are replaced with values that fit the trend.
Type (Date):Fills a series with dates. The type of date series that is incremented depends on the option selected under Date unit.Date unit is only available when working with dates.
Type(AutoFill):Fills blank cells in a selection with a series based on data included in the selection. Selecting this option produces the same results as dragging the fill handle to fill a series. Any value in the Step valuebox and any selected Date unit option are ignored.
Date unit: Specifies whether a series of dates will increase by days, weekdays, months, or years. Available only when creating a date series.
Trend: Calculates a best-fit line (for linear series) or geometric curve (for growth series). The step values for the trend are calculated from the existing values at the top or left of the selection. Any value in the Step value box is ignored if the Trend check box is selected.
Set value: Enter a positive or negative number to indicate the amount by which you want a series to increase or decrease.
Stop value: Enter a positive or negative number to indicate the value at which you want the series to end. If the selection is filled before the series reaches the stop value, the series stops at that point. If the selection is larger than needed to fill the series, the remaining cells of the selection are left blank. You do not need a value in the Stop value box to fill a series.
As you can see this option allowsmany choices and is very useful for incrementing dates! The best way byfar to familiarize yourself with this feature is to jump straight in and have ago. You may also have noticed when we right clicked the Fill Handle and dragged wehad many other options available on the Pop-up menu. The Fill day,Fill months etc will only be available if the cell(s) contain a date. Butyou can also Copy cells, Fill Values and Fill formats. Thiscan be particularly useful, but there is another Pop-up menu that is better forthis.
Type any formula into any cell
Right click on the cell border(not the Fill Handle)
Drag down then release.
As you will see you now have 10options to choose from. Some of these are short-cut methods of the Paste Specialfeature. If you hold down the Alt key while dragging you can changesheets by hovering over the sheet name tab! I find the Copy here as values onlyparticularly useful whenconverting formulas to permanent values. Try this example:
Type any number of formulasinto any adjoining cells
Select all these cells and right click on the border.
Now drag down just one row
With the right mouse buttonstill held down drag back up one row to where you Start ed
Now release and choose Copyhere as values only
This method is far quicker andeasier than using Edit>PasteSpecial-Values!
To find out what the other optionsdo, just jump right in and try them.
Special! FreeChoice of Complete Excel Training CourseOR Excel Add-insCollectionon all purchases totaling over $64.00. ALLpurchasestotaling over $150.00 gets you BOTH! Purchases MUST bemade via this site. Send payment proof to [email protected] 31 daysafter purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below... Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA| Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel| MSSQL MigrationToolkit |Monte Carlo Add-in |Excel Costing Templates
Whenever you work with Excel, you'll enter information—or content—into cells. Cells are the basic building blocks of a worksheet. You'll need to learn the basics of cells and cell content to calculate, analyze, and organize data in Excel.
Optional: Download our practice workbook.
Understanding cells
Every worksheet is made up of thousands of rectangles, which are called cells. A cell is the intersection of a row and a column. Columns are identified by letters(A, B, C), while rows are identified by numbers (1, 2, 3).
Each cell has its own name—or cell address—based on its column and row. In this example, the selected cell intersects column C and row 5, so the cell address is C5. The cell address will also appear in the Name box. Note that a cell's column and row headings are highlighted when the cell is selected.
Cell C5
You can also select multiple cells at the same time. A group of cells is known as a cell range. Rather than a single cell address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, separated by a colon. For example, a cell range that included cells A1, A2, A3, A4, and A5 would be written as A1:A5.
In the images below, two different cell ranges are selected:
Cell range A1:A8
Cell range A1:B8
Cell range A1:B8
If the columns in your spreadsheet are labeled with numbers instead of letters, you'll need to change the default reference style for Excel. Review our Extra on What are Reference Styles? to learn how.
To select a cell:
To input or edit cell content, you'll first need to select the cell.
Click a cell to select it.
A border will appear around the selected cell, and the column heading and row heading will be highlighted. The cell will remain selected until you click another cell in the worksheet.
You can also select cells using the arrow keys on your keyboard.
To select a cell range:
Sometimes you may want to select a larger group of cells, or a cell range.
Click, hold, and drag the mouse until all of the adjoiningcells you want to select are highlighted.
Release the mouse to select the desired cell range. The cells will remain selected until you click another cell in the worksheet.
Selecting a cell range
Cell content
Any information you enter into a spreadsheet will be stored in a cell. Each cell can contain different types of content, including text, formatting, formulas, and functions.
Text Cells can contain text, such as letters, numbers, and dates.
Formatting attributes Cells can contain formatting attributes that change the way letters, numbers, and dates are displayed. For example, percentages can appear as 0.15 or 15%. You can even change a cell's background color.
Cell formatting
Formulas and functions Cells can contain formulas and functions that calculate cell values. In our example, SUM(B2:B8) adds the value of each cell in cell range B2:B8 and displays the total in cell B9.
To insert content:
Click a cell to select it.
Selecting cell A1
Type content into the selected cell, then press Enter on your keyboard. The content will appear in the cell and the formulabar. You can also input and edit cell content in the formula bar.
To delete cell content:
Select the cell with content you want to delete.
Selecting a cell
Press the Delete or Backspace key on your keyboard. The cell's contents will be deleted.
You can use the Delete key on your keyboard to delete content from multiple cells at once. The Backspace key will only delete one cell at a time.
To delete cells:
There is an important difference between deletingthe content of a cell and deleting the cell itself. If you delete the entire cell, the cells below it will shiftup and replace the deleted cells.
Select the cell(s) you want to delete.
Selecting a cell to delete
Select the Delete command from the Home tab on the Ribbon.
The cells below will shiftup.
Cells shifted to replace the deleted cell
To copy and paste cell content:
Excel allows you to copy content that is already entered into your spreadsheet and paste that content to other cells, which can save you time and effort.
Select the cell(s) you want to copy.
Click the Copy command on the Home tab, or press Ctrl+C on your keyboard.
Clicking the Copy command
Select the cell(s) where you want to paste the content. The copied cells will now have a dashed box around them.
Click the Paste command on the Home tab, or press Ctrl+V on your keyboard.
Clicking the Paste command
The content will be pasted into the selected cells.
To cut and paste cell content:
Unlike copying and pasting, which duplicates cell content, cutting allows you to move content between cells.
Select the cell(s) you want to cut.
Selecting a cell range to cut
Click the Cut command on the Home tab, or press Ctrl+X on your keyboard.
Select the cells where you want to paste the content. The cut cells will now have a dashed box around them.
Pasting cells
Click the Paste command on the Home tab, or press Ctrl+V on your keyboard.
The cut content will be removed from the original cells and pasted into the selected cells.
The cut and pasted cells
To access more paste options:
You can also access additional paste options, which are especially convenient when working with cells that contain formulas or formatting.
To access more paste options, click the drop-down arrow on the Paste command.
Rather than choose commands from the Ribbon, you can access commands quickly by right-clicking. Simply select the cell(s) you want to format, then right-click the mouse. A drop-down menu will appear, where you'll find several commands that are also located on the Ribbon.
Right-clicking to access formatting options
To drag and drop cells:
Rather than cutting, copying, and pasting, you can drag and drop cells to move their contents.
Select the cell(s) you want to move.
Hover the mouse over the border of the selected cell(s) until the cursor changes from a white cross to a black cross with four arrows.
Click, hold, and drag the cells to the desiredlocation.
Dragging the selected cells
Release the mouse, and the cells will be dropped in the selected location.
To use the fill handle:
There may be times when you need to copy the content of one cell to several other cells in your worksheet. You could copy and paste the content into each cell, but this method would be time consuming. Instead, you can use the fill handle to quickly copy and paste content to adjacentcells in the same row or column.
Select the cell(s) containing the content you want to use. The fill handle will appear as a small square in the bottom-right corner of the selected cell(s).
Locating the fill handle
Click, hold, and drag the fill handle until all of the cells you want to fill are selected.
Release the mouse to fill the selected cells.
The filled cells
To continue a series with the fill handle:
The fill handle can also be used to continuea series. Whenever the content of a row or column follows a sequential order, like numbers(1, 2, 3) or days(Monday, Tuesday, Wednesday), the fill handle can guess what should come next in the series. In many cases, you may need to select multiple cells before using the fill handle to help Excel determine the series order. In our example below, the fill handle is used to extend a series of dates in a column.
The extended series
You can also double-click the fill handle instead of clicking and dragging. This can be useful with larger spreadsheets, where clicking and dragging may be awkward.
Watch the video below to see an example of double-clicking the fill handle.
To use Flash Fill:
A new feature in Excel 2013, Flash Fill can enter data automatically into your worksheet, saving you time and effort. Just like the fill handle, Flash Fill can guess what type of information you're entering into your worksheet. In the example below, we'll use Flash Fill to create a list of first names using a list of existing email addresses.
Enter the desired information into your worksheet. A Flash Fill preview will appear below the selected cell whenever Flash Fill is available.
Previewing Flash Fill data
Press Enter. The Flash Fill data will be added to the worksheet.
To modify or undo Flash Fill, click the Flash Fill button next to recently added Flash Fill data.
Clicking the Flash Fill button
Find and Replace
When working with a lot of data in Excel, it can be difficult and time consuming to locate specific information. You can easily search your workbook using the Find feature, which also allows you to modify content using the Replace feature.
To find content:
In our example, we'll use the Find command to locate a specific name in a long list of employees.
From the Home tab, click the Find and Select command, then select Find... from the drop-down menu.
The Find and Replace dialog box will appear. Enter the content you want to find. In our example, we'll type the employee's name.
Click Find Next. If the content is found, the cell containing that content will be selected.
Clicking Find Next
Click Find Next to find further instances or Find All to see every instance of the search term.
When you are finished, click Close to exit the Find and Replace dialog box.
Closing the Find and Replace dialog box
You can also access the Find command by pressing Ctrl+F on your keyboard.
Click Options to see advanced search criteria in the Find and Replace dialog box.
To replace cell content:
At times, you may discover that you've repeatedly made a mistake throughout your workbook (such as misspelling someone's name), or that you need to exchange a particular word or phrase for another. You can use Excel's Find and Replace feature to make quick revisions. In our example, we'll use Find and Replace to correct a list of email addresses.
From the Home tab, click the Find and Select command, then select Replace... from the drop-down menu.
Clicking the Replace command
The Find and Replace dialog box will appear. Type the text you want to find in the Find what: field.
Type the text you want to replace it with in the Replace with: field, then click Find Next.
If the content is found, the cell containing that content will be selected.
Review the text to make sure you want to replace it.
If you want to replace it, select one of the replace options:
Replace will replace individual instances.
Replace All will replace every instance of the text throughout the workbook. In our example, we'll choose this option to save time.
Replacing the highlighted text
A dialog box will appear, confirming the number of replacements made. Click OK to continue.
The selected cell content will be replaced.
The replaced content
When you are finished, click Close to exit the Find and Replace dialog box.
Challenge!
Open an existing Excel 2013 workbook. If you want, you can use our practice workbook.
Select cell D3. Notice how the celladdress appears in the Name box and its content appears in both the cell and the Formulabar.
Select a cell, and try inserting text and numbers.
Delete a cell, and note how the cells below shift up to fill in its place.
Cut cells and paste them into a different location. If you are using the example, cut cells D4:D6 and paste them to E4:E6.
Try dragging and dropping some cells to other parts of the worksheet.
Use the fill handle to fill in data to adjoining cells both vertically and horizontally. If you are using the example, use the fill handle to continue the series of dates across row 3.
Use the Find feature to locate content in your workbook. If you are using the example, type the name Lewis into the Find what: field.