Excel Pivot Table Sorting Problems

Pivot Table Sorting Problems

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.


sortpivotcustomlist00

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.

sortpivotcustomlist01

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 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

Sorting Correctly

After you adjust that pivot table setting, the list of names changes, and Ann is shown at the top, instead of Jan.

sortpivotcustomlist03
____________