This Mousebyte explains how to group numeric data within an Excel PivotTable.  In our example we have a transaction database listing product sales which includes product price.  We want to analyse the number of sales in different price ranges, for example £0 -£5, £6-£10, £11-15. This tutorial assumes you know how to create PivotTables and add fields to PivotTables.

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.

Let’s get started. Take a look at our database and notice there are two price fields: this is because I want to show you how grouping differs with integer values vs decimal values.

26-07-2016 11-33-05

Grouping Decimal Numbers

To start off with the Price 1 field which includes decimals has been added to the Rows area of the PivotTable and a Count of Revenue has been added to the Values area as shown below.  This report basically shows how many times a product was bought at a particular price.  This is where grouping the data into £5 price ranges would make the report more useful.

26-07-2016 11-37-17

To group the prices, right-click into one of the price row labels and select Group from the menu.

26-07-2016 11-41-36

In the Grouping dialog enter a Starting at and Ending at value (the lower and upper values you want group within) and then a By value (your grouping interval).  Click on OK to confirm.

26-07-2016 11-42-38

The grouping is applied to your prices. Now we have a more meaningful report that counts transactions within each price range.

26-07-2016 11-46-07

One thing to notice about the report is that the price ranges overlap.  For example, the first price range is 10-15 but 15 is also included in the second price range, 15-20.  This is confusing – would a product priced at exactly £15 be included in the first or second price range?  Well the answer is that it would be included in the second. The ranges, properly expressed are 10-14.99. 15-19.99 and so on.

For clarity, you may wish to overtype the row labels as shown below.

26-07-2016 11-52-01


Grouping Integer Values

Grouping integer values is not as problematic as grouping decimal values. If we swap Price 1 with Price 2 in the PivotTable and group in the same way, with exactly the same grouping settings, we get the price ranges correctly expressed as shown below.

26-07-2016 12-28-51

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






Leave a Reply

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

5 × 3 =