The Excel CHOOSECOLS function returns just the columns you ask for from a range or array — in any order, as many times as you like. One formula extracts a clean report from a wide table, and negative numbers count from the end, so -1 always means the last column. Available in Excel 365 and Excel 2024+.
=CHOOSECOLS(A2:D6, -1) returns the last column — whatever width the table grows to.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to take columns from. |
col_num1 | Required | The first column to return. 1 = leftmost column of the array; negative numbers count from the end (-1 = last column). |
col_num2, … | Optional | More columns, in the order you want them back. Repeats are allowed. |
Available in: Excel for Microsoft 365, Excel 2024+, and Excel for the web. Older versions show #NAME?. The row-wise twin is CHOOSEROWS.
Pick, reorder, and repeat columns
The orders table below sits in A1:D6. To build a two-column summary of customers and amounts — skipping the ID and region — ask for columns 2 and 4:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order ID | Customer | Region | Amount |
| 2 | 1001 | Acme Corp | West | $1,850 |
| 3 | 1002 | Bolt LLC | East | $920 |
| 4 | 1003 | Cedar Inc | West | $2,400 |
| 5 | 1004 | Delta Co | North | $1,310 |
| 6 | 1005 | Echo Ltd | West | $480 |
The column numbers also control order, so the same function rearranges a table without touching the source:
Negative numbers count from the right edge — perfect when the table’s width changes but "the last column" is always the one you want:
Try it: interactive CHOOSECOLS demo
Tick the columns you want from the orders table above — then flip the notation switch to see the same pick written with negative numbers counted from the end.
The power move: shape other functions’ results
CHOOSECOLS rarely works alone. Its everyday job is trimming the output of other dynamic array functions — FILTER returns whole rows, and CHOOSECOLS keeps only the columns the report needs:
The same pattern shapes SORT, UNIQUE, or an XLOOKUP that returns multiple columns. Before Excel 365 you needed INDEX with array constants to do any of this:
Columns vs rows: CHOOSECOLS picks columns by number; CHOOSEROWS picks rows; FILTER picks rows by criteria. Need "the Amount column for West orders"? FILTER for the rows, CHOOSECOLS for the columns.
Errors & common pitfalls
#VALUE! — column number out of range. Asking for column 5 (or -5) of a four-column array, or using 0, returns #VALUE!. Numbers must be between 1 and the column count, or -1 to minus the column count.
#SPILL! — the landing zone isn’t empty. The result needs as many blank cells as it spills into. Clear whatever blocks the range, or move the formula.
#NAME? — older Excel. CHOOSECOLS needs Excel 365 or Excel 2024+. In older versions use INDEX with an array constant, or restructure the data.
Pitfall: don’t confuse it with CHOOSE. CHOOSE picks one value from a list of arguments; CHOOSECOLS picks columns from one array. The names are cousins, the jobs aren’t.
Pitfall: column numbers are positions in the array, not sheet letters. If your array starts at column C, then col_num 1 means column C, not column A.
Practice workbook
Frequently asked questions
What do negative column numbers do in CHOOSECOLS?
=CHOOSECOLS(data, -1) always returns the final column even when the table grows wider.Which Excel versions have CHOOSECOLS?
Can CHOOSECOLS reorder or duplicate columns?
What's the difference between CHOOSECOLS and FILTER?
How do I pick columns by header name instead of number?
=CHOOSECOLS(A2:D6, XMATCH("Amount", A1:D1)) finds the column number from the header row, so the formula survives column moves.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class