Excel Pivot Table: Group Dates by Month and Year in Excel

Charts & pivot tables · Updated November 18, 2026

To group an Excel PivotTable date column by month, place the date field in the Rows or Columns area, right-click any date in the PivotTable, choose Group, select Months, and click OK. Also select Years if your source covers more than one year; otherwise, January from different years will be combined into one January total.

Group dates by month: the basic steps

These steps apply to Excel for Microsoft 365 on Windows when the PivotTable uses a normal worksheet range or Excel table as its source:

  1. Make sure the source column contains genuine Excel dates, with a heading such as Order Date.
  2. Select a cell in the source data and choose Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK.
  3. In the PivotTable Fields pane, drag the date field to Rows or Columns. Drag a measure such as Sales to Values if you want monthly totals.
  4. Right-click a date displayed in the PivotTable and choose Group.
  5. In the Grouping dialog box, select Months. Select Years as well when the data spans multiple years, then choose OK.

Excel adds grouped date fields to the PivotTable, so you can arrange the month and year levels in the layout. For a report with years across the top and months beneath each year, keep both fields in the Columns area and place Years before Months. For a vertical report, put both in Rows. The exact labels and order can vary with the existing field layout.

Why include Years with Months?

A month name on its own is not unique. If your source contains dates from 2024 and 2025 and you group only by Months, the PivotTable can total all January records together, all February records together, and so on. That is useful for comparing seasonality across years, but it is usually wrong for a chronological monthly report.

For example, suppose sales were $12,000 in January 2024 and $15,000 in January 2025. Grouping only by month produces a January total of $27,000. Grouping by both year and month keeps the figures separate: January 2024 is $12,000 and January 2025 is $15,000. The values here are illustrative; the important point is that the grouping fields determine whether years remain distinct.

Month-only grouping is appropriate when you intentionally want a cross-year summary, such as average sales by calendar month. For a monthly timeline, trend chart, or year-over-year comparison, use both Years and Months. You can also put Years in a filter or slicer if the report needs to display one year at a time.

How PivotTable date grouping works

Excel stores valid dates as serial values and uses the date parts to create groups. Grouping does not rewrite the dates in the source cells; it changes how the PivotTable summarizes them. The original date field remains available, and Excel creates grouped levels that can be rearranged or removed from the report.

The Grouping dialog can offer different intervals depending on the field and the data. For monthly reporting, choose Months, and usually Years too. You may also see options such as Quarters or Days. Selecting Quarters can be useful for financial or operational summaries, but quarters do not replace years when a report spans multiple calendar years. Choose the levels that answer the reporting question rather than selecting every available interval.

Grouping works on the actual dates, not merely on how the cells look. A cell formatted to display a date may still contain text, and a text value that looks like 1/2/2025 can be interpreted differently depending on regional date settings. Check the source values if Group is unavailable or Excel reports that the selection cannot be grouped.

Choose a layout that answers the question

Where you place the grouped fields affects how easy the result is to read. A useful starting layout for monthly totals is Years and Months in Rows, with the amount being summarized in Values. This produces a compact hierarchy that can be expanded or collapsed. Put the date levels in Columns instead when a small number of months or years should appear across the page.

  • Monthly timeline: Put Years above Months in Rows or Columns to preserve the sequence across calendar years.
  • Seasonality comparison: Put Months in Rows and Years in Columns so January, for example, can be compared across separate year columns.
  • One-year report: Keep Years as a filter and Months in Rows, or filter the Years field to the period you need.
  • Quarterly summary: Group by Quarters and Years when you need quarter totals, while checking that the year level remains visible.

For seasonality comparisons, month names may appear in calendar order in a grouped PivotTable. If you instead create month names as ordinary text in the source, they may sort alphabetically unless you apply a suitable custom sort or use a numeric month field. That distinction is one reason native date grouping is convenient for a quick report.

Common grouping errors and how to fix them

If Group is greyed out, or Excel says it cannot group the selection, inspect the entire source date column rather than only the visible PivotTable entries. A single blank, error, or text value among the dates can prevent grouping. Filter the source column, look for empty or inconsistent entries, and correct them before refreshing the PivotTable.

  • Text that resembles a date: Convert the source values into real dates. Data > Text to Columns can help when the values are consistently formatted; choose the correct date order in the wizard so day and month are not reversed.
  • Blank cells: Fill or remove missing dates if appropriate for the analysis. Do not substitute an arbitrary date unless that is a deliberate, documented treatment of missing data.
  • Errors or inconsistent values: Correct invalid entries in the source, then refresh the PivotTable. Check for values imported with extra spaces or mixed formats.
  • Different date conventions: A value such as 03/04/2025 can mean different dates under different regional settings. Confirm the intended day-month order before conversion.

After correcting the source, click inside the PivotTable and choose PivotTable Analyze > Refresh, or right-click the PivotTable and select Refresh. If the source data is an Excel table, new rows are generally included when the PivotTable refreshes. If the source is a fixed cell range, confirm that the range includes newly added records; you can change it through PivotTable Analyze > Change Data Source.

Data Model and other source limitations

The right-click Group command is not available for every PivotTable type. In particular, PivotTables based on OLAP sources, including many PivotTables built from the Excel Data Model, have restrictions on grouping items directly. If you use the Data Model and cannot group the date field, the usual solution is to add a calendar or date table to the model, with columns such as Date, Year, Month Number, and Month Name, then relate that table to the transaction dates.

A calendar table is also a better choice when a report needs consistent month labels, fiscal periods, custom business calendars, or dates with no transactions. Use the table’s Year and Month fields in the PivotTable rather than relying on ad hoc grouping. Month Number can help keep month names in calendar order, while separate fiscal year and fiscal quarter columns can represent a non-January year start.

Another less common obstacle is automatic date grouping. Some desktop Excel versions automatically add date hierarchy levels to PivotTables. If Excel has already created a hierarchy, expand the relevant levels or use the fields it provides; you may not need to group the date again. In Microsoft 365 for Windows, automatic grouping can be controlled in File > Options > Data with the setting for automatic grouping of Date/Time columns in PivotTables. The wording or availability can differ by build.

Ungroup, refresh, and keep the report current

To reverse a grouping, right-click a grouped date or month in the PivotTable and choose Ungroup. This returns the field to individual dates. You can then group it again with a different combination of intervals. Ungrouping changes the PivotTable layout; it does not alter the original date values in the source data.

When source records change, refresh the PivotTable so the summary reflects them. Grouping does not make a PivotTable update itself every time a source cell changes. If new dates extend beyond the original source range, update the source range or use an Excel table as the source, and then refresh. If a newly added date falls outside previously selected grouping boundaries, review the grouping settings as well.

For a stable monthly report, keep the source date column clean, use an Excel table where practical, and include the year level whenever the report covers multiple years. These habits make it easier to refresh the report without accidentally combining periods or excluding new transactions.

Desktop Excel, Mac, and Excel for the web

In Excel for Microsoft 365 on Windows, the right-click date > Group route is the most direct method. The ribbon may also expose grouping controls when a PivotTable is selected, but the labels and placement can vary slightly with the ribbon layout and update channel.

Excel for Mac has a similar workflow: add the date field to the PivotTable, open its context menu, and choose Group when the source and PivotTable type support grouping. A Control-click or a two-finger click can open the context menu depending on the Mac setup. Excel for the web has a more limited or changing set of PivotTable editing features, so if Group is missing, open the workbook in the desktop Excel app and use the steps above. The source-data requirements are the same.

Frequently Asked Questions

Why are all January dates combined in my PivotTable?
Months-only grouping combines the same calendar month across different years. Select both Months and Years in the Grouping dialog to keep January 2024 separate from January 2025.

Can I group a PivotTable date field by month and year?
Yes. Right-click a date, choose Group, and select Months and Years. If Group is unavailable, check for text, blanks, or errors in the source, or confirm whether the PivotTable uses the Data Model or an OLAP source.

Why does Excel say it cannot group that selection?
The date column may contain at least one blank, text value, error, or invalid date. Check the full source column, correct inconsistent entries, and refresh the PivotTable before trying again.

Can I group dates by month without changing the source data?
Yes. Normal PivotTable grouping changes the summary layout, not the original source dates. A Data Model PivotTable may need date or month fields in a calendar table instead.

How do I show month names in calendar order?
Use Excel’s native date grouping, or use a numeric month field alongside month names in a designed calendar table. Plain text month names can sort alphabetically rather than January through December.

How do I undo month grouping?
Right-click a grouped item in the PivotTable and choose Ungroup. The PivotTable returns to individual date entries, while the source dates remain unchanged.

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.