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

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: Economy and health care changes top CPAs’ list

CPA decision-makers’ economic outlook and the House Republicans’ proposed tax changes as part of replacing the Patient Protection and Affordable Care Act received attention recently. See how much you know with this short quiz.