When assembling executive summaries, KPI dashboards, or financial reporting models in Microsoft Excel, analysts frequently need to extract specific rows and columns from sprawling raw data tables. Historically, extracting non-contiguous rows or reordering columns required complex nested INDEX and MATCH matrix formulas or manual copy-pasting that breaks when source data changes. In Excel 365 and Excel 2024, Microsoft introduced dedicated array slicing primitives: CHOOSEROWS and CHOOSECOLS. These dynamic array functions extract and restructure tabular data with concise, readable formula syntax.
Understanding CHOOSEROWS and CHOOSECOLS
Both functions accept a source range or dynamic array and return a new spilled array containing only the designated row or column indices:
=CHOOSEROWS(array, row_num1, [row_num2], ...): Extracts specific vertical rows from the target range.=CHOOSECOLS(array, col_num1, [col_num2], ...): Extracts specific horizontal columns from the target range.
1. Extracting Specific Columns with CHOOSECOLS
Suppose you have a 10-column transactional sales table spanning A2:J500. For an executive dashboard, you only need the Customer Name (Column 2), Product SKU (Column 5), and Total Sale Amount (Column 8). Instead of building three separate formulas, extract all three columns simultaneously:
=CHOOSECOLS(A2:J500, 2, 5, 8)Entering this single formula into cell L2 immediately spills a clean 3-column table containing only the required fields. You can also use CHOOSECOLS to reorder columns arbitrarily (e.g., passing 8, 2, 5 to place Total Sales first).
2. Extracting Top, Bottom, and Specific Rows with CHOOSEROWS
CHOOSEROWS operates identically along the vertical axis. To extract the 1st, 15th, and 30th rows of a dataset:
=CHOOSEROWS(A2:J500, 1, 15, 30)Using Negative Indices to Count Backwards from the End
A major design strength of both functions is native support for negative numbers, which count backwards from the end of the array:
-1: Returns the very last row or column.-2: Returns the second-to-last row or column.
To extract the top 3 and bottom 3 rows of a sorted table in a single combined formula:
=CHOOSEROWS(A2:J100, 1, 2, 3, -3, -2, -1)3. Combining CHOOSEROWS and CHOOSECOLS Concurrently
You can nest both functions to perform precision two-dimensional matrix slicing. If you want to isolate specific rows and specific columns from a dataset simultaneously:
=CHOOSECOLS(CHOOSEROWS(A2:J500, 1, 5, 10), 2, 8)The inner formula extracts rows 1, 5, and 10; the outer formula extracts columns 2 and 8 from that subset, delivering a tidy 3×2 summary matrix.
Chaining with SORT and FILTER for Dynamic Reporting
Because CHOOSEROWS and CHOOSECOLS operate natively within Excel’s dynamic array calculation engine, they chain seamlessly with FILTER and SORT:
=CHOOSECOLS(SORT(FILTER(A2:J100, F2:F100="Completed"), 8, -1), 2, 4, 8)This formula filters the raw table for “Completed” projects, sorts by revenue descending, and extracts only the Customer Name, Project Lead, and Revenue columns.