The following step-by-step example shows how to group values in a pivot table in Excel by uneven intervals.
Step 1: Enter the Data
First, let’s enter the following data about 15 different stores:
Step 2: Create Helper Column
Suppose we’d like to create a pivot table to display the sum of sales by the follow intervals of store square footage:
- 100-124 sq. feet
- 125-149 sq. feet
- 150-199 sq. feet
- 200+ sq. feet
Notice that each interval does not have the same length.
In order to create a pivot table where we group by intervals of different length, we’ll first need to create a helper column that classifies each store into one of these intervals.
We can use the following formula to do so:
=IF(B2B2B2
The following screenshot shows how to use this formula in practice:
Step 3: Create Pivot Table
Once we’ve created the helper column, we can proceed to create a pivot table.
To create a pivot table, click the Insert tab along the top ribbon and then click the PivotTable icon:
In the new window that appears, choose A1:D16 as the range and choose to place the pivot table in cell F1 of the existing worksheet:
Once you click OK, a new PivotTable Fields panel will appear on the right side of the screen.
Drag the Sq. Feet Group field to the Rows box and drag the Sales field to the Values box:
The pivot table will automatically be populated with the following values:
Here’s how to interpret the values in the pivot table:
- The sum of the sales for stores with square footage between 100 and 124 is 51.
- The sum of the sales for stores with square footage between 125 and 149 is 82.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Create Tables in Excel
Excel: How to Group Values in Pivot Table by Range
How to Group by Month and Year in Pivot Table in Excel