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


Get your clients ready for tax season

These year-end tax planning strategies address recent tax law changes enacted to help taxpayers deal with the pandemic, such as tax credits for sick leave and family leave and new rules for retirement plan distributions, as well as techniques for putting your clients in the best possible tax position.


Keeping you informed and prepared amid the coronavirus crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.