Excel spreadsheet file used in video: Right-click to download file
The fill handle can be used to copy or increment values in cells. When a cell containing text or a number is selected, the contents can be copied to adjacent cells by dragging the fill handle. The fill handle is a small square located at the bottom right corner of a selection.
When the mouse pointer is over the fill handle it changes to a black cross:
When the black cross appears, drag the mouse down or across to copy the contents to adjacent cells.
When a cell containing a time, date, month, day of the week or text mixed with a number is copied with the fill handle the values will be incremented each cell:
When using the fill handle to fill dates other options are available after draging the fill handle by clicking the Auto Fill options button which appears next to the fill handle:
Then choose Fill Weekdays to have only weekdays appear. Choose Fill Months or Fill Years to have the dates increment by months or years.
If you do not want a value in the cells to be incremented by the Fill handle click the Auto fill options button. Then choose Copy cells:
To increment a number typed into a cell. Select the cell then drag the fill handle down or across. Click the Auto fill options button and choose Fill Series:
To increment a number by more than one, type the first number in a cell then the first increment an adjacent cell, drag and highlight the 2 cells, then drag the fill handle to increment.
Flash fill is an easy way to extract parts of data in a cell into other cells. For example if you had a list of names in the first column and you wanted the first names in the next column. Type the first name into the column when you type the next name into the second column a pop up will appear with all the first names from the column.
press Enter to fill in the column with the first names.
Numbers and dates can also be extracted. Sometimes you need to establish a pattern as with dates formatted without any separators:
Then press Ctrl E And excel will attempt to fill the cells:
If it can't establish a pattern, an error message will appear.
Another way to fill cells With numbersis to use the Series fill dialog. For dates first type a starting value for the fill into a cell and press Ctrl Enter. Bring up the Series Fill dialog. In the home ribbon click Fill then Series or use the shortcut keys: hold down Alt and press H F I S
Choose to have it fill the series in Rows or Columns.
In the step value choose the increment for each step of the fill, for example a 2 here will increase the starting
value by 2 each time.
Then in the stop value choose the maximum value to be reached.
The default fill is Linear, the numbers will be filled by adding the step value each time.
If Growth is chosen the series will be filled by multiplying the step value each time. .
For Dates the using the fill handle is much easier than the using the Series fill.