See all How-To Articles

How to Group Pivot Tables by Date in Excel

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

 

pivotdate intro

 

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. When you create a pivot table, you can choose to add Region to the filter area, Order Date to the row area, and Order Amount to the value area of the pivot table.

 

pivotdate row date

 

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

 

pivotdate expand

 

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

 

pivotdate added fields

 

The date field itself has therefore been grouped by year, quarter, and month. You can remove any of these fields, but if you remove the year, for example, then the data will not be divided into the individual years, e.g., 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, then only months are shown – so all the orders in February, regardless of the year, are shown in February.

 

pivotdate month only

 

This is due to the fact that by default, the pivot table does not group by individual dates.

  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

 

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

 

pivotdate group

 

  1. If you want to group by week, you can select Days in Grouping and change the Number of days to 7.   In order for the Number of days option to be available, you will need to deselect Months, Quarter and Years – you can do this by clicking on them each individually.

pivotdate add week

 

  1. The date field will then be added to the Pivot table in weekly groups.

pivotdate week groups

Turn off date grouping in Excel Pivot Tables

If you do not want Excel to automatically group by Year, Quarters and Months in the Pivot table, you can switch this option off.

  1. In the Ribbon, select File > Options > Data.
  2. Make sure Disable automatic grouping of Date/Time columns in PivotTables is checked, and then click OK.

pivotdate data options

Manual Pivot Table Grouping

Consider the following pivot table:

 

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, for example Boston and New York. Remember to old down the SHIFT key to select multiple values. Once you have grouped your values together, you can rename the groups.

 

pivotdate manual grouping

Cannot Group on That Selection

You may find that when you are trying to group in a Pivot table, you get an error informing you that you cannot group on that selection.

If you have grouping already in place, you may find that you are not able to add new grouping to the Pivot Table until you have removed the existing grouping.

pivotdate cannot group

If you added the Pivot table to the Data Model when you created the Pivot table, you will not able able to add any new levels of grouping to your data.

pivotdate data model

For example, if you have added a date field to your data, and then try to add a group for a week of data, you will not be allowed to do so. The Number of days option is disabled.

pivotdate days disabled

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

See all How-To Articles