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!
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.
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.
To group the prices, right-click into one of the price row labels and select Group from the menu.
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.
The grouping is applied to your prices. Now we have a more meaningful report that counts transactions within each price range.
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.
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.