Pivot Table Issues

Fix Month Sorting and Date Formats in Excel Data Model PivotTables

Chanuka GeekiyanageChanuka Geekiyanage Nov 20, 2026 869 views

Question details

The user wants an Excel PivotTable to display months instead of full dates and sort them in calendar order.

Fix Month Sorting and Date Formats in Excel Data Model PivotTables
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the data model

In Excel, select Power Pivot > Manage and open the table containing the date column.

2
Create month columns

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.

3
Set the sort column

Select the Month Name column, choose Home > Sort by Column, and set Month Number as the column to sort by.

4
Update the PivotTable

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.

5
Check the order

Open the month field's row-label filter and choose an ascending sort if needed. Confirm that the months appear from January through December.

Sort Data Model month names by a month number
Month names combine years: A month-name field alone groups the same month from every year together. Add a separate year field to Rows when the report must show each year separately.
Free Microsoft Office alternative

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.

Create and review spreadsheet summaries with PivotTable toolsOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar interface for everyday spreadsheet tasksMove compatible files between Excel and WPS Office
microsoft office alternative - wps office

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.