Excel pivot table group by month?

To group dates by month in an Excel PivotTable, place the date field in Rows or Columns, right-click any date in the PivotTable, choose Group, select Months, and click OK. If your data covers more than one year, select Years as well so that January from different years is not combined into one total.

What monthly grouping does

Grouping changes how a PivotTable organizes date values; it does not rewrite the dates in your source data. Instead of showing a separate row for every transaction date, Excel creates broader groups such as months, quarters, or years. You can then summarize a measure—such as sales, hours, or order count—within each group.

For example, a source list with one row per sale might contain dates such as March 3, March 14, and March 28. Grouping the date field by month puts those records together under March, where the PivotTable can show their combined sales. The underlying records remain individual transactions, so you can still drill into or expand the summary when needed.

Grouping is most useful when the source contains actual Excel dates and you want a flexible summary. It lets you change the report layout or add other fields without maintaining a separate monthly summary table. You can group a date field in either the Rows or Columns area, though dates are commonly placed in Rows and a numeric measure in Values.

How to group dates by month in Excel for Windows

Make sure the source data has a column containing dates, with a clear heading such as Order Date. Each row should represent a record, and the date column should not contain a text heading inside the records.
Click inside the source data and choose Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK.
In the PivotTable Fields pane, drag the date field to Rows. Drag a numeric field, such as Sales, to Values. Excel will usually summarize numeric values by sum; if it shows Count, check that the source values are numbers.
In the PivotTable, right-click one of the displayed date values and choose Group. You can also select a date and use PivotTable Analyze > Group > Group Field on the ribbon.
In the Grouping dialog box, select Months. If the data spans multiple calendar years, also select Years. Clear other selected time units if you do not want them in the grouping.
Click OK. Excel replaces the individual date labels with grouped date fields. Arrange the resulting Years and Months fields in the Rows area as needed.

The contextual ribbon tab may appear as PivotTable Analyze when the PivotTable is selected. On a Mac, the PivotTable tools and ribbon layout can differ by Excel version; the right-click grouping command is a useful alternative when available. Excel for the web may not expose every desktop PivotTable command, so use Excel for Microsoft 365 on Windows if the Group option is missing.

Why you should usually include Years

A month name is not unique across years. If a PivotTable groups a three-year date range by Months alone, it can combine every January into one January total, every February into one February total, and so on. That can be useful for a seasonal comparison, but it is often wrong when the goal is to report each month in sequence.

For a year-by-year report, select both Months and Years in the Grouping dialog. The PivotTable then distinguishes, for example, January 2024 from January 2025. Depending on the field layout, Excel may display Years and Months as separate nested fields. Keep Years above Months in Rows for a conventional hierarchy, and expand or collapse the year groups with the PivotTable controls.

Choose Months alone when you intentionally want to compare the same calendar month across years—for instance, to see whether average January sales differ from average July sales. If you need both views, keep Years and Months in the PivotTable and use filtering or rearrange the fields rather than relying on a single month-name total.

Arrange and format the monthly summary

After grouping, place the date groups and measures where they make the report easiest to read. A common layout has Years and Months in Rows, Sales in Values, and Region in Columns or Filters. You can add a second measure, such as transaction count, to Values to compare volume with revenue. Use the Value Field Settings command if you need to switch between Sum, Count, Average, or another available summary.

Excel may show month labels in a date style or as month names. If labels are ambiguous, retain the year group or use a date-based month field that includes the year. Avoid treating a label such as “Jan” as a unique date: it is only a display label unless the year is also represented.

To remove grouping, right-click a grouped label and choose Ungroup. The original date values return to the PivotTable; this does not remove or alter dates in the source. You can then regroup using a different combination, such as quarters and years.

Check the source dates before troubleshooting

Excel can group a PivotTable date field only when the field contains usable date values. A cell can look like a date while actually storing text, especially when data was copied from a website, imported from a system, or interpreted using a different regional date order. Changing the cell’s number format changes its appearance, but it does not necessarily convert text into a real date.

Check the source column for the following issues before trying again:

Blank cells: Fill or remove missing dates if they are not meaningful. A blank within the date field can prevent grouping.
Text dates: Convert text values into actual Excel dates, using a conversion method appropriate to the source format. Be careful with ambiguous entries such as 03/04/2025, which can mean different dates in different locales.
Errors or mixed values: Correct cells containing errors, notes, or labels among the dates. A date column should contain consistent date values for its records.
Incorrect source range: Confirm that the PivotTable’s source includes the intended date column and records, and that the heading row is separate from the data.

If Excel says it cannot group the selection, inspect the entire source date field, not just the date currently visible in the PivotTable. One blank or text entry in a long list may be enough to cause the problem. Also note that manual grouping is restricted for some PivotTables built from OLAP sources or the Data Model; if Group is unavailable in that context, use a date attribute supplied by the model or add a suitable date field to the model.

Alternatives when you need a month field

For some reports, a separate month-start date in the source is easier to use than grouping inside the PivotTable. This can be helpful when grouping is unavailable, when several PivotTables need the same month key, or when you want the source data to make the reporting period explicit. Keep the original transaction date too, so daily detail remains available.

With transaction dates in A2:A20, enter this formula in a new helper column beside the first record and fill it down:

=DATE(YEAR(A2),MONTH(A2),1)

If A2 contains July 19, 2025, the formula returns July 1, 2025. Each date in July becomes the same first-of-month date, while dates in other months receive their own month-start date. Add that helper field to the PivotTable instead of the original transaction date. Format the helper values as a date or use a custom display format such as mmm yyyy so the year remains visible. The helper is still a date value, not merely a text month name.

Another option is to create a monthly summary table outside the PivotTable, but it is less flexible if the data changes or readers need to drill into individual records. A helper field preserves PivotTable summarizing and filtering while making the month key explicit. Refresh the PivotTable after changing or extending its source data.

Refresh behavior and common reporting choices

A PivotTable does not always update just because a source cell changes. Select the PivotTable and choose PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. To refresh all PivotTables and connections in the workbook, use Data > Refresh All. If you add records below a fixed source range, confirm that the new records are included; using an Excel Table as the source makes its range expand as rows are added, though the PivotTable still needs refreshing.

When new dates fall outside the range represented by an existing grouped field, check the grouping boundaries and regroup if the new records do not appear as expected. Do not assume a missing month means there were no transactions: the PivotTable may simply have no source records for that period. If the report must display every calendar month, including months with zero activity, consider using a complete calendar table or a separate reporting structure designed to include empty periods.

Before sharing a report, confirm the unit of analysis. Grouping by month summarizes records by calendar month; it does not calculate a rolling 30-day period. Likewise, grouping by quarter follows calendar quarters unless you have configured a different fiscal calendar in your data model or reporting logic. For fiscal periods that do not match calendar periods, a dedicated period field or calendar table is often clearer than relying on the default date grouping.

Frequently Asked Questions

Why is Group grayed out in my PivotTable?
Check whether the date field is selected and whether the PivotTable is based on ordinary worksheet data rather than an OLAP source or the Data Model. For a standard PivotTable, inspect the source date column for blanks, text dates, errors, or mixed values. Those issues commonly prevent grouping.

How do I group dates by month and year?
Right-click a date in the PivotTable, choose Group, then select both Months and Years in the dialog box. This keeps the same month in different years separate, such as January 2024 and January 2025.

Can I group by month without combining years?
Yes. Select both Months and Years. Selecting Months alone can combine matching month names from every year in the source range, which is appropriate only when you deliberately want a cross-year month comparison.

How do I show months with no data?
A standard PivotTable may omit periods that have no source records. A complete calendar table or a model with a calendar dimension can provide all months, including empty ones; the exact setup depends on how the workbook’s data is organized.

Will grouping change my source dates?
No. Grouping changes the PivotTable’s display and summary levels, not the dates in the source table. Ungrouping restores the detailed date labels in the PivotTable without undoing or rewriting source data.

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.