Enlist Excel's Fill Handles to Save Time

BY STANLEY ZAROWIN

Q: I have a problem with Excel’s fill handle: All I want to do is drag the fill handle so that it fills every other row with a value (1, 2, 3, 4, etc.)—leaving one blank cell in between each incrementing value. It seems so simple, but the solution eludes me.

 

A: So, what you want is this:

 

 

You’re right, it is simple, but fill handles can be somewhat annoying, especially because Excel’s logic is so rigid. But do we really want Excel to be anything but rigidly logical? So simply keep in mind that Excel’s fill handles need to be able to recognize a pattern before they can follow it.

 

First some background: If, for example, you want to fill a range with incrementing numbers (say 1, 2, 3, 4), put 1 in cell A1 and 2 in B1. Then select both cells, and that establishes a pattern. Now drag the fill handle as far as you need, and it will repeat the incremental pattern (see screenshot below).

 

 

Now, to address your specific problem: You want those incrementing numbers, but you also want a space between each. If you fill A1 with 1, leave cell B1 blank, put 2 in cell C1, but then select all three cells and drag the fill handle, it won’t work because you haven’t established a pattern Excel can recognize. What you need to do is fill A1 with 1, leave cell B1 blank, put 2 in cell C1, and leave D1 blank, put 3 in E1 then select from A1 to F1 (so you include the whole pattern). When you drag the fill handle (see screenshot below), you will have set the pattern.

 

 

SPONSORED REPORT

Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.

TAX PRACTICE CORNER

More R&D tax help

"Can I use the R&D credit?" PATH Act enhancements make the credit more attractive to a wider range of taxpayers.

QUIZ

News quiz: Tax-related data breach explained

News about a data breach that affected about 100,000 people, the IRS’s budget for the fiscal year, and the 2018 health spending account limits received attention recently. See how much you know with this short quiz.