Pivot Table Tutorial Part 3 - Group A Pivot Table in Excel

When analysing data in an Excel Pivot Table, it is often useful to group the Pivot Table data into categories.

For example, instead of displaying total sales values for each day, you might prefer to group the days into months and display the total sales values for each month.

Excel can automatically group numeric values (including dates & times) in pivot tables. This is shown in the following examples.

Example 1: Group a Pivot Table by Date

Pivot table showing sum of sales figures per day

Imagine you have created the pivot table on the leftshown above, which shows sales figures for each date of the first quarter of 2016.

Pivot table options for grouping dates and times

If you want to group the sales figures by month, you can do this as follows:

Right click on the left column of the pivot table (containing the dates) and select the option Group...;
You will be presented with the 'Grouping' dialog box for dates;
Select the value Months and click OK.

This will group the figures by month, as shown in the pivot table below.

Example 2: Group a Pivot Table by Range

Imagine you have created the pivot table on the leftshown above, which provides a count of the number of children of each age from 5 to 16 years.

If you want to group the ages into the age ranges 5-8 years, 9-12 years and 13-16 years, you can this as follows:

Pivot table options for grouping numeric values

Right click on left column of the pivot table (containing the ages) and select the option Group...;
You will be presented with the 'Grouping' dialog box for numbers;
Excel will automatically enter the minimum and maximum values from the data, (which in this case are of 5 and 16);
We want to group the ages into 4-year periods, so we change the value in the lower box (labelled By:) to a 4;
Click OK.

This will group the ages into categories, beginning with age 5-8 and increasing by 4 years each time. The resulting pivot table is shown below:

Ungrouping a Pivot Table

To ungroup the values in a pivot table, simply:

Right click on left column of the pivot table (containing the grouped values);
Select the option Ungroup...

Common Pivot Table Grouping Error

Pivot Table Grouping Error: Cannot Group That Selection

Pivot table error message - cannot group that selection

If you attempt to group a pivot table, but find that the Group... option is greyed out, or that a message box pops up, saying "Cannot Group That Selection", this is usually because the data column in the original data sheet contains one or more non-numeric values (or non-date values), or errors.

In order to fix this, you will need to return to your original data sheet and amend the non-numeric or non-date values.

Once you have done this, right click on the pivot table and select Refresh. This will update the values in the pivot table, and you should now be able to group the selected row or column values.

Continue to Part 4 of the Excel Pivot Table Tutorial >>

Go To Excel Pivot Table Tutorial Part 4 - Advanced Pivot Table in Excel

Return to the Excel Pivot Table Tutorial Page