Fix Month Sorting and Date Formats in Excel Data Model PivotTables
Question details
The user wants an Excel PivotTable to display months instead of full dates and sort them in calendar order.

- Product
- Microsoft Excel PivotTables and Data Model
- Device & OS
- not provided
- Scenario
- A PivotTable uses the Excel Data Model to summarize records by month.
- Observed behavior
- The PivotTable displays full dates or sorts month names incorrectly compared with a standard PivotTable.
Check that the source date column contains valid dates rather than text. Determine whether the PivotTable uses the Data Model, because grouping and sorting options can differ from those in a standard PivotTable.
Sort Data Model month names by a month number
Use a month-name column and a numeric sort column when a Data Model PivotTable sorts months alphabetically.
This method requires access to the Power Pivot window. Menu availability can vary by Excel edition.
In Excel, select Power Pivot > Manage and open the table containing the date column.
In the Power Pivot table, add calculated columns such as Month Name with =FORMAT([Date],"mmmm") and Month Number with =MONTH([Date]). Replace Date with the name of your date column.
Select the Month Name column, choose Home > Sort by Column, and set Month Number as the column to sort by.
Return to the PivotTable, select PivotTable Analyze > Refresh, and replace the full date field in Rows with Month Name. Add a year field if you need to distinguish the same months across years.
Open the month field's row-label filter and choose an ascending sort if needed. Confirm that the months appear from January through December.

Regroup and manually order dates in a standard Excel PivotTable
Use this approach when the PivotTable supports date grouping and manual sorting.
Work with spreadsheet summaries in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Excel formats, making it easy to move everyday spreadsheet work between applications. It does not fix Excel's Data Model sorting behavior.

Frequently Asked Questions
Why are my PivotTable months sorted alphabetically?
Month names may be treated as text. In a Data Model, create a numeric month column and use Power Pivot's Sort by Column setting to sort the month-name column by it.
Why can't I group dates in my PivotTable?
The PivotTable may use the Data Model, where the usual Group command may be unavailable. Invalid dates or dates stored as text can also prevent grouping in a standard PivotTable. Check the source date column and use separate month fields for a Data Model report.
How can I show months without combining different years?
Add a year field to the PivotTable Rows area above the month field. In a standard PivotTable, you can also right-click a date, select Group, and choose both Years and Months.
Does selecting Manual automatically put months in order?
No. Manual sorting lets you arrange the labels yourself. After selecting More Sort Options > Manual, drag the month labels into the order you want or use the available Move command.




