CHOOSEROWS Function
Pulls out just the rows you ask for, by their position.
Category
Lookup & Reference
Available in
Microsoft 365, Excel 2024
Returns
A spilled array of the chosen rows
Overview
CHOOSEROWS is the row-based twin of CHOOSECOLS. Give it a table and one or more row numbers, and it spills back only those rows — in the order you list them.
Negative numbers count from the bottom, so =CHOOSEROWS(A2:C20, -1) always grabs the last row, even as the list grows.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The range or array to take rows from. |
| row_num1 | Yes | The first row to return. Negative values count from the bottom. |
| row_num2, … | Optional | Any more rows to return, in the order you want them. |
Examples
Example 1 — Picking specific rows
We want the 1st and 3rd sales reps from a small table.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Rep | Sales | |
| 2 | Ana | 400 | Ana | 400 | |
| 3 | Ben | 350 | Cara | 500 | |
| 4 | Cara | 500 | |||
| 5 | Dev | 275 |
Example 2 — The last three entries
Negative row numbers make a rolling 'latest entries' view easy.
As new rows are added, wrap the range in TRIMRANGE or use a table reference so the last row really is the newest entry.
Common mistakes
- #VALUE! errors. A row number is 0, or larger than the number of rows in the array.
- #SPILL! errors. Something is sitting in the cells the result needs to spill into. Clear them.
Notes
- Rows can be repeated: =CHOOSEROWS(A2:B5, 1, 1) returns the first row twice.
- Related functions: CHOOSECOLS, TAKE, DROP, FILTER, INDEX.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.