Excel Pivot Table: Group Dates into Months and Years

Charts & pivot tables · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, right-click any date in the PivotTable, choose Group, select Months, and click OK; if your data covers multiple years, select Years as well so each month stays tied to the correct year.

How to group PivotTable dates by month

Start with a PivotTable that has a date column in its source data. Add that date field to the Rows area, then right-click one of the displayed dates and choose Group. In the Grouping dialog, select Months and click OK. Excel replaces the individual dates in the row labels with month groups, and the field list typically shows a new Months field you can use in the layout.

You can also select a date item and use the contextual PivotTable Analyze tab, then choose Group Field. The right-click method is often the quickest because it starts from the date value you want to group. If the command is unavailable, see the troubleshooting section below before rebuilding the PivotTable.

  1. Click inside the PivotTable and place the date field in Rows.
  2. Right-click a date label and select Group.
  3. In the dialog, select Months. Select Years too if the source includes more than one calendar year.
  4. Click OK, then add a measure such as Sales to Values if you want monthly totals.

For example, a source list containing dates such as January 5, January 18, and February 2 can be summarized into January and February rows. If Sales is in Values, Excel adds the sales amounts for all source records whose dates fall in each month.

Why grouping by month and year matters

A month name on its own does not identify a unique period. If the source contains records from 2024 and 2025 and you group only by Months, Excel combines every January into one January total, every February into one February total, and so on. That can be useful for comparing typical seasonal patterns, but it is usually wrong for a report intended to show each month separately over time.

For a chronological monthly report, select both Months and Years in the Grouping dialog. Excel creates separate year and month levels, so the PivotTable can show January 2024 and January 2025 as distinct groups. In the PivotTable, expand a year to see its months, or collapse it to show a year-level summary. You can also put Years above Months in Rows and put the measure in Values.

Choose the grouping that matches the question the report should answer:

  • Months only: Compare all Januaries, all Februaries, and the other months across the entire source period.
  • Months and Years: Track each monthly period separately, such as March 2024 versus March 2025.
  • Years only: Roll the records up to annual totals without displaying monthly detail.

What Excel is doing when it groups dates

Excel dates are stored as numbers, even though cells usually display them as familiar calendar dates. A PivotTable can use those underlying date values to assign records to calendar groups. Grouping does not change the original dates in the source column; it changes how the PivotTable summarizes and displays them. You can still keep the ungrouped date field in the source data for other reports.

The Grouping dialog may offer intervals such as Days, Months, Quarters, and Years, depending on the selected field and the data. Selecting more than one interval creates a hierarchy. For example, selecting Quarters and Years lets a report show annual totals with quarterly detail. Grouping by Months is a calendar-month operation; it does not automatically create a custom fiscal calendar or shift the start of the year.

If your financial year begins in a month other than January, the built-in month grouping can still summarize calendar months, but fiscal-year labels and fiscal quarters may need a separate fiscal-period column in the source data or a calendar table. That is different from simply grouping dates into calendar months.

Check that the source column contains real dates

The most important prerequisite is that the date column contains consistent, valid Excel date values. A cell that looks like 1/15/2025 may actually contain text, depending on how it was imported or how the computer’s regional date settings interpreted the entry. Text that resembles a date is not necessarily a date Excel can group.

Check a few source cells by changing their number format to General or by inspecting how they behave when sorted. Real dates are numeric values and usually sort in chronological order; text dates can sort alphabetically or remain in unexpected positions. Make sure the column does not mix real dates, text entries such as TBA, blank cells, and error values. A clean, single-purpose date column makes grouping and later filtering more dependable.

If dates came from a CSV or another system, confirm that the day and month were interpreted correctly. For example, 04/05/2025 can mean April 5 or May 4 depending on the source convention and regional settings. Excel cannot infer the intended meaning if the import has already interpreted the value incorrectly. Correct the source values first, then refresh or recreate the PivotTable as needed.

Common reasons the Group command fails

If Group is disabled or Excel displays a message that the selection cannot be grouped, inspect the entire source date column rather than only the visible rows. One blank, text value, or error can prevent grouping for a conventional PivotTable built from a worksheet range. Filters can hide the problematic record, so check the source itself, including rows that are currently excluded from the report.

  • Blanks or inconsistent entries: Fill or remove empty date records as appropriate, and replace non-date placeholders with a valid date or a consistent separate status field.
  • Dates stored as text: Convert the entries to actual dates, confirming the intended day-month order, then refresh the PivotTable.
  • More than one cell selected: Click a single date item in the PivotTable before opening Group.
  • Data Model or OLAP source: Manual grouping may not be available for PivotTables based on the Data Model or an OLAP source. Use a date or calendar table with year and month fields instead.
  • Grouped field already in use: If the field has existing grouping, ungroup it where appropriate and retry with a clean date field.

For a normal worksheet-based PivotTable, right-click a grouped date and choose Ungroup if you need to return to the original date items. Fix the source data, refresh the PivotTable, and try again. For a Data Model report, adding year and month columns to a calendar table is generally a more suitable approach than relying on manual grouping.

Make the monthly report easier to read

After grouping, arrange the fields to match the report’s purpose. A common layout puts Years and then Months in Rows, a category such as Region or Product in Columns or Filters, and a measure such as revenue or units in Values. If the report is crowded, keep the date hierarchy in Rows and use filters or slicers for the other dimensions.

Grouping actual date values normally preserves calendar order. If you instead create month names as text in the source data, those names may sort alphabetically, putting April before February. A custom sort or an accompanying month number can address that issue, but using the original date field for grouping is often simpler. Avoid replacing the source dates with month-name text if the PivotTable also needs to distinguish years or support date filters.

Check the Values calculation as well as the row labels. Excel may summarize a numeric field by Sum, but a field containing text or inconsistent values can default to Count. Right-click a value, choose Summarize Values By, and confirm the calculation is appropriate. A correct-looking month layout can still produce a misleading result if the measure is being counted rather than summed.

Refresh behavior and source-data updates

Grouping is part of the PivotTable’s summary, while the records remain in the source. When you add new rows, refresh the PivotTable using right-click > Refresh or PivotTable Analyze > Refresh. If the new records are outside the PivotTable’s original source range, they may not appear after a refresh. Converting the source range to an Excel Table before creating the PivotTable helps the source expand as rows are added.

Refresh after correcting date values, too. If the PivotTable still shows an old grouping or omits new records, check its source through PivotTable Analyze > Change Data Source. A refresh updates the summary from the source; it does not repair incorrect text dates or automatically include records outside a fixed source range.

Excel desktop, Mac, and web differences

The steps above are written for Excel for Microsoft 365 on Windows. In Excel for Mac, date grouping is available in desktop PivotTables; select or right-click a date and look for Group in the contextual menu. Menu wording and ribbon arrangement can differ slightly by release, but the grouping choices are the same idea: select Months, and include Years when needed.

Excel for the web has fewer PivotTable authoring features than the desktop application, and manual grouping options may not be available in a given web workflow. If you cannot find Group in the browser, open the workbook in the desktop Excel app to create or change the grouping. Once the PivotTable is set up, check that the web version displays the resulting layout as expected.

When a helper column or calendar table is better

Manual PivotTable grouping is convenient for a quick calendar-month summary. A helper column or calendar table is more useful when the report needs custom period labels, fiscal years, week-ending dates, or consistent date attributes shared across several PivotTables. For example, a source table could include a separately maintained Month Start or Fiscal Period field, and the PivotTable could use that field as its row label.

A calendar table is especially relevant when the PivotTable uses the Data Model or when you need relationships between dates and multiple data tables. It can provide fields such as Calendar Year, Month Name, Month Number, Quarter, and Fiscal Year in one place. The trade-off is extra setup and maintenance compared with right-clicking a date and choosing Group. Use the simple grouping when it answers the reporting question; use explicit date fields when the periods need to be controlled and reused.

Frequently Asked Questions

Can I group PivotTable dates by month without grouping by year?

Yes. Select Months alone in the Grouping dialog. Excel combines the same calendar month across all years in the source, so use this only when an all-years seasonal summary is intended.

Why does Excel combine January from different years?

Because Months alone groups records by month number or name, not by a unique year-month period. Group both Months and Years to keep January 2024 separate from January 2025.

Can I group dates into quarters as well as months?

Yes. Right-click a date, choose Group, and select the available intervals you need, such as Quarters and Years. A quarter is a calendar quarter unless you build fields for a different fiscal calendar.

Why is Group greyed out in my PivotTable?

Common causes include text dates, blanks or errors in the source date column, selecting multiple PivotTable cells, or using a Data Model or OLAP source that does not support manual grouping. Check the full source column and the PivotTable’s source type.

Will grouping change the dates in my original data?

No. Grouping changes the PivotTable’s summary and labels, not the original source values. The source dates remain available for other calculations, filters, and reports.

Can Excel group dates into fiscal months automatically?

Not based only on the standard Months option. It groups calendar months. For a fiscal calendar, create or use explicit fiscal-period fields, such as Fiscal Year and Fiscal Month, in the source or a related calendar table.

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.