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.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel: