Forum Discussion
Date Format: Pivot Table vs Source Data
Perhaps you have texts which looks like date, not dates which are actually (in behind) numbers.
SergeiBaklan What if one of the things being sorted on is an aggregated group name abbreviation that comes out to something like "JAN" and the pivot table seems to be auto interpreting it as "January" despite all data types being set appropriately as Text and non-pivot tables with the same Data Type settings are sorting alphabetically appropriately?
example:
- Column Header "Group Name".
- All "Group Name" cells set as Text data type.
- In Pivot Table all "Group Name" column cells set as Text data type.
- Multiple rows with "Group Name" values of "JAN" and "AST".
- On source data sorting by "Group Name" sorts as:
- AST
- JAN
- On pivot table sorting by "Group Name" sorts as:
- JAN
- AST
Some Screenshots below:
Data Source unsorted:
Data Source Sorted:Pivot Table Sorted:
Is something like this just an extremely native limitation of current Excel and its ability to respect Data Types when creating a pivot table with source data at the moment? Doesn't seem like there are many resources specifically about this sorting issue and seems like you can't edit the default 3 letter sort list for the months in Excel's advanced options.
- SergeiBaklanJan 31, 2024Diamond Contributor
In PivotTable
More Sort Options->More Options->disable Sort Automatically->choose No Calculations
- OrinWhitener2787Jan 31, 2024Copper ContributorAwesome, thank you for such a prompt reply. That was exactly what I needed.
- SergeiBaklanFeb 01, 2024Diamond Contributor
OrinWhitener2787 , you are welcome