Group Dates by Month or Year in a Pivot Table

To group dates in a pivot table, right-click a date in the pivot, choose Group, and pick a unit such as Months and Years. Instead of one row per day, you get one total per month, quarter or year, which is what most reports need. This guide covers each grouping option, how to keep years apart, weekly groups, and what to do when Excel refuses to group.

Why group dates in a pivot table?#

A pivot table normally lists every date as its own row. With a year of daily orders, that is up to 365 rows, which is too detailed to read. Grouping rolls the dates up into bigger periods, so the pivot answers questions like "what were sales each month?" in one view.

The examples use this order list in A1:C9:

If you are new to pivots, build one first with the beginner's guide to pivot tables.

How to group dates by month and year#

Put the date field in Rows and Sales in Values, then follow these steps:

1. Right-click any date in the pivot table's row labels.
2. Choose Group. (You can also use PivotTable Analyze > Group Field.)
3. In the Grouping box, click Months and Years so both are highlighted. Click a selected item again to deselect it.
4. Leave Starting at and Ending at as they are, unless you want to cut off early or late dates.
5. Click OK.

The pivot now looks like this:

Excel adds a new Years field to the field list, and the original date field now shows months. You can drag Years to Columns to put the years side by side, which makes month-by-month comparisons easy.

Why you should select Years with Months#

If you choose Months alone, Excel groups by month name only. January 2023 and January 2024 land in the same "Jan" row, and the total mixes both years. That's a common mistake in reports and in interview tasks.

Select Years too whenever the data covers more than one year. With the table above, Months alone would still be correct today, but one more December order from 2024 would silently join the 2023 December row.

The same rule applies to Quarters: choose Quarters and Years together to get Qtr1 2024 rather than every first quarter added up.

How to group dates by week or a number of days#

Weeks aren't in the list, but you can make them from days:

1. Right-click a date and choose Group.
2. Deselect everything except Days.
3. Set Number of days to 7.
4. Change Starting at to a Monday (or Sunday) on or before your first date, so each group is a full calendar week.
5. Click OK.

Each row now shows a date range in your date format, such as 4 December to 10 December 2023 for the first week. You can't combine a 7-day group with Months or Years; the Number of days box only works when Days is the only unit selected.

Automatic date grouping in newer Excel#

In Excel 2016 and later, including Microsoft 365, dropping a date field into Rows often groups it for you. Excel adds fields such as Years, Quarters and Months and shows the top level with plus buttons to expand.

If you want the plain dates, press Ctrl+Z (Mac: ⌘Z) right after adding the field, or right-click a date and choose Ungroup. In Excel for Windows, you can switch the feature off under File > Options > Data with the option to disable automatic grouping of date and time columns in PivotTables.

One more thing to know: pivots built from the same source share their grouping. If you group dates in one pivot, a second pivot from the same data shows the same groups.

Why does Excel say "Cannot group that selection"?#

This message means at least one item in the date field isn't a real date. Excel can only group a field when every value is a date or a number. Check the source data for:

• Blank cells. A single empty date cell is enough. Fill it in, or remove the row.
• Text dates. Dates typed as text, often from an import, sit on the left of the cell. Convert them with Data > Text to Columns or DATEVALUE.
• Error values such as #VALUE! in the date column.
• A source range with empty rows. If the pivot's range runs past the data, the empty rows count as blanks. Use an Excel table as the source so the range fits exactly.

After fixing the data, refresh the pivot (right-click > Refresh), then try Group again.

Can you group dates without a pivot table?#

Yes. A formula gives the same monthly totals and updates without a refresh. For February 2024, with 1-Feb-2024 in E2 and 1-Mar-2024 in E3:

=SUMIFS(C2:C9,A2:A9,">="&E2,A2:A9,"<"&E3)

The result is 3,000, the same as the pivot. Using "before 1 March" rather than "on or before 29 February" also catches dates that include a time. The comparison of pivot tables vs SUMIFS explains when each is better. After grouping, you can also show each month as a share of the year with percent of total in a pivot table, add a ratio with a calculated field, or filter months by clicking with slicers and timelines.

Practise it#

Grouping by month is one of the first things an interviewer asks you to do with a pivot, so it pays to be fast. Practise pivot tables in a live spreadsheet: nest row fields, add filters and percent of total, and check your answers instantly. If you're preparing for an interview, review the most common pivot table interview questions too.