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


How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.


Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.


Deflecting clients’ requests for defense and indemnity

Client requests for defense and indemnity by the CPA firm are on the rise. Requests for such clauses are unnecessary and unfair, and, in some cases, are unenforceable.