Excel: Group Employees Into Age Bands

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

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?

  1. 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.

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

    Result: Excel creates a report with groups of dates.

  3. 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.

For more resources for Microsoft Excel