Excel Pivot Table Sorting Problems

Pivot Table Sorting Problems

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.

drop down arrow in a pivot table heading
drop down arrow in a pivot table heading

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.

 Jan is at the top of the list
Jan is at the top of the list

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:

  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”
PivotTable Options dialog box
PivotTable Options dialog box

Sorting Correctly

After you adjust that pivot table setting, the list of names changes, and Ann is shown at the top, instead of Jan.

names in alphabetical order
names in alphabetical order

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.

____________

40 thoughts on “Excel Pivot Table Sorting Problems”

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

  2. 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?

    1. 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.

      1. 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.

  3. 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.

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

  5. 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.

  6. 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

  7. 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

    1. 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.

      1. 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!

  8. 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!

  9. 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?

  10. This is the exact problem I was having and your solution was incredibly simple. Thank you very much for sharing.

  11. 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!

  12. 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.

  13. 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!

  14. 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.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.