Google Sheets CHOOSEROWS: Slice & Reorder Specific Rows Dynamically
CHOOSEROWS extracts specific rows from an array or range by their index numbers. Supports negative indexing to pull rows from the bottom of a dataset.
=CHOOSEROWS(array, row_num1, [row_num2, ...])Technical Overview & Engine Mechanics
Formula Comparison: CHOOSEROWS vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| array | Range | Required | The source array or table. |
| row_num | Integer | Required | The row index to extract (1 = first row, -1 = last row). |
Real-World Spreadsheet Examples
Explore verified operational formulas used in enterprise reporting and database workflows:
Example 1: Extract Top 3 Highest Rows from Sorted Table
=CHOOSEROWS(SORT(A2:D100, 4, FALSE), 1, 2, 3)Example 2: Extract Last 5 Rows from Live Log
=CHOOSEROWS(A2:D, -5, -4, -3, -2, -1)Example 3: Extract Header and Summary Row Only
=CHOOSEROWS(A1:F50, 1, -1)Example 4: Extract Top 5 Performing Sales Records
=CHOOSEROWS(SORT(A2:E500, 5, FALSE), 1, 2, 3, 4, 5)Example 5: Extract the Last Row of a Dynamic Array
=CHOOSEROWS(A2:D100, -1)Common Errors & Solutions
Performance Pro Tips
- Pair with CHOOSECOLS to slice arbitrary subsets of both rows and columns.
Frequently Asked Questions: CHOOSEROWS
How do negative numbers work in CHOOSEROWS?
-1 returns the last row, -2 returns the second to last row, and so on.
Can I extract non-consecutive rows?
Yes. You can extract arbitrary rows like CHOOSEROWS(data, 1, 5, 12, 45).
Can I reverse row order using CHOOSEROWS?
Yes: =LET(d, A2:D10, CHOOSEROWS(d, SEQUENCE(ROWS(d), 1, ROWS(d), -1))).
What is the sister function for columns?
CHOOSECOLS performs the identical operation horizontally on columns.
Is CHOOSEROWS supported in Excel?
Yes, Microsoft Excel 365 supports CHOOSEROWS.
Related Google Sheets Formulas
QUERY
The QUERY function is Google Sheets' most versatile formula, executing a SQL-like Google Visualization API Query Language string across spreadsheet tables to filter, aggregate, group, pivot, and sort data in a single formula.
Data TransformationARRAYFORMULA
ARRAYFORMULA enables single spreadsheet formulas to output an entire matrix or column of calculated results automatically. When new rows are entered, ARRAYFORMULA calculates them instantly without requiring manual formula dragging.
Data TransformationFILTER
FILTER dynamically outputs only the rows or columns from a source range that satisfy one or more logical conditions. As source data updates, filtered results re-calculate automatically.