INDEX and CHOOSE have long been the go-to tools for pulling specific columns from a table. Both work, but both require knowing the column number and both produce verbose formulas. CHOOSECOLS and CHOOSEROWS replace that complexity with a single, readable function call. Pass the array and one or more column or row numbers. The function returns exactly those columns or rows in that order. Negative numbers count from the right or bottom. You can even reorder columns, repeat them, or reverse the entire array — all without a helper column.
This guide covers both functions fully, with eight practical examples. You will learn how to extract named columns from a FILTER result, reorder and reverse a table, build a dynamic column picker, and combine CHOOSECOLS with SORT and UNIQUE. Each example also shows the pre-365 equivalent so you understand what these functions replace.
What Do CHOOSECOLS and CHOOSEROWS Do?
CHOOSECOLS returns selected columns from an array in the order you specify. CHOOSEROWS does the same for rows. Both accept multiple column or row numbers as separate arguments. Negative numbers count from the end. Column -1 is the last column; -2 is second-to-last. Consequently, you can write clean formulas without knowing the total column count.
Additionally, both accept repeated arguments. Passing the same column number twice returns that column twice. This makes it easy to duplicate a column or reverse the column order. Together, these make CHOOSECOLS and CHOOSEROWS the most flexible column-selection tools in Excel 365.
What Is the CHOOSECOLS and CHOOSEROWS Syntax?
| Argument | Required? | Description |
|---|---|---|
| array | Required | The source array or range. Can be a cell range, named range, or any dynamic array expression (FILTER, SORT, UNIQUE, etc.). |
| col_num / row_num | Required (at least one) | The column or row number to extract. Positive = count from the left/top. Negative = count from the right/bottom. Multiple numbers return multiple columns/rows in that order. Repeated numbers duplicate a column/row. |
Examples 1–8: CHOOSECOLS and CHOOSEROWS in Practice
The simplest use is selecting one or more columns from a table by their position. The result spills into as many columns as you specified. Columns appear in the order you list the numbers — not necessarily the source order.
CHOOSECOLS supports repeated column numbers and reverse ordering. You can reverse a table’s column order by listing the numbers in descending sequence, or duplicate a column by referencing the same number twice. These operations previously required SORT with a custom sort array or multiple INDEX calls.
FILTER returns all columns of a table, but you often only need a subset. Specifically, wrapping CHOOSECOLS around FILTER selects exactly the columns you want. This is cleaner than pre-selecting column ranges inside FILTER.
A dropdown that controls which columns appear in a report is a powerful dashboard pattern. When a user selects "Score", the report shows Name and Score. When they select "Date", it shows Name and Date. CHOOSECOLS with MATCH makes this possible without any VBA.
Examples 5–8: Rows, Combinations, and Advanced Patterns
CHOOSEROWS works identically to CHOOSECOLS but selects rows instead of columns. It is useful for extracting specific records by position, building a top-and-bottom comparison (first row and last row), or selecting alternating rows from a table. Negative row numbers count from the bottom.
Combining CHOOSECOLS and CHOOSEROWS extracts a rectangular sub-table from a larger range. For example, you can extract columns 1, 3, and 5 from rows 1, 10, and 20 in one nested formula. This is cleaner than INDEX with row and column arrays.
CHOOSECOLS has no "exclude column" argument. However, combining it with SEQUENCE returns all columns except a specified one. This replaces a common FILTER workaround for dropping unwanted columns.
Hard-coded column numbers break when columns move. Using MATCH to convert a header name to a column number makes CHOOSECOLS robust to changes. This is particularly useful in team workbooks where columns may shift.
Common Issues and How to Fix Them
CHOOSECOLS returns #VALUE!
#VALUE! usually means a column number is out of range. Referencing column 5 in a 4-column array causes an error. Always match the column numbers to the array width. Also verify the col_num is a number, not text — a header name without MATCH causes the same error.
The function is not available in my version
CHOOSECOLS and CHOOSEROWS require Excel 365. They are not available in Excel 2021, 2019, or Google Sheets. For compatibility, use INDEX with an array of column numbers: =INDEX(A2:D20, 0, {1, 3}) returns columns 1 and 3 in Excel 2019 and later (with Ctrl+Shift+Enter in 2019). In Excel 365 without CHOOSECOLS, HSTACK can stack individual column ranges side by side as an alternative.
Frequently Asked Questions
-
What does CHOOSECOLS do in Excel?+CHOOSECOLS selects and returns specific columns from an array in the order you specify. Pass the source array and one or more column position numbers. Positive numbers count from the left. Negative numbers count from the right — so -1 means the last column regardless of how wide the array is. You can reorder columns, duplicate columns, or reverse the array by listing the column numbers in any order. Multiple numbers return multiple columns as a spilled result. The function is available in Excel 365 only.
-
What is the difference between CHOOSECOLS and INDEX?+Both can return specific columns. INDEX(A:D, 0, {1,3}) selects columns but requires Ctrl+Shift+Enter in older Excel and is less readable. CHOOSECOLS is purpose-built for column selection, requires no array entry in Excel 365, supports negative indexing naturally, and accepts the column numbers as simple comma-separated arguments rather than an array constant. CHOOSECOLS is also easier to combine with MATCH for name-based selection.
-
Can CHOOSECOLS and CHOOSEROWS work with FILTER?+Yes — this is one of their most powerful uses. Wrap CHOOSECOLS around FILTER to select specific columns from the filtered result: =CHOOSECOLS(FILTER(A2:E100, B2:B100="Engineering"), 1, 3) returns the Name and Score columns for Engineering employees only. Similarly, combine CHOOSEROWS with FILTER to select specific rows from a filtered output. Both functions accept any array expression as input, so any dynamic array result can be passed directly as the first argument.
-
How do I select columns by name instead of number?+Use MATCH to convert a column header name into a column number, then pass that to CHOOSECOLS: =CHOOSECOLS(A2:E100, MATCH("Score", A1:E1, 0)). This makes the formula robust to column reordering because MATCH always finds the correct position regardless of where the column is. For multiple named columns, use separate MATCH calls as separate arguments: =CHOOSECOLS(A2:E100, MATCH("Name", A1:E1, 0), MATCH("Score", A1:E1, 0)).