What You Should Know About AutoFill

Q. I know that Excel has this marvelous function—called AutoFill—that quickly and easily fills a series of cells with a progression of values. But I have trouble evoking it. Can you help me? And while you’re at it, can you tell me what else AutoFill can do?

A. You’re right, AutoFill is really handy, and hard to use sometimes. What makes it difficult is the sensitivity of the mouse cursor and centering it during the AutoFill process.

Before I tell you how to solve that problem, let me describe the power and flexibility of AutoFill. For those new to AutoFill, this example best describes it: Say you type the word January into one cell. Notice that when your cursor is in the cell, in the lower right corner of the cell a small black square appears; that’s called a fill handle. If you carefully move the mouse cursor over the fill handle, it changes to a small black plus sign (called crosshairs). Now if you drag the crosshairs (click and hold down the mouse button) to the right, an AutoTip (a tiny window with the word February) appears; the AutoTip shows the impending addition of the next month (February) in the next cell. (By the way, if you had typed the abbreviated Jan ., the AutoTip would have been Feb .) As you drag the cursor along, March , April and all the subsequent months will appear.

You can use AutoFill to fill out the series of days of the week or fiscal quarters, among others. Furthermore, you can create custom lists if, for example, you have to place a list of people, sales locations or product parts in a spreadsheet on a regular basis.

Here’s how: Click on Tools, Options and the Custom Lists tab. Then click on the Add button and insert in the space under List entries the names you want to be programmed for AutoFill. Click on OK and the list setup is done.

Now if you type Stanley in one cell and then evoke AutoFill, it will produce the following:

By the way, AutoFill can handle any numerical series (for example, 5, 10, 15). Just put in the first two numbers and highlight both before following the steps for evoking AutoFill. It also works with descending numbers (10, 9, 8).

Now the trick for making it easy to evoke AutoFill: You can gain more control over the filling process if you right-click on the fill handle and then drag the cursor. When you release the mouse button, a context menu appears that allows you to select the type of fill you want. The options include: copy the same information in each subsequent cell, fill in a series and fill values (of a formula).