Abstract
Transcript
The ability to group data is one of the most powerful and useful features in a pivot table. And although pivot tables can automatically group things like dates, times, and numbers, you can also manually group data into your own groups.
Let’s take a look.
This pivot table shows a breakdown of sales and orders by product. Let’s use manual grouping to organize these products into 2 custom groups. When you group items manually, hold down the Control-key and select each item that you want to include in the first group. With these cells selected, click “Group Selection” from the Options tab on the PivotTable Tools Ribbon. Excel will put the selected items into their own group and each remaining item into other groups.
To finish grouping, just repeat the process: select each item you want to group together in the remaining items and click Group Selection again. When you click OK, the selected items will be grouped into the 2nd group, and any extra groups will be discarded.
You can repeat this process to make as many groups as you like.
The pivot table will summarize data using your new groups. Like other fields, subtotals for manual groups can be enabled or disabled.
When a manual group is created, you will see a new field name appear in the Field List pane, based on the original field name used for grouping. You can change this at any time to a name that makes more sense to you. For example, let’s change Product2 to Group. Notice that the field name changes in the field list pane as well.
In a similar way, the actual group names can also be renamed at any time. Just select a group title, and type a new name. Let’s rename Group 1 and Group 2 to New Products and Existing Products.
To remove the manual grouping temporarily, you can drag the grouping field out of the row label area.
Note that it still appears in the field list, and if we add it to the row label area again, the pivot table is re-grouped as before.
To remove grouping and the grouping field entirely, first select each group using the Control key to add to your selection. Then, select ungroup from the Options tab of the PivotTable Tools ribbon.
The grouping field is removed from the pivot table and from the field list.
