Excel Pivot Table Sorting Problems

Usually, it’s easy to sort an Excel pivot table – just click the drop down arrow, and select one of the sort options.
sortpivotcustomlist00
Every now and then, the pivot table doesn’t 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, Jan is at the top of the list, instead of Ann.
sortpivotcustomlist01

Why the Sort is Incorrect

In this example, Jan is at the top of the list, because Excel assumes Jan means January, and is in one of Excel’s built-in custom lists. In addition to the built-in custom lists, you might have created your own custom lists, such as districts or departments.
Those custom lists take precedence when you’re sorting labels in a pivot table. Fortunately, if things don’t sort correctly, you can fix the problem, by changing a pivot table setting.

Fix the Pivot Table Sort

To prevent the custom lists from taking precedence when sorting a pivot table, follow these steps to change the setting:

  1. Right-click a cell in the pivot table, and click PivotTable Options.
  2. In the PivotTable Options dialog box, click the Totals & Filters tab.
  3. In the Sorting section, remove the check mark from “Use Custom Lists When Sorting”

sortpivotcustomlist02
Now, the list of names changes, and Ann is shown at the top, instead of Jan.
sortpivotcustomlist03
____________