Back to functions
Lookup & Reference2026-11-161 related article

CHOOSECOLS Function in Excel

Return selected columns from an array in the order you specify, keeping every source row.

Syntax

CHOOSECOLS(array, col_num1, [col_num2], ...)
Table of Contents

Arguments

array

Required

The source range or array containing the columns to select.

col_num1

Required

The first column position to return. Positive numbers count from the left; negative numbers count from the right.

col_num2

Optional

Additional column positions, in the desired output order. Repeat for further columns.

What it returns

An array containing the requested columns in argument order, with the source row order preserved.

What CHOOSECOLS does

CHOOSECOLS builds a result from the source columns you name by position. You can leave out unwanted columns, put the remaining ones in a different order, or return a column more than once. It leaves the source in place and keeps every source row.

Practical examples

Build a report with revenue first

Create an Excel Table named Orders with these headers and data:

SKURegionUnitsRevenueRep
P03West250Lee
P01East118Kim
P02North472Ray

In a clear area outside the Table, enter:

=CHOOSECOLS(Orders,4,1,2)
Formula

The expected result is three rows by three columns:

50    P03    West
18    P01    East
72    P02    North
Example

Column 4 is Revenue, column 1 is SKU, and column 2 is Region. The Orders reference supplies the Table's data rows, so this formula doesn't return its headers.

Select the rightmost column

Using the same Table, enter this in another clear area:

=CHOOSECOLS(Orders,-1)
Formula

The expected result is a single column:

Lee
Kim
Ray
Example

The position -1 counts from the right, selecting Rep. A position of -2 would select Revenue.

Common mistakes and notes

Column positions belong to the source array

Position 1 means the leftmost column of array, wherever that array sits on the worksheet. A fixed position continues to select that position if you rearrange source columns. Use XMATCH to find header positions when the result should follow names; the header-based report tutorial shows the combined formula.

Zero and out-of-range positions return #VALUE!

The five-column Table above permits positions 1 through 5 and -1 through -5. Neither 0 nor 6 identifies a valid column. Repeating a valid position is allowed and repeats that column in the result.

Selecting columns doesn't filter or sort rows

All three product records remain in their original order. Use a separate filtering or sorting operation if you also need fewer records or a different row order. Keep the spilled result outside an Excel Table with enough empty cells for all its rows and columns.

Check both functions in a combined formula

CHOOSECOLS is listed for Microsoft 365 and Excel 2024, including their Mac editions. XMATCH is also available in Excel 2021, but having XMATCH alone doesn't make a CHOOSECOLS formula available there.

Related functions

Related articles

Deep dives, troubleshooting guides, and practical examples that use CHOOSECOLS.

Official documentation