Thread: Problem sorting names in pivot table

Hello,

can you post a small data sample and the pivot table that shows that behaviour? It's kinda hard to picture why a text field should be sorted as a month or day.

cheers,

Hi Teylyn,

For some reason, I'm unable to "reply with quote".

Attached is an example of the problem. I have anonymised the given names. There are three people with the family name of "Sun" and one with "May". I also tried using "June" and that also appeared at the top of the list. I have also tried formating the source entries as text, but it doesn't make any difference.

Cheers,
John

For some reason, I'm unable to "reply with quote".

Good. Why would you want to quote, anyway. You're responding directly to the previous post.

Excel recognises the words Sun or May as elements of the custom lists for weekdays and months. Therefore they will be sorted before any other text. As far as I'm concerned, this comes very close to a bug. There are two possible workarounds that I see:

1. Set the field sorting to manual and drag the rogue names into their correct places. They will stay in that order when the pivot table is refreshed. In my tests, the manual sort order was adhered to even after I deleted Sun and May from the source, refreshed the pivot, added Sun and May to the source and refreshed the pivot again.
So, this is a one-time manual effort.

2. Delete the custom lists for the weekdays and the months. This is probably not advisable, as it would prevent the proper sorting of days and months in other aspects of Excel.

cheers,