Dates and grouping Updated November 2026

Group dates by month, quarter and year

Put the date field in Rows, right-click any date, choose Group, select Months (and Quarters and Years if you want nested levels) and click OK. Excel 2016 and later group dates automatically when you add them; right-click, Ungroup returns the raw dates.

Syntax

Right-click a date in the pivot > Group > select Months, Quarters, Years > OK

Part	What it is
`Starting at / Ending at`	The range to group; auto-filled from the data
`By`	Seconds, Minutes, Hours, Days, Months, Quarters, Years; pick one or several
`Number of days`	Only with Days selected alone; 7 gives weekly buckets

Examples

Rows: Order Date (grouped by Months). Values: Sum of Revenue

Result: Jan, Feb, Mar ... with revenue per month.

If the data spans more than one year, add Years too or January of both years merge.

Rows: Date (grouped by Quarters). Values: Sum of Amount

Result: Qtr1, Qtr2, Qtr3, Qtr4 with spending per quarter.

Rows: Department. Columns: Date (grouped by Years). Values: Sum of Revenue

Result: A department by year grid, ready for a year-over-year comparison.

Group > Days only > Number of days: 7

Result: Weekly buckets labeled with their start and end dates.

Common mistakes

Cannot group that selection: some dates are text or blank, so convert them to real dates and refresh (see the dates not grouping page).
Two years of January add together: group by Years and Months together, not Months alone.
Grouping one pivot changed another: pivots sharing a cache share grouping; create the second pivot from the source again to separate them.
Weeks need Days with 7, and Months must be unticked, otherwise the option is disabled.

Practice Group dates by month, quarter and year

These drills check your answer on live data, free, no account needed.

Revenue by monthShow total Revenue for each month of 2025.
Spending by quarterShow total Amount for each quarter.
Ticket volume per monthCount tickets opened per month, with Channel across the top.

Questions

How do I group dates by month in a pivot table?

Right-click a date in the row area, choose Group, tick Months, click OK. Add Years when the data covers more than twelve months.

How do I turn off automatic date grouping?

File, Options, Data, tick Disable automatic grouping of Date/Time columns in PivotTables. Or right-click and Ungroup after the fact.

Can I group by week?

Yes. In the Group dialog select only Days and set Number of days to 7. Set Starting at to a Monday for Monday-based weeks.

Dates not groupingCannot group that selection means the date column has text, blanks or mixed values. Find the bad cells, convert text dates to real dates, refresh and group.
TimelineInsert a timeline to filter a pivot table by year, quarter, month or day with a drag slider. Where the button is, connecting several pivots, and real dates.
Difference from previousCompare periods inside a pivot table with Show Values As, Difference From and % Difference From. Pick the base field and (previous); why period one is blank.
Group numbers into bandsGroup a numeric field into ranges such as 0 to 99 and 100 to 199 with the pivot table Group dialog. Set the start, end and step, and relabel the bands.