Group Data in PivotTable

In a PivotTable, you can group different types of values. I’ve written an article about grouping dates in PivotTables, so this one will be about grouping numbers and text.

In this lesson, we are going to use the following data.

Group Numbers

  1. First, you need to insert a PivotTable. Click any cell inside the table.
  2. Navigate to Insert >> Tables >> PivotTable.
  3. Inside PivotTable Fields select Date and Units. Price should be in ROWS and Units in VALUES.
  1. Right-click any cell in Row Labels and click Group.
  2. Inside the Group window, uncheck Starting at and Ending at, and set By to 3. This will group values by 3, starting from 0.

Now, we can see how many units we sold in these price ranges.

Group Text

In addition to grouping numbers, we can also create custom groups.

Create a PivotTable and check Items and Price. Items should be in ROWS and Price in VALUES.

It’s going to create the following PivotTable.

We are going to create a custom group for Pens and Pencils. Select these positions, right-click and choose Group.

Now, Pencils and Pens are inside one group called Group1, and Clipboards and Notebooks have their own individual groups that were created automatically.

Group1 is not the best name for the group. You can change it by clicking the group and changing the name inside Formula Bar.

Now you can select the other elements and group them the same way.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.