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:
| SKU | Region | Units | Revenue | Rep |
|---|---|---|---|---|
| P03 | West | 2 | 50 | Lee |
| P01 | East | 1 | 18 | Kim |
| P02 | North | 4 | 72 | Ray |
In a clear area outside the Table, enter:
=CHOOSECOLS(Orders,4,1,2)FormulaThe expected result is three rows by three columns:
50 P03 West
18 P01 East
72 P02 NorthExampleColumn 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)FormulaThe expected result is a single column:
Lee
Kim
RayExampleThe 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.