Group Dates in an Excel Pivot Table

To group daily dates into months, quarters, or years in a pivot table, right-click any date in the Rows or Columns area and choose Group, then pick the periods you want. If Group is greyed out or throws an error, the dates are almost always stored as text rather than real Excel dates.

How do I group dates by month, quarter, or year?

Right-click any date value already placed in the pivot's Rows or Columns area, choose Group, and select one or more periods — selecting both Months and Years, for example, nests months inside years instead of collapsing 3 years of January data into one "January" bucket.

1. Build the pivot with a Date field in Rows
2. Right-click any date cell > Group
3. In the dialog, select: Months, Quarters, Years (Ctrl+Click for more than one)
4. Click OK

Since Excel 2016, dropping a date field into Rows or Columns often auto-groups it into Years > Quarters > Months immediately, without you asking for it — this is a real feature, not a bug, but it surprises people expecting to see raw daily rows. Turn it off globally if you don't want it: File > Options > Data > check "Disable automatic grouping of Date/Time columns in PivotTables".

How do I group by a custom range instead of standard periods?

The same Group dialog lets you set a specific Starting at / Ending at date and a "Days" interval, which is how you build weekly buckets (interval = 7) or a custom fiscal window instead of a standard calendar month or quarter.

Right-click a date > Group
By: Days
Number of days: 7
Starting at: (your first Monday, so weeks align to Mon-Sun)

Note that when "Days" is selected, Months/Quarters/Years become unavailable — you can group by days-interval or by calendar periods, not a mix of both in the same grouping.

How do I ungroup dates in a pivot table?

Right-click any grouped date label and choose Ungroup — this reverts to the original daily granularity and removes the automatically-added Years/Quarters/Months fields from the field list.

Right-click a grouped date (e.g. "Jan" or "Qtr1") > Ungroup

Why is Group greyed out, or why does grouping throw an error?

Group requires the field to contain actual Excel date serial numbers, not text that merely looks like a date — a value imported from a CSV, a database export, or typed with an inconsistent format often lands in the sheet as text, and a pivot table has no way to bucket text into months.

Symptom: Group is disabled, or grouping throws
"Cannot group that selection"

Diagnose: select the date column, check the cell's horizontal alignment
- Left-aligned by default → likely text
- Right-aligned by default → likely a real date/number

Fix options:
=DATEVALUE(A2)                          ' formula, converts text to a date serial
Data tab > Text to Columns > Finish     ' quick re-parse, keep default date format
Power Query > right-click column > Change Type > Date

After fixing the source column's data type, rebuild or refresh the pivot table (Change Data Source if needed) — a pivot built while the field was still text will keep the field's original text type in its cache until you refresh.

Does grouping stay applied when I refresh the pivot?

Yes — grouping is stored on the pivot table itself, not recalculated from scratch on every refresh, so it survives a normal Refresh as new rows are added within the same date range. It does not automatically extend to cover a new grouping boundary (e.g. a newly added future year) if your Starting at/Ending at values were set manually rather than left on Auto.

Common mistakes when grouping pivot dates

Most date-grouping problems come from an incomplete period selection or from the source dates not being real dates in the first place.

• Selecting only Months when data spans multiple years: January 2024 and January 2025 collapse into one "January" row unless Years is also selected.
• Mixed real dates and text dates in the same column: Group may succeed but silently exclude or misfile the text rows — clean the whole column's type first.
• Grouping, then manually reordering rows: a subsequent refresh can reset custom row order; sort by the date field instead of dragging rows.
• Expecting Ungroup to remove auto-inserted Years/Quarters fields from the field list entirely: sometimes it leaves them listed as available fields even after ungrouping; a pivot rebuild clears this fully.