PIVOTEXCEL

Full Training Course for Excel® 2013:

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 WWW.PIVOTEXCEL.COM

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 other cells.

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 rightbutton 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 etc.

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 list.

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 offices.

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’ etc

To prevent cells updating, insert $ signs before using the fill handle. This is called anchoring, and is covered in more detail a later video.

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.

Full Training Course for Excel® 2013:

VISIT WWW.PIVOTEXCEL.COM FOR THE FULL EXCEL 2013 TRAINING COURSE

WWW.PIVOTEXCEL.COM

PIVOTEXCEL

PivotExcel is an independent training program and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. Microsoft, Excel, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Excel visuals used with permission from Microsoft.