Spreadsheet Practice

How to group ages into ranges in Excel

Formula (Excel and Google Sheets)

=IFS(B2<18,"Under 18",B2<35,"18-34",B2<55,"35-54",TRUE,"55+")

Test the upper limit of each band from the bottom up: =IFS(B2<18,"Under 18",B2<35,"18-34",B2<55,"35-54",TRUE,"55+").

Result on the example
one result per row in C2, filled down to C9
Works in
Excel 2019 and later, including Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in C2, filled down to C9. Change any input and the result updates, or edit the formula to experiment.

How it works

IFS checks the bands from the youngest up and stops at the first TRUE, so each test only needs the band's upper limit: 35 fails "<35" and lands in 35-54. The final TRUE catches everyone 55 and over.

=IFS(B2<18,"Under 18",B2<35,"18-34",B2<55,"35-54",TRUE,"55+")

Result"Under 18"

  1. B217

    The value in B2.

  2. B2<18TRUE

    The < operator tests less than.

  3. B2<35TRUE

    The < operator tests less than.

  4. B2<55TRUE

    The < operator tests less than.

  5. IFS(B2<18,"Under 18",B2<35,"18-34",B2<55,"35-54",TRUE,"55+")"Under 18"

    IFS: Returns the value for the first true test.

    • test1B2<18TRUE
    • value1"Under 18"
    • test2, value2B2<35TRUE
    • test2"18-34"
    • value2B2<55TRUE
    • test3"35-54"
    • value3TRUE
    • test4"55+"

In Google Sheets

The same formula works in Google Sheets: =IFS(B2<18,"Under 18",B2<35,"18-34",B2<55,"35-54",TRUE,"55+"). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(B2,{0,18,35,55},{"Under 18","18-34","35-54","55+"},,-1)Under 18, 18-34, 35-54…Band starts and labels as arrays with "exact or next smaller". Put them in cells to make the bands editable.
=IF(B2<18,"Under 18",IF(B2<35,"18-34",IF(B2<55,"35-54","55+")))Under 18, 18-34, 35-54…Nested IFs, for Excel 2016 and older.

Common mistakes

  • Use < with the next band's start rather than <= with the band's end, so ages like 34.5 can't fall through a gap.
  • Labels like "18-34" are text. Format the column as text if Excel tries to turn them into dates.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Group ages into ranges

In C2, put the age into a group: Under 18, 18-34, 35-54 or 55+. Fill down to C9.

Questions

How do I count people in each group?

=COUNTIF(C2:C9,"18-34"), or a pivot table on the new column.

How do I make 10-year bands automatically?

=FLOOR(B2,10)&"-"&FLOOR(B2,10)+9 gives "20-29", "30-39" and so on.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.