Forum Discussion
Pivot Table sorting is not working. Name are sorting like month names.
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.
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".