Exceljet

Quick, clean, and to the point

Pivot table group by age

Pivot tables have a built-in feature to group numbers into buckets at a given interval. In the example shown, a pivot table is used to group a list of 300 names into age brackets separated by 10 years. This numeric grouping is fully automatic.

Fields

The source data contains three fields: Name, Age, and Group. Only Name and Age are used in the pivot table as shown:

Pivot table group by age field list

Age is used as a Row field. After Age has been added to the pivot table, it has been grouped as below:

Grouping configuration for age

Starting and ending value are automatically entered based on the source data. The "by" is set to 10 years, but can be customized as needed.

The pivot table maintains age grouping when fields are added or reconfigured. For example, when the Group field is added as a Column field, the pivot table below is created:

With Group field added as a Column field

Steps

  1. Create a pivot table
  2. Add Age as a Row field
  3. Add Name as a Value field
  4. Group Age into buckets of 10 years

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.