Data Transformation FormulaGoogle Sheets V8 Engine Verified

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.

Formula Syntax:=CHOOSEROWS(array, row_num1, [row_num2, ...])

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
arrayRangeRequiredThe source array or table.
row_numIntegerRequiredThe 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)
How it works: Pulls the top 3 highest revenue records.

Example 2: Extract Last 5 Rows from Live Log

=CHOOSEROWS(A2:D, -5, -4, -3, -2, -1)
How it works: Uses negative numbers to retrieve the newest 5 transactions from the bottom.

Example 3: Extract Header and Summary Row Only

=CHOOSEROWS(A1:F50, 1, -1)
How it works: Extracts row 1 (headers) and the final row (totals) into a summary card.

Common Errors & Solutions

❌ #VALUE! Row index out of range
Resolution: Ensure row index numbers do not exceed total row count in array.

Performance Pro Tips

  • Pair with CHOOSECOLS to slice arbitrary subsets of both rows and columns.

Frequently Asked Questions

Does CHOOSEROWS recalculate automatically when data changes?

Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.

Is this formula compatible with Microsoft Excel?

Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.

Related Google Sheets Formulas