Group Employees Into Age Bands

January 13, 2023 - by Bill Jelen

Problem: I work in Human Resources. I need to calculate the number of employees and average salary by groups of ages. Initially, I get a pivot table with one row per age. How do I group this into groups like 25-29, 30-34, and so on?

A pivot table with Age in the Row field. Employee ages start at 26 (1 employee, $18,000 in salary). In this screenshot, you can see details for ages 26 through 31. For example There are 2 employees aged 29. They make a combined $42,000 or an average of $21,000 each. With the cell pointer on the first Age cell, choose Group Field. — Figure 933. Group these rows into groups of 5 years.

Strategy: Choose one cell in the Age field in column A and click Group Field. Excel will initially offer to group the ages into 10-year buckets, starting at age 26. Edit those settings as shown here.

In the grouping dialog box, Starting At 20, Ending at 80, By 5. — Figure 934. Start at 20, go to 89, in groups of 5.

Result: Excel creates a report with groups of dates.

The pivot table now shows age groups:
25-29 - 7 employees averaging $16K
Age 50-54 there are 8 employees averaging $55K and so on. — Figure 935. Excel groups rows into categories.

Gotcha: Excel does not show a category if there were no employees in that category. Note that the ages jump from 69 in row 12 to 85 in row 13.

This article is an excerpt from Power Excel With MrExcel

Title photo by Kier... in Sight on Unsplash