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:
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:
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.
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.
The final result
When you click OK, you’ll see your data neatly grouped by age at 10 year 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.
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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
title: “How To Group A Pivot Table By Age Range” ShowToc: true date: “2022-11-15” author: “Angela Mclaughlin”
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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
