Table of Contents
In Excel, you can group data by age range using the “Group” feature. This allows you to organize and analyze data based on specific age categories. For example, if you have a column with ages ranging from 20-50, you can group them into categories such as 20-29, 30-39, 40-49, etc. This makes it easier to compare and analyze data within certain age groups. To group data by age range in Excel, you can select the data, go to the “Data” tab, click on “Group,” and then choose the desired age range for the grouping.
Often you may want to group data based on age range in Excel.
Fortunately this is easy to do by using the Group function within an Excel pivot table.
The following example shows how to create the following pivot table in Excel that groups basketball players on based on age ranges:
Example: How to Group Data by Age Range in Excel
Suppose we have the following dataset in Excel that shows the points scored by basketball players on various teams:
Suppose we would like to calculate the sum of points scored by the basketball players based on the following age groups:
- 20-24
- 25-29
- 30-34
To do so, we can first create a pivot table by highlighting the cell range A1:C11, then clicking the Insert tab along the top ribbon, then clicking the PivotTable icon:
In the new window that appears, we will choose to place the pivot table in the Existing Worksheet in cell E1, then click OK:
In the PivotTable Fields panel that appears on the right side of the screen, drag Age into the Rows box and Points into the Values box:
The pivot table will be populated with the following values:
To instead group the first column based on age ranges, right click any value in the first column and then click Group:
We’ll use 20 for Starting at, 34 for Ending at, and 5 for By:
Once we press OK, the age values in the first column will automatically be grouped into age ranges:
From the pivot table we can see:
- The sum of points scored by players between ages 20 and 24 is 117.
- The sum of points scored by players between ages 25 and 29 is 51.
- The sum of points scored by players between ages 30 and 34 is 48.
Note: In this example we chose to calculate the sum of points by age group, but feel free to use another summary metric in the pivot table such as average, count, etc.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: