There’s a simple but not so obvious way for Excel to take a list of values and show them grouped into segments or groups.
As an example, here’s the results of a class exam with their scores grouped into blocks of 20.
It’s done with a tricky use of PivotTables and one of the somewhat buried options.
We start with a table or range that has some values, like exam scores or ages. Go to Insert | PivotTable | from Table/Range.
Specify the Table/Range and where you want the PivotTable to go. We’ve chosen the Existing Worksheet and clicked a cell next to the source table.
A blank PivotTable will appear in the sheet as well as the PivotTable pane.
Drag the Score (or other value) field to the Rows box.
Drag the Surname (or other label) field to the Values box. It should automatically become a ‘Count of… ‘
At the point the PivotTable looks very wrong, but all will become clear in a moment.
Notice that Excel has automatically sorted the rows in ascending value. That’s just what we want. If you want a descending order, change the sort from the pull-down menu on the PivotTable header for a column.
Group a PivotTable row
The magic happens by clicking in any cell in values column (the left one in this example) and choosing Group.
Excel will offer to make groups with start and end values as the minimum and maximum set and a ‘By’ or grouping range automatically set.
Feel free to change the auto settings, for example to a standard range like 0 to 100 or 32° to 212°. The groupings or ‘By’ setting can also change, we switched to ‘20’ to make fewer groups.
The bottom ‘Whole Class’ line is useful as an extra check that the entire class or list is included in the PivotTable.
Pivotby – Excel’s new faster PivotTables
How to count unique items in an Excel PivotTable
Get smarter PivotTable recommendations in Excel
