How to Group Pivot Tables by Date in Excel

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

 

 

Group Pivot Table by Date

Consider the data table below:

 

 

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.

 

 

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.

 

 

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.

 

 

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.

 

 

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.

 

 

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.

 

 

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

 

 

3. 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.

 

 

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

 

 

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.

 

 

5. 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.

 

6. The date field will then be added to the Pivot table in weekly 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.

Manual Pivot Table Grouping

Consider the following pivot table:

 

 

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.

 

 

The values you chose are grouped together.

 

 

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

 

 

5. 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.

 

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.

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.

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.

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