Learn How to Group PivotTable by Ranges
One of the most interesting and powerful features of PivotTables is their ability to group the data. In the row and column label areas of a PivotTable report, you can group the items in a field in a custom way. Grouping the data can help you to isolate a subset of data that satisfies your specific needs, and that cannot be easily grouped in other ways, such as sorting and filtering.
Any field added as a row or column label is automatically grouped by the values that appear in that field. For example, you might use a pivot table to group a list of employees by department. In this case, with the department field added as a row label, the pivot table neatly breaks out a count of employees by department, with a new row for each department that appears in the source data.
Example
Here is the table full of employees’ detail (Count:40). To get started grouping the data by age, first create a PivotTable. Next, add the Age as Row Labels, Department as Column Labels, and Name as values (Count).
Although this PivotTable looks interesting, it is not very useful. It does not help that there are 3 employees aged 32 in 3 different departments. It would have been great if we can see the results by age ranges such as 30-34, 35-39, 40-44, etc. This can be easily done using the Group feature in PivotTables.
To group the ages, simply right click on any age value in the PivotTable and select Group from the menu.
When the Grouping dialog box appears, enter an appropriate interval for your data. In this example, I would enter 30 for starting point, 50 for ending point, and group by 5 years.
When you hit OK, you will see your data neatly grouped by age at 5 years intervals.
You can use this same approach to group numeric data in many useful ways. You can group customers by total sales, group employees by their time at a company, group weather data by temperature – the list is endless.
