Exceljet

Quick, clean, and to the point

How to group a pivot table by age range

Grouping data with pivot tables

One of the most powerful features of pivot tables is their ability to group data. 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.

Pivot table example: grouping employees by department

Voting results source data

Many people stop with this basic automatic grouping, but pivot tables can also group data in more sophisticated ways. For example, you can also use a pivot table to group data by numbers – summarizing data by age range, price range, or any numerical range that makes sense for your data. To illustrate, let’s assume you have a list of voting results that includes voter age, and you want to summarize the results by age group. Your data might look something like this:

Pivot table source data: voting results with age

The basic pivot table

To get started grouping the data by age, first create your pivot table normally. Next, add the field Age as a Row Label, and the field Vote as a Column Label. Finally, add Name as a Value. In the example below, we also renamed Grand Total to Total. At this point, our pivot table looks like this:

Pivot table example: grouping voting data ungrouped

Grouping by age

Although this pivot table is interesting, it’s not very useful, since the automatic grouping by actual age is too granular. We don't care that five 20-year-olds voted for Option B – we want to see voting results by age ranges, like 20-29, 30-39, etc. This is easily done using the grouping feature built in to pivot tables. To group results by age, right-click any value in the Age field and choose Group from the menu.

Pivot table example: grouping by age

Specifying the interval

When the Grouping dialog box appears, enter any interval that makes sense in the “By:” input area.  For this example, we’ll group by 10 years.

Pivot table example: grouping by age, specifying the interval

The final result

When you click OK, you’ll see your data neatly grouped by age at 10 year intervals.

Final pivot table: voting data grouped by age

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.

Learning pivot tables

Pivot tables are easy to create but hard to control. Our short video course, Core Pivot, walks you step-by-step through the most important features of Pivot Tables.

Author 
Dave Bruns
Attachments