What it does
The first argument is the table and the rest are the positions of the columns to return: =CHOOSECOLS(A2:E8,1,4) puts the first and fourth columns side by side. You can list the numbers in any order and even pick a column twice; a negative number counts from the end (-1 is the last column).
On its own it gives a trimmed copy of a table; it really pays off with other dynamic array functions. FILTER returns every column of the matching rows, and CHOOSECOLS keeps only the ones the report needs; add SORT for a sorted summary.
To pick rows, use its partner CHOOSEROWS; to take a number of columns from the start or end, use TAKE. Outside Microsoft 365, columns are taken one by one with INDEX.
Syntax
=CHOOSECOLS(array, col_num1, [col_num2], ...)=SÜTUNSEÇ(dizi; sütun_no1; [sütun_no2]; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range or array to pick columns from. |
col_num1 | Required | The position of the first column to return, inside the range; a negative number counts from the end. |
col_num2 | Optional | The position of the next column to return; add as many as you need the same way. |
Examples
1Take two columns of a table in a new order
The order table has five columns. 3 picks the customer column and -1 the last column, the amount; the result spills from G2 across two columns and four rows.
=CHOOSECOLS(A2:E5,3,-1)| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Region | Amount | Customer | Amount | |
| 2 | SO-701 | 2-Oct-2026 | North Market | North | 3,200 | North Market | 3,200 | |
| 3 | SO-702 | 5-Oct-2026 | Atlas Ltd | West | 1,850 | Atlas Ltd | 1,850 | |
| 4 | SO-703 | 9-Oct-2026 | Blue Electric | West | 2,600 | Blue Electric | 2,600 | |
| 5 | SO-704 | 14-Oct-2026 | West Build | South | 1,450 | West Build | 1,450 |
- G2 (the result spills into G2:H5)
=CHOOSECOLS(A2:E5,3,-1)
The first row shows North Market and 3,200. If you later widen the range to take in a new column, -1 will pick that column instead; use 5 to stay on the amount.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Only the needed columns of the filtered rows
FILTER returns the West orders with all five columns; CHOOSECOLS keeps only the customer (3) and the amount (5).
=CHOOSECOLS(FILTER(A2:E5,D2:D5="West"),3,5)| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Region | Amount | West Customer | Amount | |
| 2 | SO-701 | 2-Oct-2026 | North Market | North | 3,200 | Atlas Ltd | 1,850 | |
| 3 | SO-702 | 5-Oct-2026 | Atlas Ltd | West | 1,850 | Blue Electric | 2,600 | |
| 4 | SO-703 | 9-Oct-2026 | Blue Electric | West | 2,600 | |||
| 5 | SO-704 | 14-Oct-2026 | West Build | South | 1,450 |
- G2 (the result spills into G2:H3)
=CHOOSECOLS(FILTER(A2:E5,D2:D5="West"),3,5)
Two West orders remain: Atlas Ltd (1,850) and Blue Electric (2,600). The short report updates by itself as the source table changes.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: One of the column numbers is 0 or larger than the number of columns, e.g. 6 in a five-column range.
Fix: Use numbers that fall inside the range.
- #SPILL!
Cause: A cell in the spill area is not empty.
Fix: Clear the cells below and to the right of the formula.
- #NAME?
Cause: The file was opened in a version of Excel other than Microsoft 365.
Fix: In those versions, take the columns with INDEX.
Good to know
- If you find the column number from the header with MATCH, the right column still comes back when columns are inserted:
=CHOOSECOLS(A2:E8,MATCH("Amount",A1:E1,0)). - You do not need to know how many columns there are to get the last one; use -1.
In Nexell
Get Nexell to do it from a sentence
Copy only the customer, date and amount columns of the order table, in that order, to a separate sheet
Convert to tableBring your table into the tool and type this. Nexell builds the new table from the chosen columns and shows it in the preview; once you approve, you download the file as .xlsx.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
Can CHOOSECOLS change the order of columns?
Yes. List the numbers in the order you want: =CHOOSECOLS(A2:D8,4,1) puts the fourth column first and the first column second. The source table is not changed.
Related functions
- CHOOSEROWSSATIRSEÇ · Returns chosen rows of an array.
- TAKEAL · Takes rows or columns from the start or end of an array.
- FILTERFİLTRE · Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.
- INDEXİNDİS · Returns the value at a given row and column of a range.
- SORTSIRALA · Sorts a range (1 ascending, -1 descending).
- VSTACKDÜŞEYYIĞ · Stacks arrays vertically.