• Excel Book Excerpt

Excel Group Employees Into Age Bands

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Group Employees Into Age Bands

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?

LE10000904.jpg 

Figure 868 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.

LE10000905.jpg 

Figure 869 Start at 20, go to 89, in groups of 5.

Result: Excel creates a report with groups of dates.

LE10000906.jpg 

Figure 870 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.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: