Excel Pivot Table Sort Date by Month: Full Guide

Charts & pivot tables · Updated November 22, 2026

To sort a pivot table by month in Excel, right-click any date cell in the row labels, choose Sort > More Sort Options, and under AutoSort select Ascending (A to Z) by the date field—Excel automatically groups dates by month and sorts them chronologically (Jan, Feb, Mar…). If your dates appear as text or in the wrong order, you may need to format the source data as real dates or adjust the field grouping.

Why Dates Sort Incorrectly in Pivot Tables

Pivot tables sort dates based on the underlying data type. If your source column contains actual Excel dates (serial numbers), the pivot table recognizes them and groups them by month, quarter, or year. However, if the dates are stored as text (e.g., “Jan 2023” or “01/15/2023” entered as a string), Excel treats them as alphabetical text, so sorting yields “Apr”, “Aug”, “Feb”, etc., instead of calendar order. This is the most common cause of month sorting problems.

Another issue: if your pivot table has multiple date fields (e.g., both a transaction date and a due date), you must ensure you’re sorting by the correct field. Also, if you’ve grouped dates manually (right-click > Group), the sort order follows the grouping order you set, not necessarily chronological.

How to Sort by Month in a Pivot Table (Step-by-Step)

Here’s the exact process for Microsoft 365 on Windows (Mac steps differ slightly, noted below):

  1. Click anywhere inside the pivot table to activate the PivotTable Analyze tab.
  2. Right-click a date cell in the row labels (e.g., the cell showing “Jan”).
  3. Select Sort > More Sort Options.
  4. In the Sort dialog, under AutoSort, choose Ascending (A to Z) by the date field name (e.g., “Order Date”).
  5. Click OK. The months now appear in calendar order.

On Mac, right-click the date cell, choose Sort > More Sort Options, and the same dialog appears. For Excel for the web, right-click the date cell, select Sort > Sort Ascending—but note that the web version may not offer the full sort dialog; you may need to sort the source data first.

Grouping Dates by Month (and Why It Matters)

If your pivot table shows individual dates (e.g., 1/1/2023, 1/2/2023…) instead of months, you need to group them. Right-click any date cell, choose Group, then select Months (and optionally Years) in the Grouping dialog. This creates a new field (e.g., “Order Date (Months)”) that displays month names or numbers. Grouping also changes how sorting works: after grouping, the pivot table sorts by the underlying date serial number, not the displayed text, so months order correctly.

If you group by both Years and Months, Excel sorts by year first, then month within each year—which is usually what you want for a multi-year timeline.

Fixing Text Dates That Won’t Sort by Month

If your dates are text, the pivot table sorts alphabetically. To fix, convert the text to real dates. Use the DATEVALUE function in a helper column: with dates in A2:A20, put =DATEVALUE(A2) in B2 and copy down. Then format column B as a date (Home > Number Format > Short Date). If the text is like “Jan 2023”, you can use =DATEVALUE("1 "&A2) to prepend a day. After converting, refresh the pivot table (right-click > Refresh) and re-group.

Alternatively, use Excel’s Text to Columns wizard: select the date column, go to Data > Text to Columns, choose Delimited, click Next twice, and in step 3 select Date and choose MDY or YMD as appropriate. This converts text to real dates in place.

Common Sorting Problems and Solutions

  • Months appear as numbers (1, 2, 3…) but sort as text (10, 11, 12 before 2). Solution: format the month field as a number or use the grouping method to create a proper month field.
  • Sort order changes after refreshing. This happens if the source data has new dates or if the pivot table is set to “Sort Descending” by a value field (like Sum of Sales). To force a fixed sort, use the More Sort Options and choose “Manual” then drag items, but note that manual sorting is lost on refresh unless you reapply.
  • Dates are grouped by quarter or year unexpectedly. Right-click a date cell, choose Group, and uncheck Quarters or Years, leaving only Months.
  • Blank rows appear for missing months. This is normal if your data has gaps; to show all months, right-click the date field, choose Field Settings, Layout & Print tab, and check “Show items with no data”.

Sorting by Month Name vs. Chronological Order

A common misconception is that sorting by month name alphabetically (Apr, Aug, Dec…) is the same as chronological. It isn’t. If you have a text field like “January”, “February”, etc., sorting A-Z gives February, January, March—wrong. To sort chronologically, you must either use real dates (with grouping) or create a custom list. To create a custom list: go to File > Options > Advanced > Edit Custom Lists, add a list like “January, February, March…”, then in the pivot table, right-click the month field, choose Sort > More Sort Options, and under AutoSort select “Manual” and use the custom list by dragging items. This works but is less reliable than using real dates.

Using Slicers and Timelines to Control Date Order

Slicers and timelines don’t directly sort the pivot table, but they affect what you see. A timeline (Insert > Timeline) lets you filter by date range, and the timeline itself always displays dates chronologically. If you use a slicer for months, the slicer buttons appear in the same order as the pivot table’s row labels—so if your pivot table is sorted correctly, the slicer will be too. To reorder slicer buttons, you must reorder the underlying pivot table field.

Frequently Asked Questions

Why does my pivot table sort dates as text even though they look like dates?

This usually means the source cells are formatted as text, or they contain leading/trailing spaces or non-breaking characters. Check the cell alignment: text is left-aligned by default, while dates are right-aligned. Use the ISNUMBER function on a sample cell: =ISNUMBER(A2) returns TRUE if it’s a real date. If FALSE, convert using DATEVALUE or Text to Columns.

How do I sort months in a specific custom order (e.g., fiscal year starting in July)?

Create a helper column in your source data that assigns a month number based on your fiscal year. For example, if fiscal year starts in July, use =MOD(MONTH(A2)-7,12)+1 to get 1 for July, 2 for August, etc. Add this column to your pivot table and sort by it. Alternatively, use a custom list as described above.

Can I sort by month and year at the same time?

Yes. Group your date field by both Years and Months. The pivot table will show years as parent rows and months as child rows, sorted chronologically within each year. If you want a single column like “Jan 2023”, create a calculated field or use a helper column with =TEXT(A2,"mmm yyyy")—but note that TEXT returns text, so you’ll lose chronological sorting unless you also keep a numeric sort key.

Why does my pivot table revert to alphabetical order after I refresh?

This happens when the pivot table is set to sort by a value field (e.g., Sum of Sales) that changes with new data. To keep a fixed chronological sort, right-click the date field, choose Sort > More Sort Options, and select “Ascending (A to Z)” by the date field itself. Also, avoid using the “Sort by” option that references a value field if you want consistent month order.

Is there a way to sort months without grouping?

Yes, but only if your source data has a numeric month column (1–12) or a real date column. Without grouping, the pivot table will show each individual date, not months. To get month-level sorting without grouping, create a helper column like =MONTH(A2) and use that as a row field, then sort by that numeric field. But grouping is the standard approach because it also aggregates data by month.

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.