Excel Fill Handle 1, 2, 3
July 06, 2017 - by Bill Jelen
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.
When you let go of the mouse button, the months will fill. An icon appears giving you additional options.
The Fill Handle works great with months, 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.
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.
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.
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.
- 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