Get a Handle on AutoFill in Excel

If you have used Microsoft Excel for any length of time, you have probably discovered the AutoFill handle — the tiny black square on the lower right-hand corner of a selected cell — to be one of its most useful tools. You can use the AutoFill handle to quickly drag a series into existence. You can drag days of the week, months of the year, or a numerical series — you can even create your own custom series.

Use AutoFill to enter the days of the week or months of the year

Even though Excel can’t sing, it knows the days of the week. You can begin a list by typing either the full day of the week or its 3-letter abbreviation in a blank cell. Then, position the mouse pointer above the AutoFill handle and drag the handle horizontally or vertically across adjacent cells. Dragging the handle down or to the right fills in the next item in the series, while dragging up or to the left fills in the previous item.

Similarly, Excel knows the months of the year. You can enter the month or its 3-letter abbreviation, then drag the AutoFill handle to fill in other months of the year.

AutoFill a numerical series

When it comes to automatically filling in a numerical series, you have a couple of options.

First, if you select a cell containing a number and drag the AutoFill handle, Excel assumes you want to simply copy the value. When you release the mouse button, the AutoFill options button appears. Click the option for

Fill Series

to replace the multiple copies of the selected number with a series of numbers that increment (or decrement) by one.

Second, if you select two adjacent cells with distinct numerical values and drag the AutoFill handle, Excel fills the cells with a series which increments by the difference of the two values. In other words, if you selected one cell containing the number 1 and another cell containing the number 3 and then dragged with the AutoFill handle to the right, the resulting series would be 1, 3, 5, 7, etc. Each subsequent value would be 2 more than the last. In this way you can increment a series by twos, by threes, or even by twenty-sevens.

Customizing Excel’s AutoFill List

As we have seen, Excel knows days, months, and numbers — and that’s it. If you select a cell with any other “non-series” data and drag the AutoFill handle, Excel just copies those cells. However, you can create your own custom list — or lists — to “teach” Excel new series.

Let’s teach Excel the first ten ordinal numbers, first through tenth. In Excel 2003, click

Tools > Options > Custom Lists

. Then select

New List

and type each list entry, separating the values with commas. Finally, click

Add

, then

OK

.

Note: In Excel 2007, click the Microsoft Office button, then click Excel Options. Click the Popular > Top options for working with Excel > Edit Custom Lists.

Now, you can AutoFill a series based on items in your custom list. And, you can even sort your data according to the order of items in your custom list.

AutoFill isn’t always a drag

You can extend a series in a column by double-clicking the AutoFill handle; Excel uses the column to the left as a guide.