How to group a pivot table by age range

One of the most powerful features of pivot tables is their ability to group data, especially by number or date. In this video, I'll show you how to group data by age range.

Here we have a set of data that represents voting results. There are 300 results total, and, in each row, we have name, gender, age, and vote. I'll go ahead and summarize the data in a pivot table.

With a pivot table, any field added as a row or column label is automatically grouped by the values that appear in that field. For example, we can easily summarize total voting results by vote by simply adding Vote as a row label.

Or, I can make Vote a column label and then add Gender as a row label.

For both fields, the pivot table breaks down the data using the values that appear in each field.

But what if you want to group by age? Well, if I remove Gender and add Age as a row label, we do get a breakdown by age, but it's a little hard to understand.

What we're looking at is specific results for each age. Results for 20 year olds, 21 year olds, 25 year olds, and so on.

It's cool that the pivot table did this for us so quickly, but it's not very useful, since the automatic grouping by 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, and so on.

To group ages into buckets like this, right-click any value in the Age field and choose Group from the menu.

When the Grouping dialog box appears, set an interval that makes sense for your data. In this case, I'll group by 10 years.

When you click OK, you'll see your data neatly grouped by age range.

To change the grouping, just repeat the process.

You can use this same approach to group any kind of numeric data in a pivot table.