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.
NOTE: There are more pivot table sorting tips on my Contextures website, and there is a 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.
____________
Handy, not a problem I’ve come across before but worth bearing in mind
Thanks Debra nice tip. Glad I am part of your mailing list!!
No, don’t turn that off, I like being at the top! 🙂
Ha! Hadn’t thought of that reason for leaving the custom list option turned on.
Thanks so much, columns with dates were not sorting correctly. I was starting to curse Excel 2010. This fixed the problem.
This did not work for me. I had the original column (Called First_Date) formatted as Mon-12 (Such as Jan-12, Feb-12,…, Dec-12). I formatted the column for the data as Date and custom “Mar-12”. However, the pivot table will not recognize this column as date. It ALWAYS consider it as text and sort it A-Z or Z-A. I need the pivot table to recognize this column label as date so that I can sort it “Oldest-Newest or Newest-Oldest. Any ideas?
I have this same problem. A straightforward date that looks like 01/01/2013 is sorted before 01/02/2012 even though it is formatted as date and even though another cell that refers to it and adds 2 reads 01/03/2012. There is no opportunity to sort as oldest-newest, only A to Z.
I have the same issue when using the “Date” field as a Row Label. I moved the “Date” field to Values, and chose “Sum of”, then just adjusted the number format to Date. This allows me to sort another field by the Value of the “Date” field. (e.g. I have an “Opportunity Number” field in my Row Labels, I select More Sort Options, then Ascending (A to Z) by: “Date” – note: Excel only allows sorting Row Label fields by Value fields). This is the only way I can figure date sorting when all the other suggestions above don’t affect anything in the pivot table. I do not know if I will lose any other functionality from making the “Date” field a value. I already have other fiscal time periods in my Report Filters that will allow me to select certain time periods, so hopefully not.
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!
Nice tip, Debra, now saved in my Excel reference file.
Doug in York PA
Great tip!
Any chance anyone has figured out how to sort a pivot table column that shows the value as a % of the row? Without manually sorting, it seems to always sort by the value (count) behind the % (so 100% is not always first or last depending on ascending or descending).
Thanks!
Thanks for the tip and for saving me from pulling my hair out!
Brilliant.. many thanks
Solved my problem! Thanks!
OMG, I love you. I have been going insane for hours trying to figure this out.
Great. Thanks. Another unintuitive Excel feature explained!
Hi ,
I don’t want sorting in Pivot table, i just want to have the same order as per my data source.How can we achieve this?
Thank you, perfect!
thanks you…I have had that problem a lot of the time…maybe I just have too many Jan’s!
This is the exact problem I was having and your solution was incredibly simple. Thank you very much for sharing.
Ah. So that’s why “Sun” always appears at the top…
Thanks Debra.
I have created two pivot tables using the same data – within the same workbook – on the original pivot table I can get the “date” field to sort – no problems. On the new pivot table that I created in the workbook so I could “add measure” and insert text – I didn’t have the same options for my date field – I couldn’t specify “product” in the field settings so I added the dates as “add measure” – and it won’t sort at all – no matter what I do. It’s formatted as a “date” but it doesn’t work. It seems like if I want to use “add measure” there are quite a few things (like just having a date column as “product”) that does not work. It seems like I can either have my text or my date sorted but not both. It’s maddening!
Doesn’t work at all for me, unfortunately. Using Office 365 ProPlus.
This doesn’t work for me at all. The column that never sorts correctly is a list of stock symbols. As the pivot table is updated over time it just adds the new stock symbol on the end of the list. So this was no help.
Lisa, try this solution on my Contextures website, for seeing new pivot items in the correct order:
https://www.contextures.com/excel-pivot-table-sorting.html#videoorder
— Debra
Thank You for the information!
You put a smile on my face today. This issue hit me and I couldn’t understand why ‘MAY’ was listed first on an alphabet sort. Been playing with pivot tables for 10 years and never hit this condition. Sure enough, the box was checked.
Thank you for the tip and brightening my day!
You’re welcome, z, and thanks for letting me know that the tip fixed your pivot table problem!
Thanks Heaps
I cant seem to sort in order of largest to smallest for Debit column. there are figures in there with decimal points. The sort option is either greyed out or if i sort both date and transaction column to largest to smallest by Debit it sorts it but not really. it seems to still group the dates which doesnt allow for the sort.
You’re a lifesaver!!