CHOOSECOLS Function

Returns only the columns you pick from a range or array.

Category

Lookup & Reference

Available in

Microsoft 365, Excel 2024, Excel for the web

Returns

A spilled array of columns

Taught hands-on in Level 5: Expert Techniques & Workbook Architecture · Modern Functions module

Overview

CHOOSECOLS takes a table and gives you back just the columns you ask for, in the order you ask for them. Columns are picked by position: 1 is the first column of your range, 2 the second, and so on.

It's a dynamic array function, so the result "spills" into as many cells as it needs. Change the source data and the result updates instantly — no copying or pasting.

Syntax

=CHOOSECOLS(array, col_num1, [col_num2], …)

Arguments

ArgumentRequired?What it does
arrayYesThe range or array to take columns from.
col_num1YesThe position of the first column to return. Negative numbers count from the right (-1 = last column).
col_num2, …OptionalMore column positions. They're returned in the order you list them.

Examples

All examples use this sales table in A1:E4:

ABCDE
1RepRegionQ1Q2Q3
2AnaEast4,2004,6505,100
3BenWest3,1503,4002,980
4CaraEast6,8006,2007,050

Example 1 — Pick two columns

We only want each rep's name and their Q3 number (columns 1 and 5):

G1fx=CHOOSECOLS(A1:E4, 1, 5)
GH
1RepQ3
2Ana5,100
3Ben2,980
4Cara7,050
One formula in G1 spills the whole result.
=CHOOSECOLS(A1:E4, 1, 5) Explain

Example 2 — Reorder columns

Columns come back in the order you list them, so this flips Region in front of Rep:

=CHOOSECOLS(A1:E4, 2, 1) Explain

Result: a two-column table starting Region | Rep, then East | Ana, and so on.

Example 3 — The last column, wherever it is

Use a negative number to count from the right. This always returns the most recent quarter, even after you add a Q4 column:

=CHOOSECOLS(A1:E4, 1, -1) Explain
Point the formula at an Excel Table (e.g. CHOOSECOLS(Sales, 1, -1)) and new columns are picked up automatically.

Example 4 — Combine with FILTER

Show only East reps, and only their name and Q1–Q2 sales:

=CHOOSECOLS(FILTER(A2:E4, B2:B4="East"), 1, 3, 4) Explain
  1. FILTER(A2:E4, B2:B4="East") keeps the rows for Ana and Cara.
  2. CHOOSECOLS(…, 1, 3, 4) keeps Rep, Q1 and Q2 from those rows.
  3. Result: Ana | 4,200 | 4,650 and Cara | 6,800 | 6,200.

Common mistakes

  • #VALUE! errors. A column number is 0, or larger than the number of columns in the array (e.g. 6 for a 5-column range).
  • #SPILL! errors. Something is already in the cells where the result wants to go. Clear them and the result appears.
  • Using sheet column letters. 3 means the 3rd column of your range, not column C. For C1:F10, 1 is column C.
  • #NAME? errors. Your version of Excel doesn't have CHOOSECOLS yet — it needs Microsoft 365 or Excel 2024.

Notes

  • You can repeat a column: =CHOOSECOLS(A1:E4, 1, 1) returns the Rep column twice.
  • Pass an array of positions to pick many columns at once: =CHOOSECOLS(A1:E4, {1,3,5}).
  • Related functions: CHOOSEROWS, TAKE, DROP, FILTER, INDEX, CHOOSE.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.