Forum Discussion
Pivot Table sorting is not working. Name are sorting like month names.
I have a pivot table with Names and Data for each name. I select the LastName column as the first Row in the pivot table. Excel sorts the name March to the top of the list thinking it is a data with the month of March. I have made sure there are no hidden characters, turned off custom sort lists. Nothing works. This is a serios issue because if I need data for Mr March and scroll down to where it should be, I would conclude ther is no data for him. This should work as expected and not have to try some work around like i was an IT expert. Nothing has worked even applying the knowledge I have about programming.
Any ideas?
Excel can give month names priority through its custom lists, even when “March” is someone’s surname. That doesn’t mean the value has been converted to a date.
Since you’ve already turned custom lists off, the remaining step may be to reapply the sort to the existing PivotTable:
- Right-click inside the PivotTable > PivotTable Options > Totals & Filters. Confirm Use Custom Lists when sorting is unchecked. This is the PivotTable-specific setting, not just the general Excel option.
- Right-click an actual surname in the LastName field > Sort > More Sort Options.
- Choose Ascending (A to Z) by LastName, then click OK.
Turning off custom lists doesn’t necessarily rearrange items that were already sorted. Explicitly sorting the LastName field again should place March under M.
If you’ve already done exactly that and March still stays first, create a small new PivotTable from the same source, with only LastName in Rows, and repeat the steps. If that sorts correctly, the issue is with the original PivotTable’s saved ordering rather than your names—you shouldn’t need to alter Mr March’s name to fix it.
5 Replies
Thank you.
It just shouldn't be an issue to start with. If I sort a text field it should sort as text. A good fix would be if Excel had a popup that said, Some of your test appears to be dates. Do you want them sorted as 1. Dates or 2. Text (normal words)
Thanks I was able to get this to sort. But it shouldn't be an issue in the first place. If I sort a text field it should sort as text, not have Excel try to guess what I want. At least it could prompt something like; Some of the text appears to be dates, do you want them sorted as DATE or TEXT.
Thanks. That does work.
My issue is if I make a pivot table where only 5 of 11,000 names are the same as a month, how is a user supposed to know that it will sort incorrectly. A column formatted as text should by default sort as text. Now that I know this, everytime I have Excel sort, which should take less than 5 seconds, I have to check to see how it sorted it which will take a lot longer.
An easy fix would be to have a popup like it does when a column has actual dates and text. I get that often, where a file will have termination dates and "Still Employed" in the same column. The popup could say, "Some Items Appear to be Dates, Do you want that sorted as DATES or TEXT".
Make sure you switch "Auto Sort" off under Sort, More Sort Options... , More Options... and keep the sort order at "No Calculation" as shown below. That will stop Excel from thinking certain names are months.
Excel can give month names priority through its custom lists, even when “March” is someone’s surname. That doesn’t mean the value has been converted to a date.
Since you’ve already turned custom lists off, the remaining step may be to reapply the sort to the existing PivotTable:
- Right-click inside the PivotTable > PivotTable Options > Totals & Filters. Confirm Use Custom Lists when sorting is unchecked. This is the PivotTable-specific setting, not just the general Excel option.
- Right-click an actual surname in the LastName field > Sort > More Sort Options.
- Choose Ascending (A to Z) by LastName, then click OK.
Turning off custom lists doesn’t necessarily rearrange items that were already sorted. Explicitly sorting the LastName field again should place March under M.
If you’ve already done exactly that and March still stays first, create a small new PivotTable from the same source, with only LastName in Rows, and repeat the steps. If that sorts correctly, the issue is with the original PivotTable’s saved ordering rather than your names—you shouldn’t need to alter Mr March’s name to fix it.
