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.
- First, you need to insert a PivotTable. Click any cell inside the table.
- Navigate to Insert >> Tables >> PivotTable.
- Inside PivotTable Fields select Date and Units. Price should be in ROWS and Units in VALUES.
- Right-click any cell in Row Labels and click Group.
- Inside the Group window, uncheck Starting at and Ending at, 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.
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 the 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.