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

CPEOs provide peace of mind around payroll services

The creation of these new IRS-certified service providers for small businesses clarifies some issues around traditional professional employer organizations.

QUIZ

8 sentences to help you master subject-verb agreement

When professionals prepare written material for readers inside their organization or outside, they should make sure that no errors distract from the message they need to convey. Take this short quiz for practice in subject-verb agreement.