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?
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.
Figure 869 Start at 20, go to 89, in groups of 5.
Result: Excel creates a report with groups of dates.
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.