CHOOSECOLS function
Quick answer
CHOOSECOLS returns only the columns you name from a range or array, in the order you name them, so 3,1 puts the third column first.
- Syntax
=CHOOSECOLS(array, col_num1, [col_num2], ...)- Most common use
=CHOOSECOLS(A2:D3,1,3)returns Ana (Name and email only)
Negative numbers count from the right. Use it to trim a FILTER or SORT result down to the columns a report needs, or to reorder a table without copying it.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- An array with the chosen columns.
- Google Sheets
- Available in Google Sheets.
Syntax
=CHOOSECOLS(array, col_num1, [col_num2], ...)
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range or array to take columns from. |
col_num1 | Required | The first column to return; negative counts from the right. |
col_num2 | Optional | Optional: more columns. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =CHOOSECOLS(A2:D3,1,3) | Anaspills 2 × 2 | Name and email only |
| F5 | =CHOOSECOLS(A2:D3,-1) | 555-0101spills 2 × 1 | The last column: phone |
| H2 | =CHOOSECOLS(A2:D3,4,1) | 555-0101spills 2 × 2 | Phone first, then name |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Name | Dept | Phone | ||||||
| Ana | Sales | ana@contoso.com | 555-0101 | Ana | ana@contoso.com | 555-0101 | Ana | |
| Raj | IT | raj@contoso.com | 555-0102 | Raj | raj@contoso.com | 555-0102 | Raj | |
| 555-0101 | ||||||||
| 555-0102 |
Practice CHOOSECOLS
Graded instantly, then re-checked on changed data.
Phone list
F2, return just the phone numbers and names from the staff table in A2:D7, with the phone number first.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | A column number is 0 or larger than the number of columns. |
| #SPILL! | Cells where the result would spill aren't empty. |
Tips
- =CHOOSECOLS(FILTER(A2:F100,C2:C100="West"),1,4) filters and keeps just two columns in one step.
Questions
How do I return only certain columns from FILTER?
Wrap it: =CHOOSECOLS(FILTER(A2:F100,C2:C100="West"),1,4).
Can CHOOSECOLS reorder columns?
Yes. The columns come back in the order you list them, so =CHOOSECOLS(A2:C10,3,1) puts column C first.
Answers that use CHOOSECOLS
Updated Checked by running: every formula on this page was run by our formula engine on .