CHOOSECOLS

Return specific columns from an array or range with Excel's CHOOSECOLS function.

Lookup & Reference
|
Excel 365 only
|
Google Sheets Supported

Spreadsheet editor

Syntax

=CHOOSECOLS(array, col_num1, [col_num2], ...) Returns: Array

Arguments

Argument Required Description
array Yes The source range or array that contains the columns you want to return.
col_num1 Yes The position of the first column to return. Count from 1 on the left, or use a negative number to count from the right.
col_num2 No Additional column positions to return, listed in the order you want them.

About

CHOOSECOLS pulls one or more columns out of an array or range and returns them as a new dynamic array. You list the column positions you want, in the order you want them, and Excel spills the result into the cells below and to the right. This is handy when your source table has more fields than your report needs, or when you want to reorder columns without touching the original data.

Use CHOOSECOLS when you only need a few columns from a wide table, such as showing just the name and total from a sales export. You can also reorder columns on the fly by listing positions in a different order, like 3, 1, 2. Negative numbers count from the right, so -1 grabs the last column, which is useful when the number of columns can change.

CHOOSECOLS works well with other dynamic array functions. Pair it with FILTER to narrow rows first, then keep only the columns you want, or combine it with SORT to order rows before trimming fields. To grab rows by position instead of columns, see TAKE.

Examples

Grab the last column from the right

See how a negative number counts from the right, so -1 always returns the last column. Add a column before Total and watch the formula still land on the rightmost one.

Spreadsheet editor

Asking for a column that does not exist

Watch what happens when you ask for column 4 in a three-column table: you get a #VALUE! error. Change the 4 to 1, 2, or 3 to pull a column that really exists.

Spreadsheet editor

Filter rows, then keep two columns

Use CHOOSECOLS with FILTER to build a focused report. FILTER keeps the West rows, then CHOOSECOLS keeps just the Rep and Sales columns. Edit a region and the list rebuilds itself.

Spreadsheet editor

Watch out for

⚠

Counting from worksheet letters

People assume col_num matches column letters on the sheet, so a table starting at column D feels like it should use 4.

→ Column numbers are positions inside the array you pass in, not sheet columns. If your array starts at column D, its first column is still 1 inside CHOOSECOLS.

⚠

Asking for a column that does not exist

Using a number larger than the column count, or 0, returns a #VALUE! error and nothing spills.

→ Keep each col_num between 1 and the number of columns in the array (or use negative numbers within the same range). Use COLUMNS to check how many columns your array has.

⚠

Output blocked by existing data

CHOOSECOLS spills its result, so if a cell in the output area is occupied you get a #SPILL! error.

→ Clear the cells below and to the right of the formula so the array has room to expand into empty space.

Tips & notes

CHOOSECOLS is a Microsoft 365 function and is not available in Excel 2021 or earlier. You can repeat a column number to return the same column more than once.

Common questions

Do CHOOSECOLS column numbers use worksheet letters?

No. The numbers are positions inside the array you pass into CHOOSECOLS. If your array starts at column D on the sheet, its first column is still 1 inside the function.

What does a negative column number do?

A negative number counts from the right. Use -1 for the last column, -2 for the second to last, and so on. This is handy when the number of columns can change.

When should I use CHOOSECOLS instead of INDEX?

Use CHOOSECOLS to return one or more full columns from an array in any order. INDEX is better when you need a specific row and column intersection or a custom extraction pattern.