Pivot Table Sorting Problem Wrong Item at Top

Usually, it’s easy to sort an Excel pivot table – just select one of the sort options – A to Z or Z to A – in the heading drop downs. However, you might run into a pivot table sorting problem, where the wrong item stays at the top. See why that happens, and how you can fix the sorting problem.

Pivot Table Sorting Problems

Here is an example where the pivot table items are not sorted in the way that you’d expect.

This pivot table has sales rep names in column A, and those names were sorted alphabetically, A-Z.

All the sales rep names are sorted correctly, except June. Why is June at the top of the list of rep names, ahead of Ann?

If you sort the Rep field again, nothing changes. June’s name stays at the top.

Why the Sort is Incorrect

In this example, June stays at the top of the list, because June is also the name of a month.

Excel has a special collection of built-in lists, called Custom Lists.

June is in the custom list of month names
Pivot tables sort custom list items first, unless you tell Excel not to do that

Change Pivot Table Sort Setting

To prevent the custom lists from taking precedence when you sort 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”
Click OK

Sorting Correctly

After you adjust that pivot table sort setting, the list of names might re-sort automatically.

If not, and June is still at the top, instead of Ann, use one of these fixes:

Sort Manually

Click the arrow in the Rep heading
Click the Sort A to Z command

Sort Automatically

Click the arrow in the Rep heading
Click More Sort Options
In the Sort Options section, click Ascending (A to Z) by
- That changes the sort to Automatic, instead of Manual
Select Rep from the drop down list of fields
Click OK

Excel Custom Lists

This short video shows how to create your own Custom List in Excel. There are written steps below the video.

Create a Custom List

In Excel, you can create custom lists, like the built-in lists of weekdays and months.

For example, you could create a custom list of regions, products, or other items. Then, use the custom lists to sort the items on the worksheet, or in a pivot table.

Follow these steps to import a list of from an Excel worksheet:

On the worksheet, select your list of items for the Custom List
On the Ribbon, click the File tab, and click Excel Options.
In the list of categories, click Advanced
Scroll way down, and In the General section, click Edit Custom Lists.

In the Custom Lists dialog box, under Custom Lists, NEW LIST is automatically selected
In the range box, the address of your selected list should automatically appear
Click the Import button
Click OK twice, to close the dialog boxes.