Usually, it’s easy to sort an Excel pivot table, but you might run into one that just won’t sort correctly! For example, employee names are listed alphabetically, but there’s a name at the top that doesn’t belong there. Why is the pivot table not sorting that one name with the others?
Video: Pivot Table Not Sorting Correctly
In this 4-minute video, I show why the problem occurs, and a quick way to fix the problem.
There are written steps below the video.
Video Timeline
0:00 Pivot Sorting Problem
1:07 Why is Wrong Item at Top?
2:02 Stop the Problem
3:02 Global Default Settings
Excel Custom Lists
In most cases, this type of pivot table sorting problem is caused by Excel’s custom lists.
Excel has built-in custom lists, like days of the week or months, which are working quietly in the background.
- Tip: You can even create your own custom lists!
Those lists can be real time savers, when you’re setting up a worksheet. For example:
- Just type the full name of a month or weekday, or type the 3-letter code for a day or month.
- Then, select that cell, and point to the fill handle, at its bottom right corner
- When the pointer changes to a plus sign, drag down, or in any other direction!
- Excel’s AutoFill feature will fill in the month or day sequence, as you drag.
Sort by Custom List
When you’re sorting in Excel, you can use one of the custom lists as the setting, instead of A to Z.
That’s helpful if you’re sorting a column with weekday names, or month names.
Usually, you’d want those in chronological order, instead of A-Z.
Pivot Table Sorting
Pivot tables use custom lists for sorting too, even if you don’t ask them to!
It’s a default setting when you create a new pivot table.
So, if your employee list contains a name like “Jan,” it might get sorted to the top, ahead of Alan, Barb, and Chris!
That happens because Excel thinks “Jan” is the 3-letter code for January.
Fortunately, it’s easy to change how Excel sorts these lists in a pivot table.
Change Pivot Table Settings
To fix this sorting issue, follow these steps to change a pivot table setting:
- Right-click on any cell in the pivot table.
- Click on Pivot Table Options.
- Go to the Totals & Filters tab.
- In the Sorting section, remove the check mark from, Using custom lists when sorting
- Click the OK button, to apply the option setting change
Once you do this, your pivot table should sort correctly!
Note: This setting will apply to all the fields in this pivot table.
Sort Manually
After you change the settings, if the list still doesn’t sort correctly, you can sort it manually.
- In the pivot field header, click the drop-down arrow
- In the menu, click Sort A to Z or Z to A.
Pivot Table Default Settings
If you want to avoid this sorting problem in the future, you can change the default settings for all new pivot tables.
Here are the steps to follow:
- At the top of Excel, click on File, then Options.
- At the left, select Data.
- In the Data options section, at the top right, click the button for Edit Default Layout.
- At the bottom right, click the PivotTable Options button
- Go to the Totals and Filters tab
- Go to the Totals & Filters tab.
- In the Sorting section, remove the check mark from, Using custom lists when sorting
- Click the OK button, to apply the option setting change
- Click OK again, to save the default pivot table settings
After that, new pivot tables should sort correctly, instead of using custom list settings.
Get the Sample Excel File
To follow along with the video, you can get the Excel sample file, on the Pivot Table Sorting Tips page, on my Contextures site.
The zipped file is in Excel xlsx format, and does not contain any macros
More Pivot Table Tips
For more Excel pivot table tips and tricks, check out these helpful resources on my Contextures site: