In this Mousebyte we are are going to explain how to group text fields within a PivotTable. We want to create a report summarising revenue for each brand of kitchen item sold through our store. This tutorial assumes you know how to create a PivotTable and can add fields to a PivotTable report.
We hope you find this Mousebyte tasty and wholesome!
To get going with the matter at hand, here is a snapshot of our data.
We start with a PivotTable report showing total sales for each product, as shown below.
To group text values you need to select the values that you want to include in a group. In our example this is made easy by the fact that the PivotTable has automatically listed products in ascending order, which means that products of the same brand are listed together. To select consecutive products, select the first one and then SHIFT select the last one: all product in-between are also selected. To select non-consecutive products, select the first one and then CTRL select additional products. Once selected, right-click and click Group in the menu.
The group is created and named with a default name, such as Group 1.
Type over this group label with something more descriptive.
At this point it looks as though the PivotTable has created an individual group for all other products. Don’t be put off by this: the process is the same for creating additional groups – select-group-rename the group.
Once you have created all the groups you require you might want to add subtotals. In our scenario this would be useful as we would have a total sales value for each product brand. Click on the Ribbon’s Design tab and then click the Subtotals button. In the menu, choose whether you want subtotals to appear at the top or bottom of groups.
Here’s our report showing subtotals for brand sales.