CHOOSEROWS

Return specific rows from an array or range, in any order you choose.

Lookup & Reference
|
Excel 365 only
|
Google Sheets Supported

Spreadsheet editor

Syntax

=CHOOSEROWS(array, row_num1, [row_num2], ...) Returns: Array

Arguments

Argument Required Description
array Yes The source range or array that contains the rows you want to return.
row_num1 Yes The position of the first row to return. Count from 1 at the top, or use a negative number to count from the bottom.
row_num2 No Additional row positions to return, listed in the order you want them.

About

CHOOSEROWS pulls one or more rows out of an array or range and returns them as a new dynamic array. You list the row positions you want, in the order you want them, and Excel spills the result into the cells below. This is handy when you only need a few rows from a long table, or when you want to reorder rows without sorting or touching the original data.

Use CHOOSEROWS when you need specific rows by position, such as the top record, the last entry, or a custom pick like rows 1, 5, and 9. You can repeat a row number to return the same row twice, and you can reverse the order just by listing positions backwards. Negative numbers count from the bottom, so -1 grabs the last row, which is great when the number of rows can grow or shrink.

CHOOSEROWS pairs well with other dynamic array functions. Run FILTER first to keep only the rows that match a condition, or use SORT to order rows before you trim them down. To grab columns by position instead of rows, see CHOOSECOLS, and to pull rows from the very top or bottom of a table, see TAKE.

Examples

Grab the last rows with negative numbers

See how negative positions count from the bottom of the table. This step tracker pulls the last two days, so edit a value or add a row and the result follows the newest entries.

Spreadsheet editor

Build a top-3 leaderboard with SORT

Combine CHOOSEROWS with SORT to rank your reps and keep the top three. Change a sales figure and watch the leaderboard reshuffle instantly.

Spreadsheet editor

Watch out for

⚠

Counting from worksheet row numbers

People assume row_num matches the row numbers on the sheet, so a table starting on row 5 feels like it should use 5.

→ Row numbers are positions inside the array you pass in, not sheet rows. If your array starts on row 5, its first row is still 1 inside CHOOSEROWS.

⚠

Asking for a row that does not exist

Using a number larger than the row count, or 0, returns a #VALUE! error and nothing spills.

→ Keep each row_num between 1 and the number of rows in the array (or use negative numbers within the same range). Use ROWS to check how many rows your array has.

⚠

Output blocked by existing data

CHOOSEROWS spills its result, so if a cell in the output area already has data you get a #SPILL! error.

→ Clear the cells below the formula so the array has empty space to expand into.

Tips & notes

CHOOSEROWS is a Microsoft 365 function and is not available in Excel 2021 or earlier. You can repeat a row number to return the same row more than once, which is useful for building repeated headers or sample sets.

Common questions

Do CHOOSEROWS row numbers use worksheet row labels?

No. The numbers are positions inside the array you pass into CHOOSEROWS. If your array starts on row 5 of the sheet, its first row is still 1 inside the function.

What does a negative row number do?

A negative number counts from the bottom. Use -1 for the last row, -2 for the second to last, and so on. This is handy when the number of rows can change.

When should I use CHOOSEROWS instead of FILTER?

Use CHOOSEROWS when you know the exact row positions you want. Use FILTER when you want every row that meets a condition, since FILTER finds the matches for you.