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!

 

A video at the end of this tutorial covers the grouping of date, numeric and text fields – a great overview of this topic. We recommend in house Excel Training with Blue Pecan Computer Training.

To get going with the matter at hand, here is a snapshot of our data.

26-07-2016 11-33-05

We start with a PivotTable report showing total sales for each product, as shown below.

26-07-2016 13-01-55




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.

26-07-2016 11-41-36

The group is created and named with a default name, such as Group 1.

26-07-2016 13-12-25

Type over this group label with something more descriptive.

26-07-2016 13-14-24

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.

26-07-2016 13-15-39

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.

26-07-2016 13-18-45

Here’s our report showing subtotals for brand sales.

26-07-2016 13-22-14

Please watch our Mousebytes video on grouping data in PivotTables. As well as showing you how to group text fields, it also demonstrates how to group numeric and date fields.  Happy viewing!



Leave a Reply

Your email address will not be published. Required fields are marked *

8 − 1 =