Excel: Using Custom Lists with the Fill Handle

EXTEND A SERIES

Type any month or weekday name in a cell. With that cell selected, hover your mouse over the square dot in the lower-right corner of the active cell. When the mouse pointer changes to a black plus, click and drag down or to the right to fill additional month names (see Figure 1).

By default, the fill handle works with months, weekdays, dates, and the three-letter month and weekday abbreviations. It also handles these situations:

• Type Q1 and drag to repeat Q1 through Q4.
• Type 1Q 2016 to fill quarters and years.
• Type any word followed by a space and a number. The fill handle will include the word while increasing the number.
• Type a date. Right-click the fill handle and drag. After releasing the mouse button, you can choose to fill months, years, or work days.
• To have the number 1 extend to 2, 3, 4, and so on, hold down the CTRL key while dragging the fill handle.

CREATING A CUSTOM LIST

In the seminar, the member frequently had to create reports with Jan through Dec in columns B through M and then Total in column N. You also can imagine scenarios where you want Jan, Feb, Mar, Q1, Apr, May, Jun, Q2, and so on.

Here’s how to set up a custom list for use with the fill handle.

1. Type the values for a list in a column or a row. Make sure the list is in the proper sequence.
2. Select the range containing the list.
3. Go to the File menu and select ­Options.
4. In the Excel Options dialog, choose Advanced from the left navigation bar.
5. In the Advanced Excel Options, scroll 83% of the way through the list and select the button for Edit Custom Lists. This is where Excel stores four default lists: month names, weekday names, month abbreviations, and weekday abbreviations. You aren’t allowed to edit these four built-in lists, but you can add a new list.
6. Provided you selected the range in step 2, it will be preselected in the Import List From Cells. Click the Import button. Your new list will appear as the last list in the Custom Lists box.
7. Click OK to close the Custom List dialog. Click OK to close Excel ­Options.

Figure 2 shows that Jan is now in the third and eighth lists. What will happen when you type Jan and drag the fill handle? Excel starts searching the custom lists from the bottom, so the new list with Jan through Total will be the one that’s used. If you type Jan and drag the fill handle 12 cells to the right, you will get Jan through Dec and Total. If you only need Jan through Dec, you would stop dragging after 11 cells.

NOTES ABOUT CUSTOM LISTS

Once you set up a custom list, it’s stored in the registry and is available in any Excel workbook that you open in the current version of Excel. If you’re currently using Excel 2013 and later upgrade to Excel 2016, you’ll have to recreate the custom lists in the new version.

When using the fill handle, you don’t have to start with January or Jan as the value. You can type any item from the list, and Excel will extend the list from that point. For example, type July. Drag the fill handle 11 cells to the right. You’ll get August, ­September, …, December, January, February, …, June.

Note that if you type May, this entry is found in both the month name list and the month abbreviation list. Since the month names are below the month abbreviations in the Custom List dialog, the fill handle will always give you the full month names when you start dragging from May.

It’s also possible to fill backwards. Type December in cell M1. Click the fill handle and drag left to cell B1. Excel will fill the months in reverse sequence.

Pivot tables will sort automatically based on values found in a custom list. If you have a pivot table with first names in the report, girls named April, May, or January will sort before everyone else.

As you see in Figure 2, I have imported other common lists into my version of Excel. You might import a list of state names, letters of the alphabet, cost centers, account numbers, employee names, product lines, and so on. The list is endless.