Excel Pivot Table Sorting Problems

Usually, it’s easy to sort an Excel pivot table – just click the drop down arrow, and select one of the sort options.

sortpivotcustomlist00

Every now and then, the pivot table doesn’t 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, Jan is at the top of the list, instead of Ann.

sortpivotcustomlist01

Why the Sort is Incorrect

In this example, Jan is at the top of the list, because Excel assumes Jan means January, and is in one of Excel’s built-in custom lists. In addition to the built-in custom lists, you might have created your own custom lists, such as districts or departments.

Those custom lists take precedence when you’re sorting labels in a pivot table. Fortunately, if things don’t sort correctly, 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

Now, the list of names changes, and Ann is shown at the top, instead of Jan.

sortpivotcustomlist03

____________

You may also like...

26 Responses

  1. Handy, not a problem I’ve come across before but worth bearing in mind

  2. Thanks Debra nice tip. Glad I am part of your mailing list!!

  3. No, don’t turn that off, I like being at the top! :-)

  4. Ha! Hadn’t thought of that reason for leaving the custom list option turned on.

  5. Bruce Watt says:

    Thanks so much, columns with dates were not sorting correctly. I was starting to curse Excel 2010. This fixed the problem.

  6. Nader Numair says:

    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?

    • David Lind says:

      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.

      • Rusty says:

        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.

  7. Chris says:

    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.

  8. Cj says:

    Very good information! This helps a lot.

  9. Douglas Eckert says:

    I am still unable to sort by the PAYMENT field. The option to sort from top to bottom is greyed out. Suggestions are welcome.

  10. Nathan says:

    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.

  11. abid says:

    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

  12. Shana says:

    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

  13. Nice tip, Debra, now saved in my Excel reference file.

    Doug in York PA

  14. MJ says:

    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!

  15. Bearden says:

    Thanks for the tip and for saving me from pulling my hair out!

  16. Sean says:

    Brilliant.. many thanks

  17. Kevin Johnston says:

    Solved my problem! Thanks!

  18. Jeremy says:

    OMG, I love you. I have been going insane for hours trying to figure this out.

  19. chmollo says:

    Great. Thanks. Another unintuitive Excel feature explained!

  20. ss says:

    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?

  21. antonio silva says:

    Thank you, perfect!

  22. Slot Pilot says:

    thanks you…I have had that problem a lot of the time…maybe I just have too many Jan’s!

  1. November 26, 2013

    […] Nach dem ich das Problem erkannt habe, konnte ich dann auch einen guten Beitrag auf Englisch bei Deobrah Dalgleish […]

Leave a Reply to Nader Numair Cancel reply

Your email address will not be published. Required fields are marked *