Cellwise Start free

Dynamic arrays function

Excel CHOOSEROWS function

CHOOSEROWS returns the rows you ask for from a range or array, in the order you list them. Negative numbers count from the bottom, so -1 is always the last row, which makes "latest month" and "top 3" formulas short.

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

Arguments

array
The range or array to take rows from.
row_num1
The first row to return, counted from the top of array. A negative number counts from the bottom: -1 is the last row.
row_num2
Optional. More rows, in the order you want them.

CHOOSEROWS examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

AB
1MonthSales
2Jan48200
3Feb45100
4Mar52600
5Apr50900
6May57300
7Jun54800
8Jul49700
9Aug58100
  1. =CHOOSEROWS(A2:B9,1)

    Result: spills 1 row × 2 columns

    Jan48200

    The first row of the data: Jan and its sales.

  2. =CHOOSEROWS(A2:B9,-1)

    Result: spills 1 row × 2 columns

    Aug58100

    -1 is the last row of the range, so this shows the latest month: Aug.

  3. =CHOOSEROWS(A2:B9,-3,-2,-1)

    Result: spills 3 rows × 2 columns

    Jun54800
    Jul49700
    Aug58100

    The last three months, oldest first.

  4. =CHOOSEROWS(SORT(A2:B9,2,-1),1,2,3)

    Result: spills 3 rows × 2 columns

    Aug58100
    May57300
    Jun54800

    Sort by sales, high to low, then keep the top three months: Aug, May and Jun.

How to use CHOOSEROWS

  1. Click the top-left cell of an empty area.
  2. Type =CHOOSEROWS( and select the data, without the header row.
  3. Add the row numbers you want, separated by commas. Use negative numbers to count from the bottom.
  4. Close the bracket and press Enter.

Common CHOOSEROWS mistakes

Counting sheet rows

Numbers count from the top of array. In CHOOSEROWS(A2:B9,1), row 1 is sheet row 2.

A row number out of range

0, or a number past the last row, returns #VALUE!. Eight rows allow 1 to 8 and -1 to -8.

Blank rows at the bottom

If the range runs past the data, -1 picks an empty row. Select only the data, or use a table, which grows with it.

CHOOSEROWS FAQ

How do I get the last row of data with a formula?

Use =CHOOSEROWS(A2:B9,-1). It returns the whole last row; wrap it in CHOOSECOLS to keep one column.

How do I show the top 3 with a formula?

Sort first, then pick rows: =CHOOSEROWS(SORT(A2:B9,2,-1),1,2,3). In Excel 365, TAKE(SORT(A2:B9,2,-1),3) does the same.

Which Excel versions have CHOOSEROWS?

Microsoft 365, Excel 2024 and Excel for the web. Older versions show #NAME?; there, =INDEX(B2:B9,COUNTA(B2:B9)) returns the last value in a column with no gaps.

Guides that use CHOOSEROWS

Related functions

Updated