Age Group Formula in Excel: Put Ages Into Bands

An age group formula in Excel turns an exact age, like 34, into a band, like 30-44, so you can count and compare groups in a survey, a customer list or an HR report. The quickest way is IFS. For bands that change often, a small lookup table with VLOOKUP is easier to maintain. This guide shows both, plus a one-line formula for equal 10-year bands and how to count each group.

The quick answer: an IFS age group formula#

IFS checks a list of conditions in order and returns the value for the first one that is true. For five age bands, type this in C2 and fill it down:

=IFS(B2<18,"Under 18",B2<30,"18-29",B2<45,"30-44",B2<60,"45-59",TRUE,"60+")

All the examples use this list:

For Rosa, 34 is not under 18 and not under 30, but it is under 45, so IFS returns 30-44 and stops. The last pair, TRUE,"60+", catches everything left, because TRUE is always true. IFS needs Excel 2019, Excel 2021 or Microsoft 365; the IFS function guide and the article on the IFS function cover the syntax.

How the bands work: order and boundaries#

The order of the tests matters. IFS stops at the first true condition, so start with the youngest band and work up. If you put B2<60 first, everyone under 60 would land in "45-59".

Each test uses less than the next band's lower limit. Look at the edges:

• Jens is 29: 29 < 30 is true, so he is in 18-29.
• Nadia is 30: 30 < 30 is false, 30 < 45 is true, so she is in 30-44.
• Malik is 45: he moves up to 45-59.

Because each test starts where the last one ended, no age can fall into a gap or into two bands. Avoid labels that overlap, such as "18-30" and "30-45": a 30-year-old can't be in both.

In older Excel, nest IF functions in the same order. It works everywhere, but the brackets are easy to get wrong:

=IF(B2<18,"Under 18",IF(B2<30,"18-29",IF(B2<45,"30-44",IF(B2<60,"45-59","60+"))))

The same pattern turns test scores into letter grades; see the grade formula guide.

Age groups with VLOOKUP and a band table#

When you have many bands, or the bands might change, put them in a table and look them up. Each row holds the lowest age in the band and its label, sorted from smallest to largest:

Then in C2:

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

TRUE turns on approximate match: VLOOKUP finds the largest "From" value that is less than or equal to the age. For 45 that's the 45 row, so the result is 45-59. For 30 it's 30-44. The $ signs lock the table so it stays put when you fill the formula down.

To change the bands, edit the table; every formula updates. Two rules: the first column must be sorted smallest first, and the first row must cover the lowest possible age (0 here), or young ages return #N/A. The VLOOKUP function guide and VLOOKUP approximate match explain the details. In Excel 2021 or Microsoft 365, =XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,,-1) does the same, and the table doesn't need to be sorted.

Equal 10-year bands with FLOOR#

If every band is the same width, you don't need a list of bands at all. FLOOR rounds a number down to a multiple you choose:

=FLOOR(B2,10)&"-"&FLOOR(B2,10)+9

For 34, FLOOR(34,10) is 30, so the result is 30-39. For 58 it's 50-59. The & joins the pieces into one label. Change both 10s to 5 for five-year bands. This is quick, but you can't make a special "Under 18" or "60+" group with it.

Get the age first if you only have a birth date#

Many lists hold dates of birth, not ages. Work out the age in a helper column first, then band it. With the birth date in B2 and the report date in H1:

=DATEDIF(B2,$H$1,"y")

DATEDIF with "y" returns the number of full years between the two dates. Use a fixed report date, not TODAY(), if the groups must match a report from a set day. The guide to calculating age and tenure covers months and days too.

Count how many people are in each age group#

Once each person has a band, count the bands. COUNTIFS with two conditions counts straight from the ages:

=COUNTIFS(B2:B8,">=30",B2:B8,"<45")

This returns 2 (Rosa and Nadia). Or count the labels in column C: =COUNTIF(C2:C8,"45-59") also returns 2 (Malik and Pete). Build a small summary table with one row per band, and the counts make a quick chart. The frequency table guide shows the full layout.

Watch for these common mistakes:

• Ages stored as text. "34" as text is not less than 45, so the formula returns the wrong band. Convert the column with VALUE first.
• Blank ages. A blank cell counts as 0, so it lands in "Under 18". Wrap the formula: =IF(B2="","",IFS(...)).
• Labels turning into dates. If you type a label like 1-12 straight into a cell, Excel may change it to a date. Labels inside a formula's quotes are safe.

Practise it#

Banding values with IF, IFS and lookup tables is a favourite job-test task, because it checks logic and lookups at once. Practise nested IF and IFS in a live spreadsheet: you type the formula and it checks it instantly. Then build a band table in the VLOOKUP approximate match lesson, or try the exercise on this page first.