Using the Fill Handle to extend a series is a common technique in Microsoft Excel. Although most people have used the Fill Handle to populate month names across a spreadsheet, there are many more hidden features.
Let's start with the basics. The primary use of the Fill Handle is for filling month names, weekday names and dates across the top or down the side of a report.
To do this, type the first item in a cell. Position the mouse cursor over the square dot in the lower corner of the active cell. When the mouse pointer changes to a + sign, drag right or down to fill other items in the list.
After you drag the Fill Handle, Excel fills items from the series. If you go beyond 12 cells for months, Excel starts over again with the first month.
Now let's take it further. Excel follows your lead on upper, lower or proper case. If you type all caps for the first entry, Excel will fill all caps. If you type lowercase, Excel will fill lowercase.
Excel will also follow the date formatting applied to the first cell, or use abbreviations for month or weekday names if you use them in the first cell.
If you type any text, followed by a number, Excel will correctly extend the series, even if there's a dash or a space before the number. This works great for entries such as Room 101, Pad 39, Building 36 or Area 51. However, it does not work when the number is in the middle.
Excel was built for accountants, so it easily handles any possible variation of quarters. After Q4, Excel will go back to Q1. However, to have Excel correctly fill quarters and years, you have to use "1Q" instead of "Q1," and place the quarter before the year.
Annoyingly, Excel will not automatically extend 1 to 1, 2, 3, etc. But there are several workarounds. The easiest is to hold down the Ctrl key while dragging the Fill Handle. Excel will then extend the series.
- Select the 1 and a blank cell to the right of the 1 and fill.
- Type 1 and then 2. Select both cells and fill.
- Drag the Fill Handle to get a series of 1's. Use the icon to the bottom right of the series to change from 1, 1, 1 to 1, 2, 3.
For dates, Excel will fill every date in a sequence. To have Excel fill only Monday-through-Friday dates, hold down the right mouse button while you fill. The tooltip will show all dates as normal. But after you release the mouse button, choose Fill Weekdays from the flyout menu and Excel will skip Saturdays and Sundays. Other options in this menu include Fill Months and Fill Years.
Bill Jelen, aka MrExcel, has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.