# CHOOSECOLS function in Excel

> 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.

Source: https://spreadsheetpractice.com/functions/choosecols · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** 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)
- **Available in:** Excel 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** An array with the chosen columns.
- **Google Sheets:** Available in Google Sheets.

## What it does

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. 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.

## 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 was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | `=CHOOSECOLS(A2:D3,1,3)` | Ana (spills 2 × 2) | Name and email only |
| F5 | `=CHOOSECOLS(A2:D3,-1)` | 555-0101 (spills 2 × 1) | The last column: phone |
| H2 | `=CHOOSECOLS(A2:D3,4,1)` | 555-0101 (spills 2 × 2) | Phone first, then name |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Dept | Email | Phone |  |  |  |  |  |
| 2 | Ana | Sales | ana@contoso.com | 555-0101 |  | Ana | ana@contoso.com | 555-0101 | Ana |
| 3 | Raj | IT | raj@contoso.com | 555-0102 |  | Raj | raj@contoso.com | 555-0102 | Raj |
| 4 |  |  |  |  |  |  |  |  |  |
| 5 |  |  |  |  |  | 555-0101 |  |  |  |
| 6 |  |  |  |  |  | 555-0102 |  |  |  |

## 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.

## Related functions

- [CHOOSEROWS](https://spreadsheetpractice.com/functions/chooserows): CHOOSEROWS returns the rows you name from a range or array: 1 is the first row and -1 the last, so CHOOSEROWS(range,-1) always gives the latest entry.
- [FILTER](https://spreadsheetpractice.com/functions/filter): FILTER returns only the rows of a range that pass a test, such as every order from the West region.
- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.

## 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.

## Practice

A graded CHOOSECOLS exercise ("Phone list") is on [the CHOOSECOLS page](https://spreadsheetpractice.com/functions/choosecols). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.