# How to group ages into ranges in Excel

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+").

**Excel:** `=IFS(B2<18,"Under 18",B2<35,"18-34",B2<55,"35-54",TRUE,"55+")` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Respondent | Age | Age group |
| R-01 | 17 |  |
| R-02 | 24 |  |
| R-03 | 35 |  |
| R-04 | 52 |  |
| R-05 | 68 |  |
| R-06 | 34 |  |
| R-07 | 55 |  |
| R-08 | 18 |  |

Formula in C2, filled to C9: `=IFS(B2<18,"Under 18",B2<35,"18-34",B2<55,"35-54",TRUE,"55+")`

Result: Under 18; 18-34; 35-54; 35-54; 55+; 18-34; 55+; 18-34

## 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.

## Other ways

- `=XLOOKUP(B2,{0,18,35,55},{"Under 18","18-34","35-54","55+"},,-1)`: 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+")))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/group-ages-into-ranges