How to Use the CHOOSEROWS and CHOOSECOLS Functions in Excel

Why Extracting Subsets of Data in Excel Used to Be Painful

In traditional Microsoft Excel workflows, extracting specific rows or non-adjacent columns from a master data table was surprisingly cumbersome. Financial analysts, accountants, and administrative teams often resorted to complex nested index formulas (such as INDEX combined with MATCH, FILTER, or volatile OFFSET statements) or wrote custom VBA macros just to cherry-pick particular data slices for executive summaries.

To eliminate this friction and empower dynamic array formulas, Microsoft introduced two purpose-built array manipulation functions in modern Excel (Microsoft 365 and Excel for the web): CHOOSEROWS and CHOOSECOLS. These functions allow you to extract exactly the rows and columns you need using intuitive numeric indices, outputting dynamic, auto-spilling results that update instantly when your source data changes.

Syntax and Core Parameters

The CHOOSEROWS Function

=CHOOSEROWS(array, row_num1, [row_num2], ...)

• array: The source table, range, or array formula from which you want to return rows.
• row_num1: The numeric index of the first row to return. Passing a positive number counts from top to bottom (e.g. 1 returns the first row). Passing a negative number counts backwards from the bottom (e.g. -1 returns the final row).
• [row_num2, ...]: Optional additional row indices in the exact sequence you want them displayed.

The CHOOSECOLS Function

=CHOOSECOLS(array, col_num1, [col_num2], ...)

• array: The source table, range, or array formula.
• col_num1: The numeric column index to extract. Positive integers count from left to right; negative integers count from right to left.
• [col_num2, ...]: Optional additional column indices.

Practical Example 1: Reordering and Extracting Columns (CHOOSECOLS)

Imagine you have an employee roster occupying range A2:E50 with columns in this order: Employee ID (Col 1), Full Name (Col 2), Department (Col 3), Hire Date (Col 4), Annual Salary (Col 5).

You need to create a simplified board presentation that shows only Full Name (Col 2) and Annual Salary (Col 5), but you want Salary displayed first, followed by Name:

=CHOOSECOLS(A2:E50, 5, 2)

Excel creates a dynamic spill range containing two columns. Column 5 becomes the first column of the output, and Column 2 becomes the second column. If salaries update in the master sheet, this view recalculates immediately.

Practical Example 2: Extracting First, Middle, and Last Records (CHOOSEROWS)

Suppose you have sorted a list of product sales in range A2:D100 from highest to lowest revenue. You need to pull the top performer (Row 1), the median entry (Row 50), and the lowest performer (the final row):

=CHOOSEROWS(A2:D100, 1, 50, -1)

Using -1 as the third argument is a best practice: it guarantees that even if rows are inserted or removed in the source data, the formula will always dynamically grab the true final record at the bottom of the dataset.

Advanced Synergy: Combining CHOOSEROWS, CHOOSECOLS, and SORT

Because these functions return native dynamic arrays, you can nest them inside sorting and filtering pipelines. For example, to find the top 3 highest-earning departments from a raw data range A2:D100 (where revenue is stored in column 4):

=CHOOSEROWS(SORT(A2:D100, 4, -1), 1, 2, 3)

This formula sorts the entire table descending by column 4, and CHOOSEROWS instantly slices out only the top three rows of the sorted output.

Common Errors and Troubleshooting

• #VALUE! Error: Occurs if an index argument is set to zero (0). Row and column indices in Excel must be either positive integers (1, 2, 3…) or negative integers (-1, -2, -3…).
• #SPILL! Error: Appears when cells directly below or to the right of your formula already contain data. Clear the obstruction path to allow the formula array to expand cleanly.
• #REF! Error: Thrown when you specify an index that exceeds the total boundaries of your source range (for instance, asking for row 25 from a range that only contains 20 rows).

Frequently Asked Questions

Can I pass an array of numbers instead of individual arguments?

Yes. You can pass an array constant such as =CHOOSECOLS(A2:E50, {1, 3, 5}) or supply a cell range containing dynamic sequence numbers generated by SEQUENCE.

Are CHOOSEROWS and CHOOSECOLS available in older Excel versions?

These functions are exclusive to Microsoft 365, Office 2024, and Excel for the web. Users opening the workbook in legacy desktop releases (such as Excel 2019 or Excel 2016) will encounter the _xlfn. unsupported function tag.