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.
____________
Thanks Debra
That really helped me out. For some reason Excel was listing data labels twice, but by changing these settings, it seemed to correct itself.
Very good information! This helps a lot.
I am still unable to sort by the PAYMENT field. The option to sort from top to bottom is greyed out. Suggestions are welcome.
I do not remember this one at the moment…
I still have a persistent issue with grouping by dates. It oders the dates within the month randomly, and I’ve also noticed it’s flipping the data from two columns (i.e. I have a column called $10’s and one called $5’s). The data on the data table is correct, but it transposes it the PivotTable columns.
I’m battling to sort data in a pivot table, in date order. One of the columns of data selected to appear in the pivot table, contains dates (and is formatted properly) – but the tranactions are all mixed up. I’ve tried the obvious (like “sorting” by the data in the “date” column) – but this has no effect. Can anyone help – Many thanks
data is as under
Sr.No Name date.of.order
1 john 10/12/12
2 Michel 15/12/12
This did not work for me and I’m wondering if anyone has any other ideas. When I sort A to Z this is what I get: I would like this to sort in numerical order (09580 on top, 31785 in its proper place). Any ideas? Thanks.
10747
13485
14455
14456
14703
31785
09580
09580A
09581
09582
09582A
09584
09902
09903
09905
09924
09924A
10747A
10747B
14703A
14703B
14703C
14703D
14703E
30282A
30282B
30282C
30454B
30454C
30454D
30454F
30454G
30454H
30473C
30473D
30473E
30473F
30473G
30473H
Some years later, Shana, but the solution may be useful for others: I’d say that Excel was recognising your first six terms as numbers, and therefore sorting them to the top, and the rest as text.
Excel automatically removes a number’s leading zero, unless it is formatted as text or has a (hidden) leading apostrophe, so the “numbers” with a leading zero must be formatted as text. They get sorted next.
Any term with a non-digit anywhere (a-z, space, other characters) will be treated as text and sorted according to their first character.
If you format your first six terms as text, they’ll sort into the right places.
This sorted it for me actually, Philip. Except it was non-digits that was the problem but decimals. Thanks for updating even so long after this was posted!