Advance Spreadsheet Skills
LESSON: USING THE FILL HANDLE
LEVEL: BEGINNER
CONTENTS:
10 Using the Fill Handle to Copy Values
19 Using Auto Fill with Number Series
34 Using Auto Fill with Dates
48 Creating Custom Auto Fill Lists
56 Using the Fill Handle with Formulae
61 More Auto Fill Options
The fill handle is a quick and easy way to copy data
and formulas across cells.
This presentation shows how to use the fill handle to
copy values across cells, and how to auto fill
number series, dates, times, formulas, functions and
formatting.
By the end of this presentation you can:
Fill values quickly using the fill handle and editing ribbon
Use auto fill to extend number, date, time and other patterns
Use other auto fill options to fill formatting to other cells
When a cell or range is selected, a small box appears in the
bottom-right corner. This is called the fill handle.
Clicking and dragging the fill handle will fill the cell’s value (or
extend the pattern in the cell/range) into other cells.
As you drag, the pop-up displays the value that will be filled into
each cell:
If Excel doesn’t recognise any pattern in your data, it will simply
copy the contents into the other cells:
If you double-click the fill handle, Excel will fill the cell’s contents
down to the bottom of the current block of data:
You can also fill a value downwards by selecting the original cell
along with the desired range below and pressing CTRL + D.
The same can be done filling right using CTRL + R.
You can also fill left or up using the Fill command button in the
Editing tab.
Dragging the fill handle back into the selected range will clear the
values from those cells:
Using Auto Fill with Number Series
When using the fill handle, a feature called auto fill will try to
recognise a pattern or trend in your data, and extend this into the
For example, the pop-up shows that Excel is continuing the linear
trend (1, 2, 3, … ) of our selected range:
The linear trend can be whatever we wish
… and can be going up or down.
A number series could exhibit either a linear trend (i.e. 1,2,3,4,5…)
or a growth trend (i.e. 1,2,4,8,16…)
By default, the fill handle extends the linear trend.
To fill in a growth trend instead, click the fill handle with the right
button and drag:
When you release the mouse button, you see the option to use a
growth trend instead of a linear trend:
The result is a series which grows in the same ratio as the original
number:
You can use any growth ratio you want (this example multiplies
each number by 0.5).
Clicking and dragging with the right mouse button also allows us
to bring up the series dialog box:
This has an option to set a stop-value for your series.
i.e. this series increases by 1 each time, but stops at 5:
Cells which would otherwise occur after the stop-value are left
blank:
Auto fill can also identify number patterns that occur along with
other text in a cell:
Using Auto Fill with Dates
Auto fill can recognise patterns in date series.
By default, the fill handle increases each date by one day:
This is useful for quickly entering a list of dates one day apart.
Click and drag the auto fill handle with the right mouse button to
see other fill options:
Filling ‘Weekdays’ will skip any weekend days in the series:
‘Fill Months’ will extend the series by changing the month instead
of day:
And similarly for ‘Fill Years’:
Auto fill tries to identify a date pattern in the original series and
extends this (i.e. the pattern above is the 5th day of every month):
Where no specific pattern is found, auto fill just extends the series
with the same gap between dates as in our original series.
i.e. auto fill extends the above date series 19 days apart.
Differences of 7, 14 days are useful for entering weeks, fortnights
Auto fill can also recognise patterns in weekdays,
… and separate months,
… and ranks,
… and times.
Creating Custom Auto Fill Lists
What if you commonly use data in lists which are not recognised
by Excel?
For example if our company has offices in 10 cities and we enter
these into Excel frequently, then It might help to use a custom
To enter a custom list, go to the File tab, then:
Options -> Advanced -> Edit Custom Lists
We can import our custom list from existing entries, then click
import to enter the list:
Otherwise we can enter the list manually then click ‘Add’.
Once the list has been added by either method, it appears as a
custom list:
Now when we start typing any of our offices, auto fill can
automatically fill in the list based on all the other
Using the Fill Handle with Formulas
The fill handle can also be used with formulas.
The fill handle copies down the same formula but with updated
cell references -> as we fill down ‘A2’ changes to ‘A3’, then ‘A4’
To prevent cells updating, insert $ signs before using the fill handle.
This is called anchoring, and is covered in more detail a later
Cell references used as arguments in functions are updated in the
same way.
More Auto Fill Options
When you use the fill handle with the left mouse-button, a drop-
down button appears afterwards with further auto fill options:
‘Copy Cells’ tells Excel just to copy the original cells down (instead
of trying to extend any trend)
‘Fill Formatting Only’ won’t fill any values, but will copy down the
selected cells’ formatting.
‘Fill Without Formatting’ will copy down just the values, and no
formatting.
Flash Fill is a new feature offering a range of useful features, and is
the topic of our next video.