Extracting specific rows and columns from expansive data ranges in Google Sheets historically required complex combinations of QUERY, INDEX, FILTER, or nested array literals ({...}). Modern spreadsheet workflows frequently demand reorganizing tabular reports, plucking arbitrary summary records, or reordering columns dynamically without modifying raw data sources. Google Sheets simplifies array transformations with CHOOSEROWS and CHOOSECOLS. These native matrix functions return dynamic spilled arrays containing only the specified rows or columns by numerical position.
Syntax and Core Parameters of CHOOSECOLS
The CHOOSECOLS function isolates specified vertical columns from a source range or formula output:
=CHOOSECOLS(array, col_num1, [col_num2, ...])array: The source data range, array constant, or formula result (e.g.,A2:Z100).col_num1: An integer representing the index of the first column to retrieve (1-based index from left to right).[col_num2, ...]: Optional additional column indices to retrieve in your desired output order.
Example 1: Reordering and Extracting Columns
# Extract Employee Name (Col 1), Department (Col 3), and Salary (Col 5)
=CHOOSECOLS(A2:F500, 1, 3, 5)
# Rearrange columns to put Department first, then Employee Name
=CHOOSECOLS(A2:F500, 3, 1)Syntax and Practical Applications of CHOOSEROWS
The CHOOSEROWS function extracts horizontal records from a range based on specified row indices:
=CHOOSEROWS(array, row_num1, [row_num2, ...])array: The source table or array to slice.row_num1: The integer index of the specific row to extract (1-based index from top to bottom).[row_num2, ...]: Optional additional row indices to retrieve.
Example 2: Extracting First, Middle, and Benchmark Records
# Extract header summary, benchmark row 10, and audit row 25
=CHOOSEROWS(A2:E100, 1, 10, 25)Using Negative Indices for Reverse Indexing
Both CHOOSEROWS and CHOOSECOLS support negative integers, allowing you to index items backwards starting from the end of the data set:
-1: Refers to the last row or column.-2: Refers to the second to last row or column.
Example 3: Extracting the Most Recent Entries and Totals
# Retrieve the last 3 rows from a growing sales log
=CHOOSEROWS(A2:D, -3, -2, -1)
# Retrieve only the first column and the very last column
=CHOOSECOLS(A2:M100, 1, -1)Nesting with SORT, FILTER, and UNIQUE
Pairing CHOOSEROWS and CHOOSECOLS with dynamic array functions unlocks powerful dashboards:
# Extract top 3 highest revenue clients, showing only Name (Col 1) and Revenue (Col 4)
=CHOOSECOLS(CHOOSEROWS(SORT(A2:E100, 4, FALSE), 1, 2, 3), 1, 4)
# Dynamically pluck columns from a filtered dataset
=CHOOSECOLS(FILTER(A2:G200, F2:F200 = "West Region"), 1, 2, 7)By replacing verbose formulas with clean index arrays, CHOOSEROWS and CHOOSECOLS make Google Sheets models faster, more readable, and effortless to maintain.