Excel CHOOSEROWS Function

What is the CHOOSEROWS function?

CHOOSEROWS is one of the new dynamic array functions introduced in Excel 365. Its Turkish name is SATIRSEÇ, and it allows you to select specific rows from an array or cell range and return them as a new array. Together with SORT, FILTER, and UNIQUE, it forms the foundation of modern Excel formulas.

Previously, extracting specific rows required complex INDEX-MATCH combinations. With CHOOSEROWS, this task is now a single formula: if you want rows 1, 3, and 5, you simply write =CHOOSEROWS(A1:C100,1,3,5).

CHOOSEROWS Syntax

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

• array: (Required) The cell range or array from which rows will be selected.
• row_num1: (Required) The first row number to select. Positive numbers count from the top; negative numbers count from the bottom (-1 = last row).
• row_num2, ...: (Optional) Additional row numbers to include. The same row can be selected multiple times.

What CHOOSEROWS Does — Practical Use Cases

1. Quickly Pulling the Top N Rows

For a Top 10 report from a sorted table: =CHOOSEROWS(SalesTable,SEQUENCE(10)). SEQUENCE(10) generates {1;2;3;4;5;6;7;8;9;10}, and CHOOSEROWS extracts those rows.

2. Pulling the Last N Rows (Negative Indexing)

One of CHOOSEROWS' most powerful features is negative indexing. Writing =CHOOSEROWS(DataTable,-1,-2,-3) grabs the last three rows — without you needing to know the table's total row count.

3. Selecting Specific Rows for a Summary Report

From a budget table, you can pull only "Total", "Gross Profit", "Net Income" rows for an executive summary: =CHOOSEROWS(FinancialTable,5,10,15).

4. Reordering Rows

To view a table's rows in a different order: =CHOOSEROWS(A1:E10,5,3,1,2,4,6,7,8,9,10). This rearranges row positions without sort formulas.

5. Selecting Even / Odd Rows

Combined with SEQUENCE and MOD, you can extract even-indexed rows: =CHOOSEROWS(DataArray,FILTER(SEQUENCE(ROWS(DataArray)),MOD(SEQUENCE(ROWS(DataArray)),2)=0)). This is useful for grouping rows for analysis.

Advanced Usage Techniques

Pulling Specific Rows from FILTER Results

After filtering rows that match a condition, use CHOOSEROWS to pick exact rows. For example, the first and last customer from filtered results: =CHOOSEROWS(FILTER(Customers,Customers[City]="London"),1,-1).

Combining with Multiple Ranges

Together with VSTACK or HSTACK, you can pick rows from different tables and combine them. For instance, take the totals row from two separate budget tables and stack them side by side.

Header + Data Combination

To show a table's header along with the latest entries: =CHOOSEROWS(Table,1,-3,-2,-1). This shows the header followed by the last three data rows.

Common Mistakes and How to Fix Them

The most common error with CHOOSEROWS is providing an invalid row number. If your table has 50 rows, writing =CHOOSEROWS(A1:C50,100) raises a #VALUE! error. The same applies to negative indexes: -51 exceeds the bounds of a 50-row table. Another major issue is that CHOOSEROWS only works in Excel 365 and Excel 2021 (after the March 2022 update); in Excel 2019 and earlier, you'll get a #NAME? error.

Performance and Version Compatibility

CHOOSEROWS is fully supported in Excel 365, Excel 2024, and Excel for Web. Excel 2021 supports it after the March 2022 update. Excel 2019 and earlier do NOT support it. Performance-wise, selecting from thousands of rows in a single formula is much faster than classic INDEX-MATCH combinations.

Related Functions

• CHOOSECOLS: Selects specific columns from an array.
• FILTER: Filters rows based on a condition.
• INDEX: Returns a single row or column the classical way.
• SEQUENCE: Generates a sequential number array, useful as a CHOOSEROWS argument.
• VSTACK / HSTACK: Stacks multiple arrays vertically or horizontally.

Conclusion

CHOOSEROWS is the "row selector" of modern Excel formulas. From Top N reports to last-N-rows analysis, row reordering, and header+results combinations, it replaces classical functions in many common scenarios. Its negative indexing is especially handy. If you use Excel 365, learning this function and adding it to your routine will visibly boost your productivity.