Excel 2024: The Fill Handle Does Know 1, 2, 3…
February 22, 2024 - by Bill Jelen
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. There is a square dot in the lower right corner of the cell. This dot is called the Fill Handle. Hover over the Fill Handle. The mouse cursor changes from a white cross to a black plus. Click the handle and drag right or drag down. The tooltip increments to show the last month in the range. Note If it is not working, select File, Options, Advanced. The third checkbox, Enable Fill Handle and Cell Drag and Drop, toggles the Fill Handle. |
|
When you let go of the mouse button, the months will fill in. An icon appears, giving you additional options. |
|
The Fill Handle works great with months or weekdays. |
|
The Fill Handle also works with quarters in many formats. To do both quarters and years, you have to type a number, then Q, then any punctuation (period, space, apostrophe, dash) before the year. |
|
When you type 1 and grab the Fill Handle, Excel gives you 1, 1, 1, ... Many people say to enter the 1 and the 2, select them both, then drag the Fill Handle. Here is a faster way. The secret trick is to hold down Ctrl while dragging. Hold down Ctrl and hover over the fill handle. Instead of the normal icon of a plus sign, you will see a plus sign with a superscript plug sign. When you see the ++, click and drag. Excel fills in 1, 2, 3, ... |
Note
Andrew Spain of Spain Enterprise in Huntsville, Alabama 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. If you need a great Excel Consultant, find Andrew at spainenterprise.com
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 the IMA Meonske 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 copies instead of fills.
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. |
|
Teach the Fill Handle a New List
The Fill Handle is a really handy tool. What if you could use it on all sorts of lists? You can teach Excel a new list, provided that you have anywhere from 2 to 255 items. Here is the easy way:
1. Type the list in a column in Excel.
2. Select the list.
3. Select File, Options, Advanced. Scroll almost to the bottom and click Edit Custom Lists.
In the Custom Lists dialog, click Import.
Excel will now understand your list as well as it understands Sunday, Monday, Tuesday. Type any item from the list It does not have to be the first item.
Grab the Fill Handle and drag. Excel fills from your list.
I use this trick for lists that should be in Excel, such as a list of the U.S. states and a list of the letters of the alphabet.
Bonus Tip: Fill Jan, Feb, ..., Dec, Total
A person in one of my seminars wanted to have Jan fill into 13 values: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Total. While you can edit any custom list that you create, you cannot edit the first four lists in the Custom Lists dialog. However, if you use the preceding tip to add a new custom list with the 13 values, that list wins. If two custom lists have the value Jan, the lowest one in the dialog box is the one that is used. If you fiscal year ends March 31, you could set up a list with Apr, May, Jun, ..., Jan, Feb, Mar, Total. |
|
Bonus Tip: Fill 1 to 100,000 in a Flash
What if you have so many items that you can't drag the Fill Handle? Follow these steps: 1. Type the number 1 in a cell. 2. Select that cell. 3. On the Home tab, toward the right, in the Editing group, open the Fill dropdown and choose Series. 4. Select Columns. 5. Enter a Stop Value of 100000. 6. Click OK. |
What if you have to fill 100,000 cells of bagel flavors?
1. Type the first bagel flavor in A1.
2. Select A1.
3. Type A100000 in the Name box and press Shift+Enter to select from the current cell to A100000.
4. Home, Fill, Series and click AutoFill in the Type box. Click OK to fill from the custom list.
Thanks to the person in row 6 at the Meonske Conference in Kent, Ohio, for suggesting this feature.
This article is an excerpt from MrExcel 2024 Igniting Excel
Title photo by David Wright on Unsplash