Table of Contents
You can group values in a Pivot Table by uneven intervals by using the Group feature in the PivotTable Field List. This feature allows you to specify a certain range of values and group them together. This can be helpful for organizing data that may not have consistent intervals, such as specific date ranges or ages. Additionally, you can also manually select values to group together. This allows for more flexibility in how data is grouped and organized in the Pivot Table.
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(B2<125, "100-124", IF(B2<150, "125-149", IF(B2<200, "150-199","200+")))
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:
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.