Pivot Table Group by Age: How To & Examples

To group ages in a pivot table, you use the “Group” feature on a numeric field, which lets you set a starting point, ending point, and interval (like 10-year buckets). This article explains exactly how to do it in Excel and Google Sheets, how to fix common grouping problems, and how to choose the right age ranges for your data.

Formula

The calculator above converts an age in years to minutes and hours using these formulas:

• Age in minutes = years × 365.25 days/year × 24 hours/day × 60 minutes/hour
• Age in hours = years × 365.25 days/year × 24 hours/day

Variables:

• years – the age you enter (unit: years)
• Age in minutes – output (unit: minutes)
• Age in hours – output (unit: hours)

The 365.25 factor accounts for leap years (one extra day every four years on average).

Worked Example

Using the default values (age = 35 years):

1. Minutes: 35 × 365.25 × 24 × 60 = 35 × 525,960 = 18,408,600 minutes
2. Hours: 35 × 365.25 × 24 = 35 × 8,766 = 306,810 hours

The calculator shows 18,408,600 minutes and 306,810.00 hours.

Why Group Ages in a Pivot Table?

Raw age data (e.g., 23, 45, 67) is too granular for most reports. Grouping turns it into meaningful buckets like 0–9, 10–19, 20–29, etc. This helps you see patterns—such as which age group has the highest sales or most website visits—without squinting at hundreds of individual numbers.

How to Group Ages in Excel (Step-by-Step)

1. Select any cell inside your pivot table that contains the age values.
2. Right-click and choose Group (or go to the PivotTable Analyze tab → Group Field).
3. In the dialog box, set Starting at (e.g., 0) and Ending at (e.g., 100).
4. Set By to the interval you want (e.g., 10 for 10-year buckets).
5. Click OK. The pivot table now shows age groups instead of individual ages.

If you want custom ranges like 18–25, 26–35, etc., you cannot use the automatic grouping. Instead, create a new column in your source data with a formula like =IF(A2<18,"Under 18",IF(A2<=25,"18-25",...)) and refresh the pivot table.

How to Group Ages in Google Sheets

Google Sheets pivot tables do not have a built-in “Group” button for numbers. You have two workarounds:

• Add a helper column in your data with a formula that assigns each age to a bucket. For example: =ARRAYFORMULA(IF(A2:A="",,FLOOR(A2:A/10)*10 & "-" & (FLOOR(A2:A/10)*10+9))) – this creates “0-9”, “10-19”, etc.
• Use a QUERY function to aggregate by a calculated bucket, e.g., =QUERY(A:B, "select floor(A/10)*10, count(B) group by floor(A/10)*10 label floor(A/10)*10 'Age Group'").

Both methods require a bit of setup but give you full control over the ranges.

Common Problems and Fixes

• Grouping is grayed out: This happens when the age field is not numeric (e.g., stored as text). Convert the column to numbers (select column → Data → Text to Columns → Finish, or use =VALUE()).
• Grouping creates too many groups: Check your “By” interval. If you set it to 1, you get every age as its own group. Use 5, 10, or 20.
• “Cannot group that selection” error: Often because there are blank cells or text in the age column. Remove blanks and ensure all cells are numbers.
• Groups appear in the wrong order: If you have text like “0-9” vs “10-19”, Excel sorts alphabetically (so “10-19” comes before “0-9”). Use numeric grouping or a custom list to fix.

How to Choose Age Intervals

There is no universal rule; it depends on your analysis. Common choices:

• 5-year intervals for detailed demographic studies (e.g., 0–4, 5–9, …).
• 10-year intervals for broad trends (e.g., 20–29, 30–39).
• Life-stage groups like 0–17, 18–64, 65+ for policy or marketing.

Consider your sample size: too many groups with few people each make patterns noisy. Too few groups hide important differences. Aim for at least 5–10 observations per group.

Frequently Asked Questions

Can I group ages in a pivot table without changing the source data?

Yes, in Excel you can group directly on the pivot table field. In Google Sheets, you need a helper column because the pivot table itself doesn’t support numeric grouping.

Why does my pivot table show ages as text like “20s”?

That happens if your source data already contains text labels. The grouping feature only works on numeric values. Convert the text to numbers before creating the pivot table.

How do I group ages into custom ranges like 18-25, 26-35?

Use a formula in your source data to create a new column with the custom labels, then add that column to the pivot table as a row field. Excel’s automatic grouping only does equal intervals.

Can I group ages by decade in a pivot table?

Yes. In Excel, right-click the age field → Group → set Starting at 0, Ending at 100, and By 10. That gives you 0-9, 10-19, 20-29, etc. In Google Sheets, use the helper column formula with FLOOR.

What if my age data has decimals (like 25.5)?

Grouping still works, but the groups will include decimals (e.g., 20-29 includes 25.5). If you want whole-year groups, round the ages first using =ROUND() or =INT() in a helper column.

How do I ungroup ages in Excel?

Right-click any grouped cell → choose Ungroup → select the field you want to ungroup. This restores the original individual ages.

Related

• Set Time Limit App iPhone iOS 12
• SSL Ports Explained: Examples & How to Choose
• Troubleshoot Personal AI with Outdated Device Context

in t | Total minutes | min | 0 | 0
in d | Age (years) | years | 35 | 0
out pace | Age in minutes | min | d * 525600 | 0
out speed | Age in hours | h | d * 8760 | 2