Excel Pivot Table: Group Dates by Month

To group dates by month in an Excel PivotTable in Microsoft 365 for Windows, put 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 as Months so January from different years is not combined into one total.

What date grouping does

Grouping changes how a PivotTable organizes and summarizes date values; it does not rewrite the dates in the source data. Instead of listing every individual date, Excel creates broader categories such as months, quarters, or years. You can then summarize a numeric field, such as sales, by those categories. For example, a table with daily transactions can show one sales total for each month.

A month is not a fixed 30-day period. When you choose Months, Excel groups dates according to calendar months, so February, March, and April have their normal calendar boundaries. This is different from choosing Days and entering 30 in the grouping interval, which creates repeated 30-day spans rather than named calendar months.

Grouping is useful when a PivotTable has too many daily entries to scan, but you still want the original transaction-level records available. You can usually expand a month to see its underlying dates, or change the grouping later without rebuilding the source table.

Group dates by month in Excel for Windows

These steps apply to the desktop version of Excel for Microsoft 365 on Windows. Start with a PivotTable that has a genuine date field in its source data.

Click a cell in the PivotTable. In the PivotTable Fields pane, drag the date field into Rows. You can use Columns instead if you want months across the top of the report.
Right-click one of the date labels inside the PivotTable. Choose Group from the context menu. The command is available when you right-click a date item, not the field name in the PivotTable Fields pane.
In the Grouping dialog box, select Months. If the source includes multiple calendar years, also select Years.
Click OK. Excel adds grouped date labels to the PivotTable. If needed, use the field list to arrange the year and month fields in the order you want.
Put the measure you want to summarize, such as Revenue or Units, in Values. Check that Excel uses the intended calculation, such as Sum or Count.

You can also select a date label and use the PivotTable Analyze tab’s Group Selection command when it is available. Right-clicking a date is often the quickest route. If Group is missing or disabled, see the troubleshooting section below before assuming the workbook is damaged.

Why selecting Years matters

Suppose a source table contains sales from January 2024 through December 2025. If you group only by Months, Excel can place both January periods in the same January category, both February periods in the same February category, and so on. The result may be useful for comparing typical performance by month of the year, but it does not show a separate January total for each year.

For a chronological monthly report, select both Years and Months in the Grouping dialog. The PivotTable can then show a year heading with its months underneath, preserving the distinction between January 2024 and January 2025. You can also move Years and Months into separate row or column positions to create a layout that suits the report. To compare the same month across years, keep a year field visible rather than relying on month names alone.

Months only: combines dates with the same month name across years. Choose this for a month-of-year comparison when that combined result is intentional.
Years and Months: keeps each calendar year separate and is generally the safer choice for monthly totals over a multi-year period.
Years, Quarters, and Months: adds more levels for reports where readers need to move between annual, quarterly, and monthly totals.

Arrange and format the monthly report

After grouping, check the field order and the PivotTable layout. A common arrangement places Years and then Months in Rows, with a measure such as Sum of Sales in Values. Another arrangement puts Years in Rows and Months in Columns, making it easier to compare the same months side by side. The right design depends on whether the report is meant to show a timeline or a cross-year comparison.

If Excel summarizes a numeric measure as Count instead of Sum, open the value field’s settings and select the intended summary. Count can be correct when you want the number of records, but it will not calculate monthly revenue totals. Also confirm that the source measure contains numbers rather than numbers stored as text; text entries may not contribute to a sum as expected.

Grouped month labels are categories, not a promise that every month has a transaction. A month with no source records may be absent. If you need to display empty months, review the PivotTable field settings for showing items with no data and consider whether the date range and source structure support the report you need. Formatting the labels can improve readability, but changing a displayed number format is not the same as changing the underlying grouping.

Common reasons Group is unavailable

Excel needs to recognize the field as dates. A cell can look like a date while actually containing text, especially after importing a CSV or combining data from different systems. Applying a date number format does not convert text into a real Excel date. Check representative source cells, sort the source column, and look for values that behave differently from the rest.

Other common blockers include blank cells, errors, or inconsistent values in the date field. A mixed column with true dates, text such as “Pending,” and empty entries may prevent grouping. Inspect the entire source field, correct or consistently handle invalid entries, then refresh the PivotTable and try again. Do not replace missing dates with an arbitrary date unless that treatment is valid for the data.

Confirm that the date field is in the PivotTable’s Rows or Columns area.
Check the source date column for blanks, errors, and text mixed in with actual dates.
Make sure you right-click a date item in the PivotTable rather than a value or field-list heading.
After correcting source data, refresh the PivotTable and repeat the grouping step.

If the PivotTable uses an external connection or the Data Model, grouping options can differ from those for a regular range-based PivotTable. In that case, the source model or its date table may control the available date categories. If grouping remains unavailable after checking the source, identify how the PivotTable was created before changing its structure.

Keep the report current when data changes

Grouping is a PivotTable feature, so edits to the source do not necessarily appear in the report immediately. Refresh the PivotTable after updating its source. Right-click inside it and choose Refresh, or use Data > Refresh All to update workbook connections and PivotTables. If new records fall outside a fixed source range, refreshing alone will not include them; expand the source or use an Excel Table as the PivotTable source so added rows are easier to incorporate.

For a table-based source, add new records as rows in the table, then refresh the PivotTable. Check the grouped result after refresh, particularly when new dates extend the report into a new year. A PivotTable can retain cached items or field selections, so review the visible years and months rather than assuming the layout has changed exactly as intended.

Grouping does not make source dates monthly dates. The original daily values remain in the source, and another report can still analyze them by day or by a different time period. This distinction matters if you share the workbook: readers who inspect the source table will see the transaction dates, while readers of the PivotTable see the grouped summary.

Mac and Excel for the web

In Excel for Mac, the overall approach is similar: put the date field in the PivotTable’s Rows or Columns area, select a date item, and look for Group or Group Selection in the context menu or PivotTable ribbon. Ribbon labels and their placement can vary between Mac releases, so use the right-click route if the ribbon command is not obvious. The grouping dialog provides the period choices when grouping is supported.

Excel for the web has a different, changing feature set and interface from desktop Excel. If the web version does not show a Group command for the selected date item, open the workbook in the desktop app and group the field there. Once saved, check the web PivotTable to confirm it displays the grouping as expected. For workbooks that must be edited in a particular environment, verify the available commands in that version before building a process around them.

When a helper date field is a better choice

PivotTable grouping works well for ordinary calendar months. It may not match a business calendar that uses a fiscal year, a 4-4-5 retail calendar, custom accounting periods, or labels such as “FY2025 P03.” For these cases, add clearly defined fields such as Fiscal Year and Fiscal Period to the source data, then use those fields in the PivotTable. This makes the business rules visible and avoids treating a calendar month as though it were a custom reporting period.

A helper field can also be useful when you need stable, reusable labels across multiple reports or when your PivotTable source is a Data Model with a proper calendar table. Keep the original date field as well: it supports detailed analysis and lets users trace a period back to individual records. Document how custom periods are assigned, especially if the reporting calendar does not follow January through December.

Frequently Asked Questions

Can I group a PivotTable by month without grouping by year?
Yes. Select Months without Years in the Grouping dialog. Excel will combine the same month name across different years, so use this only when a combined month-of-year total is what you intend.

Why does Excel say it cannot group that selection?
The source date field may contain text, blanks, errors, or inconsistent values, or the selected field may not be a date field in Rows or Columns. Check and correct the source, refresh the PivotTable, then try grouping a date label again.

Can I group dates by month and quarter at the same time?
Yes. In the Grouping dialog, select the period levels you need, such as Years, Quarters, and Months. Excel creates a hierarchy that you can arrange in the PivotTable to show the detail or summary levels required.

Does grouping change the dates in my source data?
No. It changes the PivotTable’s grouping and summary view, not the original date values. You can still use the source records for daily analysis or create another PivotTable with a different grouping.

Can I group dates into fiscal months?
Not reliably through the standard calendar-month grouping when your fiscal periods differ from calendar months. Add fiscal year and period fields to the source, using the organization’s actual calendar rules, and summarize by those fields instead.

Why do I see only one January for several years?
You probably grouped by Months without Years. Edit the grouping and add Years, or place a separate year field in the PivotTable so each January remains associated with its own calendar year.

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.