Enlist Excel's Fill Handles to Save Time


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.




Supercharge your audit process with AI

Auditors today can employ AI to automate tedious tasks and gain far greater insights from their clients’ information. This free report lays out a five-step process for implementing AI and shows ways AI can add value to the auditing process.


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.