# CHOOSEROWS function in Excel

> CHOOSEROWS returns the rows you name from a range or array: 1 is the first row and -1 the last, so CHOOSEROWS(range,-1) always gives the latest entry.

Source: https://spreadsheetpractice.com/functions/chooserows · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** CHOOSEROWS returns the rows you name from a range or array: 1 is the first row and -1 the last, so CHOOSEROWS(range,-1) always gives the latest entry.
- **Syntax:** `=CHOOSEROWS(array, row_num1, [row_num2], ...)`
- **Most common use:** `=CHOOSEROWS(A2:B5,-1)` returns Apr (The latest month)
- **Available in:** Excel 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** An array with the chosen rows.
- **Google Sheets:** Available in Google Sheets.

## What it does

CHOOSEROWS returns the rows you name from a range or array: 1 is the first row and -1 the last, so CHOOSEROWS(range,-1) always gives the latest entry. Use it to pull the top and bottom rows of a sorted list, or specific positions such as every quarter-end month.

## Syntax

`=CHOOSEROWS(array, row_num1, [row_num2], ...)`

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range or array to take rows from. |
| `row_num1` | Required | The first row to return; negative counts from the bottom. |
| `row_num2` | Optional | Optional: more rows. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | `=CHOOSEROWS(A2:B5,-1)` | Apr (spills 1 × 2) | The latest month |
| D4 | `=CHOOSEROWS(A2:B5,1,3)` | Jan (spills 2 × 2) | The first and third rows |
| G2 | `=CHOOSEROWS(SORT(A2:B5,2,-1),1)` | Apr (spills 1 × 2) | The best month: sort by sales, keep row 1 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Month | Sales |  |  |  |  |  |  |
| 2 | Jan | 1200 |  | Apr | 1500 |  | Apr | 1500 |
| 3 | Feb | 1350 |  |  |  |  |  |  |
| 4 | Mar | 990 |  | Jan | 1200 |  |  |  |
| 5 | Apr | 1500 |  | Mar | 990 |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | A row number is 0, or beyond the number of rows. |
| The "latest" row isn't the latest | CHOOSEROWS counts positions, not dates. Sort first if position should mean something. |

## Tips

- =CHOOSEROWS(A2:C100,1,-1) returns the first and last rows together.

## Related functions

- [CHOOSECOLS](https://spreadsheetpractice.com/functions/choosecols): CHOOSECOLS returns only the columns you name from a range or array, in the order you name them, so 3,1 puts the third column first.
- [TAKE](https://spreadsheetpractice.com/functions/take): TAKE keeps the first or last rows (and columns) of a range and drops the rest.
- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.

## Questions

### How do I get the last row of a list in Excel?

=CHOOSEROWS(A2:C20,-1). Negative numbers count from the bottom.

### What is the difference between CHOOSEROWS and TAKE?

TAKE keeps a block from the start or end. CHOOSEROWS picks any rows, in any order.

## Practice

A graded CHOOSEROWS exercise ("First and last month") is on [the CHOOSEROWS page](https://spreadsheetpractice.com/functions/chooserows). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.