Contents
Often there is a need to group in a pivot table by row or column headings. For numerical values, Excel can do this automatically (including for dates and times). This is shown below with examples.
Example 1: Grouping in a pivot table by date
Suppose we have created a PivotTable (as in the image below) that shows the sales data for each day of the first quarter of 2016.
If you want to group sales data by month, you can do it like this:
- Right-click on the left column of the pivot table (column with dates) and select the command Group (Group). A dialog box will appear Grouping (Grouping) for dates.
- Выберите Months (Month) and press OK. The table data will be grouped by month as shown in the pivot table below.
Example 2: Grouping a PivotTable by Range
Suppose we have created a PivotTable (as in the image below) that groups a list of 150 children by age. Groups are divided by age from 5 to 16 years.
If you want to go even further and combine the age groups into categories 5-8 years old, 9-12 years old and 13-16 years old, then you can do this:
- Right-click on the left column of the pivot table (column with ages) and select the command Group (Group). A dialog box will appear Grouping (Grouping) for numbers. Excel will automatically fill in the fields Since (Starting At) и On (Ending At) with the minimum and maximum values from our initial data (in our example, these are 5 and 16).
- We want to combine the age groups into categories of 4 years, therefore, in the field With a step (By) enter the value 4. Click OK.Thus, the age groups will be grouped into categories starting from 5-8 years old and then in increments of 4 years. The result is a table like this:
How to ungroup a pivot table
To ungroup values in a pivot table:
- Right-click on the left column of the pivot table (the column containing grouped values);
- In the menu that appears, click Ungroup (Ungroup).
Common Mistakes When Grouping in a PivotTable
Error when grouping in a pivot table: Selected objects cannot be combined into a group (Cannot group that selection).
Sometimes when you try to group in a pivot table, it turns out that the command Group (Group) in the menu is not active, or an error message box appears Selected objects cannot be combined into a group (Cannot group that selection). This occurs most often because a data column in the source table contains non-numeric values or errors. To fix this, you need to insert numbers or dates instead of non-numeric values.
Then right click on the pivot table and click Update & Save (refresh). The data in the PivotTable will be updated and row or column grouping should now be available.