MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Fill Handle 1, 2, 3


July 06, 2017 - by Bill Jelen

Excel Fill Handle 1, 2, 3

The fill handle is great for Months, Weekdays, Quarters, and Dates, but why won't it fill 1, 2, 3? This episode shows many workarounds. Also includes:

Why does the Excel Fill Handle pretend it does not know how to count 1, 2, 3?

The Fill Handle is great for filling months, weekdays, quarters and dates. Why doesn’t it know that 2 comes after 1?

In case you’ve never used the Fill Handle, try this: Type a month name in a cell. Select that cell. In the lower-right corner of the cell is a square dot. Click the dot and drag right or drag down. The tooltip increments to show the last month in the range.


Fill Handle
Fill Handle

When you let go of the mouse button, the months will fill. An icon appears giving you additional options.

Fill Handle Additional Options
Fill Handle Additional Options

The Fill Handle works great with months, days...

Fill Handle with Months and Days
Fill Handle with Months and Days

The fill handle works with quarters in many formats:

To do both quarters and years, you have to start with a number, then Q, then any punctuation (period, space, apostrophe, dash) before the year.

Fill Handle with Quarters
Fill Handle with Quarters

But when you type in 1 and grab the Fill Handle, Excel gives you 1, 1, 1, 1, 1, .... Many people tell me to enter the 1 and the 2, select them both, then drag the Fill Handle. But there is a faster way.

The secret trick is to hold down Ctrl! If you hold down Ctrl while dragging, Excel will fill 1, 2, 3.

Secret Trick - <kbd>Ctrl</kbd>
Secret Trick - Ctrl

Note

Huntsville Alabama’s Andrew Spain of Spain Enterprise taught me a cool variation on this trick. If you start dragging without Ctrl, you can press Ctrl during the drag. A+icon appears at the bottom of the drag rectangle to indicate that you are going to fill instead of copy.

How were we supposed to figure out that Ctrl makes the Fill Handle count instead of copy? I have no idea. I picked up the tip from Row 6 at an IMA seminar in Kent, Ohio. It turns out that Ctrl seems to make the Fill Handle behave in the opposite way. If you Ctrl + drag a date, Excel will copy instead of Fill.

I've heard another trick: type 1 in A1. Select A1 and the blank B1. Drag. Excel fills instead of copies.

Right-Click the Fill Handle for More Options

If you right-click and drag the Fill Handle, a menu appears with more options, like Weekdays, Months, and Years. This menu is great for dates.

Right Click and Drag the Fill Handler
Right Click and Drag the Fill Handler

What if your payroll happens on the 15th and on the last day of the month? Put in both dates. Select them both. Right-click and drag the Fill Handle. When you finish dragging, choose Fill Months.

Fill Handle - Fill Months
Fill Handle - Fill Months
Fill Months - Result
Fill Months - Result

Watch Video

  • The fill handle is great for Months, Weekdays, Quarters, and Dates, but why won't it fill 1, 2, 3? This episode shows many workarounds. Also includes:
  • You can fill a series by using the Fill Handle
  • You can drag down or right
  • Filling months works automatically
  • Filling weekdays works automatically
  • Type in ALL CAPS to have the fill be all caps
  • Abbreviations work for months and weekdays
  • For years and quarters: 1Q-2016
  • Any word followed by a number works
  • Hold down Ctrl key to have 1 fill to 1 2 3
  • You can press Ctrl after you start dragging
  • Hold down Ctrl to copy dates instead of filling
  • Right-click and drag to fill weekdays or months or years

Title Photo: blickpixel / pixabay


Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.