The Excel CHOOSEROWS function returns just the rows you ask for from a range or array — by position, in any order. Its superpower is negative numbers, which count from the bottom: -1 is always the newest entry of a growing log, no matter how long it gets. Available in Excel 365 and Excel 2024+.
=CHOOSEROWS(A2:C6, 1, 2) returns the first two rows instead.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to take rows from. |
row_num1 | Required | The first row to return. 1 = top row of the array; negative numbers count from the bottom (-1 = last row). |
row_num2, … | Optional | More rows, in the order you want them back. Repeats are allowed. |
Available in: Excel for Microsoft 365, Excel 2024+, and Excel for the web. Older versions show #NAME?. The column-wise twin is CHOOSECOLS.
First, last, and specific rows
The shipment log below sits in A1:C6, newest entry at the bottom. The highlighted row is what -1 returns:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Carrier | Packages |
| 2 | Jun 2 | FastShip | 42 |
| 3 | Jun 3 | Redline | 38 |
| 4 | Jun 4 | FastShip | 51 |
| 5 | Jun 5 | Polar | 29 |
| 6 | Jun 6 | Redline | 47 |
Positive numbers count from the top, and you can mix several picks in one call:
Because the picks are positions, not addresses, the formulas keep working as the log grows — point the array at a whole Table column set and -1 always finds the newest row.
Try it: interactive CHOOSEROWS demo
Pick rows from the shipment log above — watch how negative numbers walk up from the bottom.
The power move: top-N lists with SORT
CHOOSEROWS turns "sort the data" into "show me the leaders." SORT arranges the rows; CHOOSEROWS keeps the top three:
It also pairs with SEQUENCE when the row list is itself computed — the last n rows of a log, where n lives in a cell:
Where it sits among its siblings:
| Function | Picks rows by | Typical job |
|---|---|---|
CHOOSEROWS | Position (1, -1, …) | First/last/nth rows, top-N after a SORT |
| FILTER | Criteria (Region="West") | All rows matching a condition |
| TAKE / DROP | A count from either end | "First 5 rows" / "all but the header" |
Errors & common pitfalls
#VALUE! — row number out of range. Asking for row 6 (or -6) of a five-row array, or using 0, returns #VALUE!. Numbers must be between 1 and the row count, or -1 to minus the row count.
#SPILL! — the landing zone isn’t empty. The result spills as many columns wide as the source array. Clear the blocking cells or move the formula.
#NAME? — older Excel. CHOOSEROWS needs Excel 365 or Excel 2024+. In older versions, use INDEX with a row-number array constant.
Pitfall: include the header or not? If your array includes the header row, row 1 is the header. Start the array at the first data row (A2, not A1) when you mean "first record."
Pitfall: blank rows at the bottom count too. If the array includes empty rows, -1 returns zeros from the last blank row. Trim the reference first — in Excel 365, TRIMRANGE does exactly that.
Practice workbook
Frequently asked questions
What do negative row numbers do in CHOOSEROWS?
=CHOOSEROWS(log, -1) always returns the newest entry of a growing log.Which Excel versions have CHOOSEROWS?
How do I get the last row of a table that keeps growing?
=CHOOSEROWS(Orders, -1) with an Excel Table, so the reference expands automatically and -1 always lands on the newest row.What's the difference between CHOOSEROWS and FILTER?
CHOOSEROWS vs TAKE — which should I use?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class