Excel’s Auto Fill Feature

In an Excel spreadsheet, there is a small block at the bottom, right-hand corner of the active cell or active range of cells: this is the Fill Handle. The results from left-click-dragging the Fill Handle are dependent on the contents of the selected cell or range. Follow the steps in this tutorial to learn the basics of this time-saving feature!

1. Completing a series

Open a new, blank workbook and follow the instructions below:

1. Enter the text January in cell A1 and press CTRL+ENTER on the keyboard
2. Left-click drag the Fill Handle (black plus sign) down
3. Release the left-click button when the ScreenTip displays December

2. Use the Auto Fill shortcut

Because we have a range of data-filled cells adjacent (immediately next to) cell B2 we can now use the double-left-click shortcut when using the Auto Fill on B2 to extend the series in column B to row 12:

1. Enter the text Monday in cell B1 and press CTRL+ENTER on the keyboard
2. Double-left-click on the Fill Handle (note the black plus sign mouse pointer)

The column of day names in column B will extend down to row 12 to match the entries in column A. Create a series of numbers in column C:

1. Enter the digits 1, 2 and 3 in cells C1 to C3
2. Ignore the Quick Analysis tool and double-left-click on the Fill Handle as before

The Auto Fill can complete multiple series at once, meaning you could complete steps 1 and 2 above as one step:

1. Type January in cell A1
2. Type Monday in cell B1
3. Select the range A1:B1
4. Left-click-drag the Auto Fill down to row 12.

3. Copying actual content

If the actual content of a cell is a function or a formula, then the actual contents of the cell will be copied and adjusted by Auto Fill. In column D, create a formula that multiples the value in column C by 10:

1. Create the formula =C1*10 in cell D1
2. Double-left-click on the Fill Handle of cell D1 to copy the formula down to cell D12
3. Use the CTRL+~ shortcut to display the formulae in the cells in column D

4. Roll your own!

You can even create your own series that will then be recognised as a series in Excel. In the below example, we “teach” Excel the days of the week in Afrikaans:

1. Select Options from the File menu
2. Select the Advanced category
3. Scroll down to the General section
4. Click on the Edit Custom Lists… button

1. The Custom Lists dialog opens
2. Add a list of entries, one per line
3. Click the Add button
4. Your new list will be added to the existing lists
5. Click the OK button

5. Test

Type Maandag in cell E1 and give it a twirl!

6. Next steps

It is important to understand how functions and formulae are copied down. Head over to the Absolute vs Relative references in Excel tutorial.