Excel Pivot Table: Sort Months Chronologically

Charts & pivot tables · Updated November 18, 2026

Yes. To sort months chronologically in an Excel PivotTable, use a real date field and group it by Months (and Years when the data spans multiple years). If your month names are text, Excel may sort them alphabetically; custom lists or a date-based helper field can restore calendar order.

Why PivotTable months can appear out of order

A PivotTable sorts according to the values in its source field, not necessarily the way those values look on the worksheet. A cell containing a genuine Excel date can be displayed as January, Jan, or 1/1/2025, but Excel still stores it as a date serial number. That underlying value gives Excel a chronological order to use when grouping.

By contrast, January typed as a word is text. Alphabetical sorting puts April before August, then December, rather than following the calendar. A list of month names can also be ambiguous if it has no year: January 2024 and January 2025 look identical if the year has been discarded.

First identify what is in the source data. If the field contains actual dates, use date grouping. If it contains only month labels, use a custom list or add a reliable date or sort key. Changing the number format of text does not turn it into a date.

Group a real date field by month

For a PivotTable based on a date column, grouping is usually the simplest solution in Excel for Microsoft 365 on Windows:

  1. Click a date item in the PivotTable, such as one of the dates listed in the Rows area.
  2. Right-click the selected date and choose Group.
  3. In the Grouping dialog, select Months. If the source covers more than one year, also select Years.
  4. Choose OK. Excel creates grouped date fields that can be arranged in the PivotTable.

Depending on the PivotTable layout and Excel build, you may see fields such as Years and Months in the field list or see the grouped labels directly in the table. Put Years above Months in the Rows area when you want separate month sequences within each year. The result should run January, February, March, and so on, rather than alphabetical order.

To show the month name without a full date, format the grouped field or use the month grouping Excel creates. Number formatting changes how a date is displayed; it does not remove the date value Excel uses for ordering.

Include the year when data spans multiple years

Grouping by Months alone can combine the same month from different years. For example, January 2024 and January 2025 may appear together as one January category, with their amounts aggregated. That can be useful for comparing all Januaries, but it is not the same as a continuous monthly timeline.

For a month-by-month report, select both Years and Months in the Grouping dialog. The hierarchy then separates January 2024 from January 2025 and keeps each year’s months in calendar order. In the PivotTable Fields pane, place Years before Months in Rows. If you prefer months across the top, put Months in Columns and retain Years as a separate field so the periods remain distinguishable.

For example, sales dated December 2024, January 2025, and February 2025 should display in that order in a continuous report. A month-only label can make the first two periods look like December followed by January, but without a year field readers cannot tell which January is meant if the report contains several years.

Check that the source values are real dates

If the Group command is unavailable, or Excel reports that the selection cannot be grouped, inspect the source column. One text value, blank, or error in a date field can prevent grouping. Dates pasted from a website or imported from a CSV file are common sources of date-like text.

  • Click a source value and check whether changing its number format to a date format changes its display. A real date will usually respond; text generally will not.
  • Look for leading or trailing spaces, inconsistent date patterns, blank cells, and error values in the source field.
  • For imported data, use Excel’s data-conversion tools or correct the source values so the whole column contains valid dates. Check ambiguous day/month formats carefully; Excel may interpret them according to regional settings.
  • After correcting the source, refresh the PivotTable and try grouping again.

Do not rely on how a cell looks alone. A text value such as 03/04/2025 can resemble a date while meaning different things under different regional conventions. Confirm the intended date before converting a column, especially if the data will be used for reporting.

Sort month names stored as text

If the source contains month names only, Excel’s built-in custom lists can sort familiar month labels in calendar order. Excel includes month-name sequences such as January through December and their common abbreviations. In the PivotTable, try sorting the month field ascending; if custom lists are enabled for PivotTable sorting, the familiar month names can follow that sequence instead of alphabetical order.

In Excel for Microsoft 365 on Windows, check the PivotTable setting by clicking inside the PivotTable, opening PivotTable Analyze > Options (the command may be labeled PivotTable Options), and looking on the Totals & Filters tab for Use Custom Lists when sorting. The exact dialog presentation can vary by build. You can also review or create lists through File > Options > Advanced > General > Edit Custom Lists. A custom list should contain the month labels in the exact form used by the source, such as Jan, Feb, Mar, through Dec.

Custom lists are convenient, but they do not create a year or a true date. They work best when the entries are standard month names or abbreviations and the report only needs the calendar sequence. If the labels include extra text, such as 2025 Jan, or use unfamiliar translations, Excel may not recognize them as the built-in sequence. In those cases, use a date field or an explicit sort key.

Use a date-based helper field for more control

When source data has separate Year and Month text columns, create a proper month-start date in the source table, then use that field in the PivotTable. For instance, each record for March 2025 can carry the date March 1, 2025 as its month key. Format the helper field to display as Mar 2025 or another useful label, then group it by Months and Years if needed. Because the field is a real date, it sorts chronologically across year boundaries.

Another approach is to add a numeric month-order field from 1 to 12 alongside the month label. This can help with a deliberately controlled layout, but simply adding the number as a second row field may show extra levels in the PivotTable. A date-based field is usually clearer for a reusable report. If a report must keep a separate text label, make sure its sort order is tied to a value that represents the year and month together, not just the month number; otherwise January from different years can be mixed together.

Sort direction and PivotTable layout

Chronological sorting usually means oldest to newest: January through December, or earlier dates before later ones. To reverse the timeline, sort newest to oldest. Right-click a date or month item and use the available sort options, or use the field’s sort controls. The labels can remain chronological even when the field is displayed in Rows rather than Columns.

Manual dragging can rearrange items in some PivotTable layouts, but it is fragile. A refresh, new source item, or changed grouping can make a hand-arranged order harder to maintain. For recurring reports, prefer date grouping or a custom list over manual placement. Also distinguish sorting from filtering: a month filter controls which items are visible, while sorting controls their order.

Refresh and common troubleshooting

PivotTables use a cache of source data. After changing dates or adding a helper field, refresh the PivotTable using PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections and PivotTables. If new source rows are not included, check that the PivotTable’s source range includes them; converting the source range into an Excel table can make future additions easier to include.

  • Group is disabled: Check for text dates, blanks, errors, or mixed data types in the source date column.
  • Months are alphabetical: The field is likely text, or custom-list sorting is not being applied. Group a real date field or use a recognized custom list.
  • Months from different years are combined: Group by Years as well as Months, or use a month-start date that retains the year.
  • New values do not appear: Refresh the PivotTable and verify its source range or table.
  • Month names are duplicated: This can happen when labels differ by spaces, spelling, or abbreviations. Standardize the source labels before relying on a custom list.

Excel for Mac and Excel for the web

The core method is the same in Excel for Mac: select a date item in the PivotTable and use the available Group command to group by Months and, when appropriate, Years. Ribbon labels and the location of PivotTable options can differ slightly between Windows and Mac, so use the contextual PivotTable tab and right-click menu if the command is not immediately visible. Custom-list controls may also be presented differently.

Excel for the web has fewer PivotTable authoring controls than the desktop app, and some grouping or options may not be available in a particular web version. If you cannot group the date field or change custom-list sorting in the browser, open the workbook in the desktop Excel app, make the change there, and save it. The resulting PivotTable can then be viewed in the browser.

Frequently Asked Questions

Why is my Excel PivotTable sorting months alphabetically?
Most often, the source field contains month names as text. Text sorts alphabetically unless Excel recognizes a month custom list. Use an actual date field and group by Months, or enable and use a matching custom list.

How do I sort months across multiple years?
Group the source date field by both Years and Months. This keeps January 2024 and January 2025 separate and maintains the correct sequence within each year.

Can I sort a PivotTable by month name without a date column?
Yes, if the labels match a built-in or custom month list and custom-list sorting is enabled. For nonstandard labels or a multi-year timeline, adding a date-based month key is more dependable.

Why can’t I group dates in my PivotTable?
The source date column may contain text, blanks, errors, or mixed values. Correct the source so every entry is a valid date, refresh the PivotTable, and try Group again.

How do I show January to December instead of December to January?
Sort the month field ascending, and confirm that it is grouped from real dates or recognized as a month custom list. If the report is intentionally a reverse timeline, choose descending order instead.

Will formatting text as a date fix the sort order?
No. Number formatting changes the appearance of a value, not its underlying type. Convert text to genuine dates or use a custom list or date-based helper field.

Related

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.