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

Taught hands-on in Level 5: Expert Techniques & Workbook Architecture · Modern Functions module

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

=CHOOSEROWS(array, row_num1, [row_num2], …)

Arguments

ArgumentRequired?What it does
arrayYesThe range or array to take rows from.
row_num1YesThe first row to return. Negative values count from the bottom.
row_num2, …OptionalAny 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.

D2fx=CHOOSEROWS(A2:B5, 1, 3)
ABCDE
1RepSalesRepSales
2Ana400Ana400
3Ben350Cara500
4Cara500
5Dev275
=CHOOSEROWS(A2:B5, 1, 3) Explain

Example 2 — The last three entries

Negative row numbers make a rolling 'latest entries' view easy.

=CHOOSEROWS(A2:B100, -3, -2, -1) Explain

As new rows are added, wrap the range in TRIMRANGE or use a table reference so the last row really is the newest entry.

Use SEQUENCE to generate row numbers: =CHOOSEROWS(A2:B100, SEQUENCE(5)) returns the first five rows.

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

Kyle Stott

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.