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?

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.

SHARE

Additional Info

Spread the Word Excel

MrExcel.com & related websites debuted on November 21, 1998.

ExcelArticles.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only,
without warranty either expressed or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures
on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
This site contains affiliate links. Any affiliate commissions that we earn when you click a link to Amazon
or other sites is reinvested in keeping MrExcel.com and ExcelArticles.com running. You can earn a commission for
sales leads that you send to us by joining our affiliate program. View my Privacy Policy.

Excel ® is a registered trademark of the Microsoft Corporation.
MrExcel ® is a registered trademark of Tickling Keys, Inc.