Creating a series of month headings in Excel is simple: Type “January” or “Jan” in a cell, grab the Fill Handle, and drag to the right until you’ve reached December. The ability to fill months and weekdays is handled through the Custom List dialog in Excel. However, ever since the 1990s, Excel includes secret custom lists that are used for filling quarters.
Using the Custom List Dialog
To access the Custom Lists dialog, go to File, Options. In the left pane of the Options dialog, choose Advanced. Use the scrollbar to scroll to the end of the Advanced category, and click the button for Edit Custom Lists.
The Custom Lists dialog shows four built-in custom lists:
Sun, Mon, Tue, Wed, Thu, Fri, Sat
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
January, February, March, April, May, June, July, August, September, October, November, December
You can import a new custom list by typing the words of a list into a series of cells and then using the Import button in the Custom Lists dialog box to import the list. In Figure 1, the first four lists are built in. The remaining lists have been imported.
Figure 1
Benefits of a Custom List
Custom lists are useful in three situations:
- Quick data entry: Type any item from the list in a cell. Drag the Fill Handle to the left or down, and Excel will fill cells with items from the custom list.
- Sequence of items in a pivot table: By default, pivot tables that are built using data in the grid will automatically sort based on items found in any custom list. This feature ensures that your months appear starting with January instead of the alphabetical sequence of April, August, February, and so on.
- Sorting data: When sorting with the Sort dialog, open the Order drop-down menu. The typical choices are A to Z and Z to A, but you can also choose Custom List… and specify that the data should be sorted into the sequence of the custom list (see Figure 2). Let’s say that your manager wants a sales report to appear with the East region first, followed by Central and then West. The built-in sort would either put Central first or West first. By using a custom list, you can specify that the sequence should be East, Central, West.
Figure 2
Custom lists are helpful most of the time. Being able to sort data into the sequence used by your employer saves a lot of cutting and pasting of data to change the sequence. In Figure 3, a simple custom list containing East, Central, West is used to sort the sales report.
Figure 3
An Anomaly with Custom Lists
There’s one unusual problem with custom lists: Sometimes just one person in a pivot table is sorted out of sequence. For example, in Figure 4, most of the eight employees are in alphabetical order (Abby, Buck, Dan, Elsa, Gretchen, Josh, and Ken). But every time the pivot table is created, a sales rep named Jan appears at the top of the pivot table.
Figure 4
Why is this happening? Remember that pivot tables will automatically be sorted by the order in the custom lists. What if you have a combination of names that are in a custom list and some that aren’t in any list? The names in the custom list appear first, and the others appear in alphabetical order after the names in the custom list. The same logic that ensures that January appears before February is causing this sales rep named Jan to sort to the top of the list.
When this happens, one option is to turn off the option to use custom lists for sorting. From the pivot table, click the Options button in the PivotTable Analyze tab. In the PivotTable Options dialog, use the second tab, Totals & Filters. The last option on that tab is Use Custom Lists when sorting. Unselect this option, click OK, and then sort the pivot table alphabetically to return Jan to the alphabetical sequence (see Figure 5).
Figure 5
The Hidden Custom List
Someone noticed that a pivot item called “JAS” was always coming to the top of their pivot table. This person knew about the problem with custom lists changing the pivot table sequence. They searched through their custom lists to look for any list that contained JAS, but none of the custom lists contained an entry for JAS (see Figure 6).
Figure 6
To find the solution, I dug deep into my contacts for people who were on the Excel development team decades ago. I asked if anyone knew what was happening to cause JAS to come to the top of the pivot table list.
It turns out that there are secret custom lists designed to deal specifically with quarters.
In Figure 7, I typed something in row 1 and then dragged the Fill Handle down several rows. These examples reveal some of the hidden custom lists.
Figure 7
In columns A and B, a letter followed by a number will extend the numbers. R1 changes to R2, R3, and so on, while W1 changes to W2, W3, and so on. But in column C, a secret custom list is making sure that Q4 is followed by Q1.
In column D, any word, followed by a space, followed by a number will extend the numbers. “Word 1” changes to “Word 2” and “Word 3.” But in columns E and F, a secret custom list is making sure that “Qtr 4” is followed by “Qtr 1.”
Columns G and H are a fun example. Ordinal numbers followed by a space and a word will correctly extend. “1st Place” becomes “2nd Place.” But when you use “1st Quarter,” Excel will make sure that “4th Quarter” is followed by “1st Quarter.”
Column I shows how to use the Fill Handle for quarters and year. Make sure that the cell starts with 1Q, then any space or punctuation, and then the year. As you drag the Fill Handle, the quarters advance, but then after Q4, the year will also advance.
Finally, column J demonstrates why JAS appears at the top of the pivot table. Some of Microsoft’s early customers were using quarter abbreviations of JFM, AMJ, JAS, and OND where JFM stands for January, February, and March, and so on. Microsoft added a secret custom list with these four quarterly abbreviations just like it added Q1, Q2, Q3, and Q4.
Personally, I wasn’t familiar with using JFM to mean quarter 1. However, if you Google “Quarters JFM, AMJ, JAS, OND,” there are several examples from various industries that use these abbreviations.
After learning that Excel has secret custom lists, the next question is: What other hidden custom lists might be stored in Excel? If you ever find a pivot table that isn’t sorting in the correct sequence, drop me an email.