When assembling executive summaries, dashboard reports, or financial balance sheets in Google Sheets, analysts often need to extract a specific subset of rows or reorder columns from a master dataset. Traditionally, achieving this required writing verbose QUERY strings, complex INDEX/MATCH matrices, or duplicating columns manually. If the underlying data updated, static references often misaligned, resulting in broken formulas.
To provide clean, native array slicing capabilities, Google Sheets introduced the CHOOSEROWS and CHOOSECOLS functions. These dynamic array formulas allow you to cherry-pick, reorder, or duplicate specific rows and columns from an existing table using intuitive numerical indices. Here is a comprehensive guide on their syntax, practical examples, and combined formulas.
Understanding CHOOSEROWS and CHOOSECOLS
Both functions belong to Google Sheets’ modern array manipulation library:
- CHOOSEROWS: Extracts specific rows from an array or range based on row position numbers you provide.
- CHOOSECOLS: Extracts specific columns from an array or range, allowing you to reorganise column sequences dynamically without touching source data.
- Negative Index Support: Supports negative numbers to index rows or columns counting backwards from the end (e.g.,
-1extracts the final row or column).
Function Syntax and Arguments
CHOOSEROWS Syntax
=CHOOSEROWS(array, row_num1, [row_num2, ...])array: The source table or cell range (e.g.,A2:E50).row_num1: The first row index to extract.[row_num2, ...]: Additional row numbers to extract in the specified order.
CHOOSECOLS Syntax
=CHOOSECOLS(array, col_num1, [col_num2, ...])array: The source data range.col_num1: The first column index to extract.[col_num2, ...]: Additional column numbers to extract and reorder.
Practical Example 1: Reordering Table Columns with CHOOSECOLS
Suppose you have an employee database spanning columns A through E: ID (1), First Name (2), Last Name (3), Department (4), Salary (5). For a public directory, you want to show only Last Name, First Name, and Department, in that exact order:
=CHOOSECOLS(A2:E100, 3, 2, 4)Google Sheets dynamically outputs a three-column spill range featuring Last Name first, followed by First Name and Department, while completely omitting ID and Salary.
Practical Example 2: Extracting First and Last Records with CHOOSEROWS
To extract the top performer and the bottom record from a sorted sales table:
=CHOOSEROWS(A2:D50, 1, -1)The index 1 pulls the top row, while the negative index -1 automatically pulls the very last row in the range, regardless of how many entries exist.
| Index Value | Extraction Direction | Result |
|---|---|---|
1 |
Forward from top / left | Extracts the first row or column |
-1 |
Reverse from bottom / right | Extracts the final row or column |
-2 |
Reverse from bottom / right | Extracts the penultimate row or column |
Nesting CHOOSEROWS and CHOOSECOLS Together
You can nest both functions into a single formula to isolate a precise rectangular matrix from a massive dataset:
=CHOOSEROWS(CHOOSECOLS(A2:Z1000, 1, 4, 8), 1, 2, 3, 4, 5)This nested formula first isolates columns 1, 4, and 8, and then extracts only the top 5 rows from those columns, delivering a lightweight, targeted report.