How to Group People by Age Bracket Using an Excel Pivot Table

Pivot Table for Age Brackets

Pivot Table to Group Age Brackets

In my previous Excel Tutorial, I demonstrated how to use the Group Field command in a Pivot Table to summarize time periods by the Hour. Several viewers wanted to know what other grouping possibilities are available in Pivot Tables. One viewer wanted to know how to group people who responded to a survey by Age Brackets. That is why I created this tutorial!

Use DATEDIF Function to Calculate Age

From my perspective, the DATEDIF() is an invaluable function. However, it is not documented in Excel. Watch how I use its three arguments to calculate the age of each person in the data set.

Group Field by Age

Select a single value in the “Age” Field and then select the “Group Field” Command. In the dialog box, choose the “step-value” for your groups. In this case, I chose the Default Setting of 10 years.

Show Pivot Table Values As

Some of the most powerful Pivot Table tools are found on the “Show Values As” tab of the “Value Field Settings” Dialog Box. Watch me demonstrate how to show each Age Bracket as a Percentage of the Column.

Apply Conditional Formatting to Pivot Table

Take advantage of the greatly improved Conditional Formatting Commands in Excel 2007 and Excel 2010 to focus attention on the key information in your Pivot Table. In this case, I use the “Top 10 Items” rule to apply Conditional Formatting to the two highest Age Brackets- by percentage – for each gender.

Learn Excel Pivot Tables Quickly

Follow this link to learn about the focused 90 minute video tutorials that I have published to help you to really learn how to get the most out of Pivot Tables. Available in versions for Excel 2003, Excel 2007, and Excel 2010.

Watch Tutorial in High Definition

Click on this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels


Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn


  1. The use of DATEIF() as demonstrated in the video leaves me with #VALUE! and #NUM! in the Age column. What could be the reason for not showing the number of the years? Thanks.

  2. You will get a #VALUE! error if one or more of the dates that you refer to in your formula is not properly entered as a number. Check the alignment for your date entries – if they do NOT align to the Right-side of the cell, you have entered a Text Label. For a proper date entry, use this shortcut – Ctrl + ; (Semi-colon) which enters the current date. It is also possible that your cell(s) are formatted as Text – so clear ALL formatting before you enter dates and formulas.

    For the “NUM! error message to appear, you most likely selected the later date as your first argument. The proper syntax for DATEDIF is StartDate, EndDate, “y” – for the difference in years.

    Thanks for adding your comment!

    Danny Rocks
    The Company Rocks

  3. Neli’s problem has a simple solution. The function is DATEDIF not DATEIF!



  1. […] by the Hour. Several viewers wanted to know what other … … See the original post: Use an Excel Pivot Table to Group People by Age Bracket ← Looking for the Advance Excel Training Gauteng That You Need … Advance Excel […]

  2. […] you watched my previous tutorial, I used the same data set and a Pivot Table to Group these 500 plus Sample Records by Age Bracket. […]

  3. […] Group by Age Bracket in an Excel Pivot Table […]

Speak Your Mind