Skip to content

Excel function · Lookup & reference

Excel CHOOSECOLS function

CHOOSECOLS picks the columns whose numbers you give from a range and returns them as a new array, in the order you set. It is available only in Microsoft 365.

In Turkish Excel: SÜTUNSEÇ

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

English Excel=CHOOSECOLS(array, col_num1, [col_num2], ...)
Turkish Excel=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

ArgumentRequiredDescription
arrayRequiredThe range or array to pick columns from.
col_num1RequiredThe position of the first column to return, inside the range; a negative number counts from the end.
col_num2OptionalThe 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.

G2fx=CHOOSECOLS(A2:E5,3,-1)
Take two columns of a table in a new order: example table
ABCDEFGH
1OrderDateCustomerRegionAmountCustomerAmount
2SO-7012-Oct-2026North MarketNorth3,200North Market3,200
3SO-7025-Oct-2026Atlas LtdWest1,850Atlas Ltd1,850
4SO-7039-Oct-2026Blue ElectricWest2,600Blue Electric2,600
5SO-70414-Oct-2026West BuildSouth1,450West Build1,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).

G2fx=CHOOSECOLS(FILTER(A2:E5,D2:D5="West"),3,5)
Only the needed columns of the filtered rows: example table
ABCDEFGH
1OrderDateCustomerRegionAmountWest CustomerAmount
2SO-7012-Oct-2026North MarketNorth3,200Atlas Ltd1,850
3SO-7025-Oct-2026Atlas LtdWest1,850Blue Electric2,600
4SO-7039-Oct-2026Blue ElectricWest2,600
5SO-70414-Oct-2026West BuildSouth1,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

Type this in Nexell

Copy only the customer, date and amount columns of the order table, in that order, to a separate sheet

Convert to table

Bring 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

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free