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

Do you have a technology question for this column? Send it to Senior Editor Stanley Zarowin via e-mail at zarowin@mindspring.com or regular mail at the Journal of Accountancy, Harborside Financial Center, 201 Plaza Three, Jersey City, NJ 07311-3881. We regret that we cannot answer letters individually. If a reader’s question is deemed to have sufficiently broad interest, we will answer it in a forthcoming Technology Q&A column.

—The editors


Implementing a global statutory reporting maturity model

Assess your organization's capabilities and progress toward an ideal state of global statutory reporting. Sponsored by Workiva.


Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.