Excel Pivot Table: How to Group Dates by Month

In Excel for Microsoft 365 on Windows, click a date in the PivotTable, right-click, choose Group, select Months, and click OK. If your data spans more than one year and you want each year’s months kept separate, select both Months and Years in the grouping dialog.

What date grouping does

Grouping changes how a PivotTable organizes its date field; it does not rewrite the dates in your source data. Excel creates grouped categories, such as months or quarters, so you can summarize values by a time period instead of seeing a separate row for every date. A daily sales list, for example, can become a concise monthly sales summary.

When you group dates by month, Excel recognizes calendar months rather than combining every block of 30 days. A date such as March 18 is counted in March, and a date such as April 2 is counted in April. The underlying source dates remain available, and you can usually expand or collapse the grouped levels in the PivotTable.

Grouping is especially useful when the source contains many daily records. It reduces clutter, makes month-to-month comparisons easier, and gives charts a more readable monthly axis. It is also reversible: you can ungroup the field or change the selected time intervals later.

Group dates by month in Excel for Windows

These steps assume the PivotTable already exists and its date field is placed in the Rows or Columns area. The field may be named Date, Order Date, or something similar.

Click any individual date displayed in the PivotTable—not a total or a value cell.
Right-click that date and choose Group. The Grouping dialog should appear.
In the By list, select Months. Clear other intervals if you want month groups only.
If the source covers multiple years and each year should remain distinct, also select Years.
Check the displayed starting and ending dates if Excel has filled them in, then click OK.

The PivotTable will show month groups in place of individual dates. Depending on the options selected and the PivotTable layout, Excel may display separate fields for the grouped intervals, such as Months and Years. You can move those fields between Rows, Columns, and Filters in the PivotTable Fields pane to change the report layout.

To build a PivotTable first, select a cell in the source data and choose Insert > PivotTable. Confirm the table or range and where to place the report, then add the date field to Rows and a numeric field—such as Sales—to Values. Once the dates appear in the PivotTable, use the grouping steps above.

Choose the right month-and-year view

A frequent source of confusing results is selecting Months without Years when the source contains several years. Excel then groups all Januaries together, all Februaries together, and so on. That can be correct for a question such as “How much do we sell in January across all years?” It is not the right view if you need to compare January 2024 with January 2025.

For a chronological report by month, select both Months and Years. That keeps a month attached to its year, so the report can distinguish January 2024 from January 2025. If you only need a total for each calendar year, select Years instead. For quarterly reporting, select Quarters and Years when the PivotTable spans multiple years.

Consider a source with orders dated November 2024 through February 2025. Grouping by Months alone can put both Novembers in one group if the source includes more than one November. Grouping by Months and Years produces distinct year-month combinations, which is usually the clearest choice for a trend report.

Make sure Excel recognizes the source dates

Excel can group values that it recognizes as dates. A cell that merely looks like a date may instead contain text, especially if it was imported from a website, CSV file, or another system. Text dates can be inconsistent in separators and ordering: 04/05/2025 can mean April 5 or May 4 depending on the date convention used when it was imported.

Check the source date column before troubleshooting the PivotTable. Choose a few cells and temporarily apply a date format or General format. Real Excel dates are stored as numbers and can be formatted in different date styles; text generally remains text. Formatting alone does not convert text into a real date. If conversion is needed, use a method that matches the source’s date order and regional settings, then check several converted values before refreshing the PivotTable.

Also inspect the whole field for blank cells, error values, or entries such as “Unknown” mixed in with dates. These can prevent grouping or leave records outside the expected date groups. A reliable source column has a consistent date value in every relevant row, with a clear column heading and no stray text values.

Fix common date-grouping errors

If Group is unavailable or Excel reports that it cannot group the selection, the issue is usually with the field or the PivotTable’s source. Check for text dates, blanks, errors, or mixed types throughout the entire source column—not just the visible part of the PivotTable. Correct the source, refresh the PivotTable, and try grouping again.

Some dates are text: Convert them to genuine Excel dates using the correct date order, then refresh.
There are blanks or non-date entries: Fill, remove, or otherwise handle those records consistently before grouping.
The PivotTable uses the Data Model or an OLAP source: Standard PivotTable grouping may not be available in the same way. Create year or month fields in the model or source, or use a proper calendar/date table.
The grouped report omits new records: Confirm that new rows are inside the PivotTable’s source range, then refresh. Using an Excel Table as the source helps the source range expand when rows are added.

To refresh a PivotTable in Windows, right-click inside it and choose Refresh, or use PivotTable Analyze > Refresh. Refreshing updates the report from its source; it does not necessarily correct malformed date values. If the source range itself excludes new rows, adjust the PivotTable’s data source or base it on an Excel Table so new entries can be included.

Control the display and sort order

After grouping, you can change the date display without changing the grouping. Select a month label or grouped date field, open the number-format options, and choose a format such as a full month name or a short month name. A year-month display is often useful in compact reports because it makes the period unambiguous.

Use the grouped Year field as well as the Month field when the report covers multiple years. This avoids labels that show only Jan, Feb, and Mar with no indication of which year they represent. If you need one label per month-year pair in a single field, consider the source-column alternative below; it offers control over the label and can be easier to use in some charts.

Month names in a PivotTable are calendar categories, not text values that should be alphabetized. If a report looks out of order, check whether it is sorted by a value, a custom list, or a text month field instead of the grouped date field. A genuine grouped date field normally follows calendar order. For presentation, keep year and month levels visible or use a year-month key to make the sequence clear.

Use a helper column when grouping is unavailable

A helper column is a practical alternative if the source is unsuitable for grouping, you need a custom month-year key, or the PivotTable uses a source that does not support ordinary date grouping. It creates a real date representing the first day of each source date’s month. You can then place that field in the PivotTable and format it as a month or year-month.

For example, with transaction dates in A2:A20, add a heading such as Month Start in B1. In B2, enter =DATE(YEAR(A2),MONTH(A2),1) and fill the formula down. If A2 contains March 18, 2025, the formula returns March 1, 2025; the day is set to 1 while the original year and month are retained. Format the helper column as mmm yyyy to display Mar 2025, while keeping an actual date value for sorting and PivotTable use.

After adding the helper field, refresh the PivotTable and use Month Start as the row field. The formula assumes the source dates in A2:A20 are real Excel dates; it will not reliably repair text dates by itself. A date-valued key is generally preferable to a text label because it sorts chronologically across years without depending on alphabetical order.

Excel for Mac and Excel for the web

The instructions above target Excel for Microsoft 365 on Windows. In Excel for Mac, the same general operation is available in desktop PivotTables: select a date item, open its context menu, choose Group, and select the desired intervals. The exact context-menu presentation can differ by Mac version and input device.

Excel for the web has a different feature set and interface, and date-grouping commands may not be available for every PivotTable or source. If you cannot find Group in the web interface, open the workbook in the desktop app and apply the grouping there. The grouped report can then be viewed in the web version, subject to the workbook’s other feature limitations.

Frequently Asked Questions

Why does my PivotTable show months from different years together?
That happens when you group by Months without Years. Select a date in the PivotTable, choose Group, and select both Months and Years to keep each month attached to its calendar year.

Can I group a PivotTable by month and quarter at the same time?
Yes. In the Grouping dialog, select the intervals you need, such as Months and Quarters, and include Years when the source covers multiple years. The resulting fields can be arranged in the PivotTable to show the levels that suit the report.

How do I undo month grouping?
Right-click a grouped date or month field in the PivotTable and choose Ungroup. This removes the grouping from the report; it does not alter the original date values in the source data.

Will grouping change my original dates?
No. Grouping changes the way the PivotTable summarizes the date field. The original source dates remain unchanged, so you can ungroup the field or use the daily dates in another report.

Why is the Group command missing?
Check for blanks, text, errors, or other non-date values in the source date field. Also check whether the PivotTable uses the Data Model or an OLAP source, where the usual grouping command may not be supported; a helper date field or calendar table can be a better option.

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.