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.
Imagine you have created the pivot table on the leftshown above, 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:
This will group the figures by month, as shown in the pivot table below.
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:
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:
To ungroup the values in a pivot table, simply:
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.