This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Month Names Don’t Sort in PowerPivot
Problem: My data includes month names of Jan, Feb, Mar, and so on. When I put month in a pivot table, the months appear alphabetically: Apr, Aug, Dec, Feb.
Strategy: Did you know that regular pivot tables automatically look through all of the custom lists on your computer and use those lists to sort? This is why a regular pivot table puts Jan before Feb. But pivot tables do this with other values too. Create a custom list of East, Central, West, and Excel will sort a region field that way without any hassle. This has been happening automatically for over a decade. I never realized how much that I appreciated it until it stopped happening in PowerPivot.
PowerPivot tables are actually considered online analytical processing (OLAP) pivot tables. This means that many annoying limitations are imposed on those tables. You cannot group daily dates up to monthly dates in an OLAP pivot table. This is why calculated fields were added for year and month.
PowerPivot is attracting attention from Excel people, SQL Server people, and SharePoint people. The SQL Server people suggest calculating your month field with =Month(Sales[Date]). While people following that suggestion won’t have the month sorting problem, those people are not creating reports for use in the board room. You can’t seriously put out a report listing July as 07.
It requires eight clicks to fix this problem and they are the most annoying eight clicks. I’ve come to take for granted the fact that months sort correctly. I can create a pivot table in six clicks. When I have to spend eight clicks to fix the month names over and over, it becomes annoying.
To fix the month names, follow these steps:
1. Open the Row Labels drop-down.
2. Choose More Sort Options.
Figure 964 Choose More Sort Options.
3. Choose Ascending. Click More Options.
Figure 965 Choose Ascending, and then More Options.
4. In the More Sort Options dialog, uncheck Sort Automatically Every Time the Report Is Updated.
5. You can now open the first Key Sort Order drop-down and choose Jan, Feb, Mar.
Figure 966 Uncheck Sort Automatically. Choose the month custom list.
6. Click OK to close More Sort Options. Click OK to close Sort Options.
The result after eight clicks is that months are sorted into the correct sequence.
Figure 967 You finally have month names sorted into the correct sequence.
Gotcha: If you pick up a book by one of the SQL Server authors who are writing about PowerPivot tables, you will see that they suggest a formula of =Month([Date])&"-“&Format([Date],"MMM"). This produces values that do sort correctly (1-Jan, 2-Feb, 3-Mar). However, it is abundantly clear that those authors have never worked for your manager. Your manager would never accept a report with bizarre month names like 1-Jan, 2-Feb, 3-Mar. This is some crazy thing that an IT person would try to sneak into the board room. It just won’t pass in a real reporting environment.