Group and Ungroup In Pivot Table

Group A Pivot Table in Excel

When analyzing data in Excel, it is often useful to group a pivot table.

For example, instead of displaying total sales values for individual days, you might prefer to group the days and display the total sales values by 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

Imagine you have created the pivot table on the left, which shows sales figures for each date of the first quarter of 2016. If you want to group the sales figures by month, you can do this as follows: Right click on 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 left, which groups 150 children according to age. The ages range 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: 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

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.