Usually, it’s easy to sort an Excel pivot table – just click the drop down arrow in a pivot table heading, and select one of the sort options. Occasionally though, you might run into pivot table sorting problems, where some items aren’t in A-Z order.
Pivot Table Sorting Problems
In some cases, the pivot table items might not sort the way that you’d expect. In this example, there are sales representative names in column A, and they have been sorted alphabetically, A-Z.
However, after sorting the items, Jan is at the top of the list, instead of Ann. The rest of the items are sorted correctly, but Jan is the exception.
Video: Wrong Item at Top in Pivot Table
After you sort an Excel pivot table, the wrong item might appear at the top.
In this video, I show how to fix that problem, so all items are listed in alphabetical order, A-Z.
There are written steps below the video.
NOTE: There are more pivot table sorting tips on my Contextures website, and there is another video at the bottom of this page. It shows how to fix NEW pivot table items that are not in alphabetical order.
Why the Sort is Incorrect
In this example, Jan is at the top of the list, because Excel assumes Jan means January, and “Jan” is in one of Excel’s built-in custom lists.
There are built-in custom lists of weekday names and month names — both the full names (January, February, etc.), and 3-letter versions (Jan, Feb, Mar, etc.).
In addition to the built-in custom lists, you can also create your own custom lists, such as districts, or departments, or other lists of items.
Custom Lists and Pivot Tables
By default, Excel’s custom lists take precedence when you’re sorting labels in a pivot table. The built-in lists and the custom lists that you create, will both affect the pivot table sorting.
Fortunately, if things don’t sort the way that you need them to, you can fix the problem, by changing a pivot table setting.
Fix Pivot Table Sorting Problems
To prevent the custom lists from taking precedence when sorting a pivot table, follow these steps to change the setting:
- Right-click a cell in the pivot table, and click PivotTable Options.
- In the PivotTable Options dialog box, click the Totals & Filters tab.
- In the Sorting section, remove the check mark from “Use Custom Lists When Sorting”
Sorting Correctly
After you adjust that pivot table setting, the list of names changes, and Ann is shown at the top, instead of Jan.
More Pivot Table Sorting Tips
If this tip didn’t fix your sorting problem, go to the Pivot Table Sorting page on my Contextures site. There’s more information there, to help you fix sorting problems and macros to make sorting easier.
And this video shows another pivot table sorting problem that you might see in your Excel workbooks — new items are not in alphabetical order. See how to fix that problem.
____________
Handy, not a problem I’ve come across before but worth bearing in mind
Thanks Debra nice tip. Glad I am part of your mailing list!!
No, don’t turn that off, I like being at the top! 🙂
Ha! Hadn’t thought of that reason for leaving the custom list option turned on.
Thanks so much, columns with dates were not sorting correctly. I was starting to curse Excel 2010. This fixed the problem.
This did not work for me. I had the original column (Called First_Date) formatted as Mon-12 (Such as Jan-12, Feb-12,…, Dec-12). I formatted the column for the data as Date and custom “Mar-12”. However, the pivot table will not recognize this column as date. It ALWAYS consider it as text and sort it A-Z or Z-A. I need the pivot table to recognize this column label as date so that I can sort it “Oldest-Newest or Newest-Oldest. Any ideas?
I have this same problem. A straightforward date that looks like 01/01/2013 is sorted before 01/02/2012 even though it is formatted as date and even though another cell that refers to it and adds 2 reads 01/03/2012. There is no opportunity to sort as oldest-newest, only A to Z.
I have the same issue when using the “Date” field as a Row Label. I moved the “Date” field to Values, and chose “Sum of”, then just adjusted the number format to Date. This allows me to sort another field by the Value of the “Date” field. (e.g. I have an “Opportunity Number” field in my Row Labels, I select More Sort Options, then Ascending (A to Z) by: “Date” – note: Excel only allows sorting Row Label fields by Value fields). This is the only way I can figure date sorting when all the other suggestions above don’t affect anything in the pivot table. I do not know if I will lose any other functionality from making the “Date” field a value. I already have other fiscal time periods in my Report Filters that will allow me to select certain time periods, so hopefully not.