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
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
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The range or array to take columns from. |
| col_num1 | Yes | The position of the first column to return. Negative numbers count from the right (-1 = last column). |
| col_num2, … | Optional | More column positions. They're returned in the order you list them. |
Examples
All examples use this sales table in A1:E4:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Q1 | Q2 | Q3 |
| 2 | Ana | East | 4,200 | 4,650 | 5,100 |
| 3 | Ben | West | 3,150 | 3,400 | 2,980 |
| 4 | Cara | East | 6,800 | 6,200 | 7,050 |
Example 1 — Pick two columns
We only want each rep's name and their Q3 number (columns 1 and 5):
| G | H | |
|---|---|---|
| 1 | Rep | Q3 |
| 2 | Ana | 5,100 |
| 3 | Ben | 2,980 |
| 4 | Cara | 7,050 |
Example 2 — Reorder columns
Columns come back in the order you list them, so this flips Region in front of Rep:
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:
Example 4 — Combine with FILTER
Show only East reps, and only their name and Q1–Q2 sales:
- FILTER(A2:E4, B2:B4="East") keeps the rows for Ana and Cara.
- CHOOSECOLS(…, 1, 3, 4) keeps Rep, Q1 and Q2 from those rows.
- 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.

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.