How to Group Pivot Tables by Date in Excel

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on June 30, 2023

This tutorial demonstrates how to group dates in Excel pivot tables.

pivotdate intro

When you add a date field (from a column of dates in the source data) to a pivot table, Excel groups the dates automatically.

Read on to see how automatic date grouping works, how to change which group fields are included, and some other grouping options. Then, see how to turn off automatic date grouping and group manually. Finally, see a common grouping error and how to fix it.

Tip: Try using some shortcuts when you’re working with pivot tables.

Group Pivot Table by Date

Consider the data table below.

pivotdata table data

This sales data has five individual columns and is made up of 800 rows of orders. For this example Region is added to the filter area, Order Date to the row area, and Order Amount to the value area of the pivot table.

pivotdate row date

Drill Down in Automatic Date Groups

When you add a date to the filter, row, or column areas of a pivot table, Excel groups the data automatically into years and quarters. Each group has an expand (+) icon attached to it. If you click this icon, it changes to a collapse () icon.

pivotdate expand

Dates as Pivot Table Fields

The row area of the pivot fields reflects that, as well as the Order Date, Quarters and Years fields are created and added to the pivot table.

pivotdate added fields

The date field itself is therefore grouped by year, quarter, and month. You can remove any of these fields, but if you remove the year, for example, then the data isn’t divided into individual years, so here, all the orders for February are included in Quarter1 > February, regardless of what year the order is in.

pivotdate quarter month

Similarly, if you remove the Quarters field, the pivot table only shows months – so all the orders in February, regardless of the year, are shown in February.

pivotdate month only

This is because, by default, pivot tables don’t group by individual dates.

Change Grouping Options

  1. To change the way that the pivot table groups, select the row field in the pivot table and then, in the Ribbon, go to Pivot Table Analyze > Group > Group Selection.

pivotdate group selection

  1. You can now add the actual date of the order (Days) to the pivot table as well.

pivotdate add days

  1. This enables you to drill down further and expand by year, then by quarter, then by month to obtain the sales order amount for each individual day.

pivotdate expand month

  1. Similarly, you can remove the months grouping and group just by quarters and years.

pivotdate group quarters

  1. Or, remove both the months and quarters grouping and group just by year. This gives you the order amounts for each year and removes the ability to drill down to the individual quarters, months, or dates.

pivotdate group

Group Pivot Data by Week

If you want to group by week, click Days in Grouping By and change the Number of days to 7. For the number of days spin button to be available, first remove grouping by Months, Quarters, and Years; do this by clicking each on them individually.

pivotdate add week

Your pivot table now displays the date field in weekly groups.

pivotdate week groups

Turn Off Automatic Date Grouping

If you want Excel to keep date formatting, you can switch off automatic date grouping in a pivot table.

  1. In the Ribbon, go to File > Options > Data.
  2. Tick Disable automatic grouping of Date/Time columns in PivotTables, and then click OK.

pivotdate data options

Manual Pivot Table Grouping

Excel allows for grouping data as you wish once the automatic date grouping is turned off. Here’s an example of manual grouping.

pivotdate show pivot

  1. Click in the Ribbon, and then go to PivotTable Analyze.
  2. Hold down the SHIFT key and click the field values to group on.
  3. In the Ribbon, go to PivotTable Analyze > Group > Group Selection.

pivotdate ribbon group

The values you chose are grouped together.

pivotdate group

  1. Click in the group name (e.g., Group 1) and type in a relevant name for your group.

pivotdate group name

  1. You can then choose other values to group on (e.g., Boston and New York). Remember to hold down the SHIFT key to select multiple values. Once you have your values grouped together, you can rename those groups too.

pivotdate manual grouping

There are no dates in the example above, but the same idea applies when grouping dates manually. You can, for example, group by four-quarter accounting period that starts at the last quarter of the prior year instead of the calendar year; it may be best to group data like that manually. Or, keep year grouping on all years starting in 2020 but group all prior years together.

Cannot Group on That Selection

You may find that, when you are trying to group in a pivot table, you get an error saying that you cannot group that selection. If you have grouping already in place, you might not be able to add new grouping until you remove existing groups.

pivotdate cannot group

If you added the pivot table to the Data Model when you first created it, you can’t add any new levels of grouping to your data.

pivotdate data model

For example, if you have added a date field to your pivot table and then try to group by week, you cannot. The Number of days setting is disabled.

pivotdate days disabled

AI Formula Generator

Try for Free

See all How-To Articles