Pivot Table Sort is Too Clever

I created a pivot table from a list containing 3 character IDs, then used the Pivot Table field pulldown to sort it.


The resulting sorted list looks like this:


Looks like Pivot Table sort recognises 3 character abbreviations for

  • The day of the week
  • Months
  • A quarter given as the initial letter of the month

And decides to sort them into (some kind of mangled) time sequence and place them before the unrecognised 3-character IDs!

And I can’t find a way to make it do a proper sort!

Any ideas how you can make Excel do this in a not-so-clever but more sensible way?

Thanks to Debra, Alastair and Rory for telling me how to do it.

This entry was posted in Uncategorized. Bookmark the permalink.

6 Responses to Pivot Table Sort is Too Clever

  1. Alastair says:

    Only checked in Excel ’10 but – in the field drop-down, choose ‘More Sort Options’, then ‘More Options’, Untick the box ‘Sort Automatically every time the report is updated’ and make sure First Key Sort order is set to ‘No Calculation’

  2. Rory says:

    For later versions of Excel you can turn off the ‘Use custom lists when sorting’ option for the pivot table, but it’s all or nothing rather than field specific.

  3. fastexcel says:

    Alastair, Rory,
    Thanks: both your solutions work.

  4. AlexJ says:

    Late to the party, but, how about adding the same dummy character to each of the 3 letter abbreviations (like a following space)? You won’t have to play with sorting rules that way.

  5. Dan Czerwonky says:

    Did you try formatting the cells as text first?

  6. fastexcel says:

    Formatting as text does not make any difference for me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s