Excel Pivot Table Months Not in Order? Fix the Dates

If Excel PivotTable months are out of order, first check whether the source column contains real dates or month names stored as text. For real dates, right-click a date in the PivotTable, choose Group, select Months and Years, and click OK; for text month names, convert the source to dates or use a chronological custom list instead of relying on alphabetical sorting.

Why PivotTable months appear out of order

A PivotTable can sort values only according to the data and field structure it receives. A genuine Excel date is stored as a serial number, so Excel can recognize that March 2024 comes after February 2024. A text value such as March has no built-in date position: alphabetically, April comes before August, and December comes before February. That is why a list of month names may look scrambled even when the source rows are arranged chronologically.

There is another common cause: the source may contain dates from several years, but the PivotTable displays only month names. If you group by Months without including Years, Excel combines every January into one January category, every February into one February category, and so on. That is useful for comparing seasonal patterns, but it does not show a continuous timeline. Grouping by both Months and Years keeps January 2023 separate from January 2024.

Finally, a PivotTable may still show an old order after the source has been corrected because it has not been refreshed, or because the field is sorted manually. Identify which situation applies before changing the layout; the right fix depends on whether the values are true dates, text labels, or intentionally grouped data.

Group genuine dates by month and year

In Excel for Microsoft 365 on Windows, use this process when the source column contains valid dates:

Click a date value in the PivotTable.
Right-click and choose Group. You can also use PivotTable Analyze > Group Field after selecting a date item.
In the Grouping dialog, select Months and Years. If the PivotTable should summarize one year at a time, select Years as well as Months.
Click OK. If needed, use the expand and collapse controls to show or hide the year level.

For example, a source containing 15 January 2023, 20 February 2023, and 10 January 2024 can be grouped into a year level with January and February beneath it. Grouping by month alone instead combines the two January records. When a timeline crosses years, include Years unless the goal is specifically to compare all Januaries, all Februaries, and so on.

Excel may display the grouped field as Months or Years even if the original source column is named Transaction Date. The grouping changes the PivotTable’s view; it does not rewrite the source dates. To undo the grouping, right-click a grouped label and choose Ungroup. On Mac, the same grouping option is available from the PivotTable context menu in desktop Excel, though tab placement can vary slightly by release. Excel for the web has a more limited set of PivotTable authoring controls, so use desktop Excel if Group is unavailable there.

Check whether the source values are real dates

Changing a cell’s number format to Date does not convert text into a date. A cell can look like 1/2/2024 while still containing text, especially after data is copied from a website, exported from another system, or imported with a different regional date convention. A quick clue is that Excel often left-aligns text by default and right-aligns numbers, although alignment alone is not proof because formatting can override it.

Check a few source cells directly. Select one and look at the formula bar: a recognized date typically appears as a date value, while text may retain an apostrophe or unexpected spacing. You can also temporarily change the cell’s number format to General; a real date usually displays as a serial number. If some rows are dates and others are text, the whole column may not group consistently.

After correcting the data, refresh the PivotTable. Select a cell in it and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update all connections and PivotTables. If the source is an Excel Table, adding rows to that table is generally more reliable than maintaining a fixed range. If the PivotTable was built from a fixed cell range and new records fall outside it, use PivotTable Analyze > Change Data Source to include them.

Convert text dates without losing the original data

For a small dataset, you can sometimes select the text-date column and use Data > Text to Columns, then choose the date order that matches the source data and finish the wizard. Be careful with ambiguous values such as 03/04/2024: that can mean March 4 or April 3 depending on the source’s date convention. Do not choose a conversion order by guesswork.

A helper column is safer when you want to preserve the imported values while checking a conversion. With text dates in A2:A20 that Excel can interpret using the workbook’s regional settings, enter =DATEVALUE(A2) in B2 and fill down. For a source value of 1/15/2024 interpreted as January 15, the result is the Excel date serial for that date; format the result as a date to display it clearly. DATEVALUE cannot reliably resolve every ambiguous or nonstandard text format, so verify several converted rows against the original source before using the helper column in a PivotTable.

Once the converted column contains consistent dates, build or update the PivotTable using that field, then group it by Months and Years. If conversion returns errors, check for extra spaces, mixed formats, blanks, or date strings that do not match the computer’s regional settings. For data from a database or reporting system, correcting the date type at import is often preferable to repeatedly repairing it in the worksheet.

When the source contains month names instead of dates

If the source has labels such as Jan, Feb, and Mar, Excel may sort them alphabetically because they are text. The most dependable long-term solution is to store a real date for each record, such as the first day of the relevant month, and let the PivotTable group that date. With the original date in A2, enter =DATE(YEAR(A2),MONTH(A2),1) in a helper column and fill down. For an original date of 18 February 2024, the formula returns 1 February 2024; format the helper column as mmm yyyy or another suitable date format. Grouping this helper field by Months and Years then gives chronological categories while retaining the year.

If only month names are available and no year or day is meaningful, you can make a custom sort order. In Excel for Windows, go to File > Options > Advanced, scroll to the General section, and select Edit Custom Lists. Add the month names in calendar order, using the same spelling as the PivotTable labels. Then open PivotTable Options, go to Totals & Filters, and make sure Use Custom Lists when sorting is selected. Custom lists can affect other sorting in Excel, so check the result and consider whether the setting is appropriate for the workbook.

A custom list is convenient for a simple set of month labels, but it cannot distinguish the same month in different years. It also depends on consistent labels: Jan and January may not behave like identical items. If the report needs a real timeline, a date field is clearer and less fragile than text labels plus a custom order.

Choose the right month layout for the report

Before sorting, decide what the rows are supposed to mean. A month-only summary answers a question such as how much revenue is typically recorded in January across all available years. A year-and-month sequence answers a different question: how did revenue change month by month over time? These layouts should not be treated as interchangeable.

Compare seasons across years: group by Months only, so all Januaries roll up together. Add Years as a report filter or column field if readers need to limit the comparison.
Show a continuous trend: group by Years and Months, or use a month-start date helper. This keeps each year’s months distinct.
Show fiscal periods: do not assume calendar-month grouping matches the organization’s fiscal calendar. Use a fiscal period field from the source or a calendar table if the fiscal year starts in a month other than January.
Keep the source categories as labels: use a month number or custom list only when the source truly has labels rather than full dates, and document how the order is defined.

Grouping by Months alone can also make a chart misleading if someone expects January 2023, February 2023, and later months in sequence. A PivotChart inherits the PivotTable’s categories, so verify the underlying row fields before interpreting the visual. Including the year usually makes a chronological chart easier to understand.

Sorting controls, refreshes, and common snags

When the date field is correctly grouped but the display still looks wrong, check the field’s sort setting. Click a month label, right-click, and choose Sort, then select the appropriate ascending or descending option. For grouped dates, ascending order should run from earlier to later periods. A manually arranged order can override the expected order, so reset sorting rather than dragging labels into place if the PivotTable must update reliably.

Refresh after any source-data correction. A PivotTable uses a cached summary, so editing the worksheet does not always immediately change the displayed result. Refresh can update the values and recognize newly included rows, but it cannot repair text that remains text or add rows outside a fixed source range. Check both the underlying values and the PivotTable’s data source when a refresh appears to do nothing.

If Group is disabled or Excel reports that the selection cannot be grouped, inspect the entire source field for blanks, errors, or entries stored as text. A single inconsistent value can interfere with grouping. Also consider how the PivotTable was created: PivotTables connected to an external OLAP source or the Data Model can have different grouping capabilities from a PivotTable based on a regular worksheet range. In those cases, use a proper date or calendar field in the source or model rather than assuming ordinary worksheet grouping will be available.

Do not try to fix an ordering problem by sorting the entire source worksheet and assuming the PivotTable will inherit that order. PivotTables have their own field sorting and grouping rules. A source sort can make the original records easier to inspect, but it is not a substitute for grouping a date field or setting the PivotTable’s item order.

Frequently Asked Questions

Why are months alphabetical in my Excel PivotTable?
If the field contains month names as text, Excel treats them as words and sorts them alphabetically. Use actual dates and group by Months, or apply a custom month list when only text labels are available.

How do I sort PivotTable months chronologically across multiple years?
Group the date field by both Years and Months. If you group by Months alone, Excel combines the same month from different years into one category rather than creating a continuous timeline.

Why is the Group option greyed out?
Check for blanks, errors, or text mixed into the date column, and confirm that the PivotTable uses a source type that supports ordinary date grouping. Data Model and OLAP-based PivotTables may require a date or calendar field supplied by the model.

Will formatting text as a date fix the month order?
No. Number formatting changes how a value appears, but it does not convert text into an Excel date. Convert the values first, verify the results, and then refresh the PivotTable.

Why does my PivotTable still show the old month order after I fix the source?
Refresh the PivotTable and check its sort settings. Also confirm that the PivotTable’s source range includes the corrected rows and that the month field is not still based on text labels or a manually arranged order.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.