MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: The Fill Handle Does Know 1, 2, 3…


January 20, 2020 - by Bill Jelen

Excel The Fill Handle Does Know 1, 2, 3…. Photo Credit: David Wright at Unsplash.com

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 toggles the Fill Handle.

Grab the fill handle from a cell containing January and drag down. The tooltip is currently showing October.

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

Release the mouse button and it fills the month names February, March, and so on. A tiny Options drop-down appears on the grid to the right of the last filled cell.

The Fill Handle works great with months or weekdays.


Three additional examples are shown. JAN fills FEB, MAR, APR. Monday fills Tuesday, Wednesday, Thursday. mon fills tue wed thu.

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.

Four more fill handle examples: Q1 fills Q2, Q3, Q4. Qtr 1 fills Qtr 2, Qtr 3. 1st Quarter fills 2nd Quarter, 3rd Quarter. The final example is not well-known. 1Q-2020 will fill the four quarters in 2020 and then jump to 1Q-2021.

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, ….

Drag the fill handle from 1 and you will get 1, 1, 1.
But, if you hold down the Ctrl key while dragging the 1, a tiny plus sign appears to the right of the plus mouse cursor and....
... the 1 drags to 2, 3, 4.

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.

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.

5 fill handle example are shown. Fill from a date and you get sequential dates. Ctrl+Drag the fill handle from a date and it will copy the date. Right-Drag the fill handle from a date and you can choose to Fill Weekdays, Months, or Years.

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.

Two cells are selected. One is January 15. The other is January 31. With both cells selected, right-drag the fill handle down 22 cells and choose FIll Months to get payroll dates for the year.

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 240 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 Excel Options, choose Advanced in the left navigation bar. Scroll all the way to the bottom and choose Edit Custom Lists button.

In the Custom Lists dialog, click Import.

A list of bagel flavors is selected in the Excel grid. The Custom Lists dialog is shown, with built-in lists such as Sun, Mon, Tue, Wed and January, February, March. The address for the selected cells is in the Import box and the mouse cursor is pressing Import to import the bagel flavors as a custom list.

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.

Type the first bagel flavor in any cell. Grab the fill handle and drag.

Grab the Fill Handle and drag. Excel fills from your list.

Drag the fill handle and Excel fills all of the bagel flavors. In this screenshot, you dragged too far by one cell. The first item from the list fills the last cell.

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.

Thanks to the person in row 6 at the Meonske Conference in Kent, Ohio, for suggesting this feature.

Title Photo: David Wright at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.