Dynamic arrays function
Excel CHOOSECOLS function
CHOOSECOLS returns the columns you ask for from a range or array, in the order you list them. It is the easy way to keep only a few columns from a FILTER result, or to reorder a table without copying it.
=CHOOSECOLS(array, col_num1, [col_num2], ...)Arguments
array- The range or array to take columns from.
col_num1- The first column to return, counted from the left of array. A negative number counts from the right: -1 is the last column.
col_num2- Optional. More columns, in the order you want them.
CHOOSECOLS examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Name | Dept | Office | |
| 2 | E-101 | Maya Ortiz | Finance | [email protected] | Denver |
| 3 | E-102 | Sam Lee | Sales | [email protected] | Austin |
| 4 | E-103 | Ruth Kahn | Sales | [email protected] | Denver |
| 5 | E-104 | Joel Park | IT | [email protected] | Remote |
| 6 | E-105 | Nia Brooks | Sales | [email protected] | Austin |
Result: spills 5 rows × 2 columns
Maya Ortiz [email protected] Sam Lee [email protected] Ruth Kahn [email protected] Joel Park [email protected] Nia Brooks [email protected] Returns just the names and emails, side by side.
Result: spills 6 rows × 2 columns
Email Name [email protected] Maya Ortiz [email protected] Sam Lee [email protected] Ruth Kahn [email protected] Joel Park [email protected] Nia Brooks Columns come back in the order you list them, so the email column is now first, headers included.
Result: spills 3 rows × 2 columns
Sam Lee Austin Ruth Kahn Denver Nia Brooks Austin Filter to Sales, then keep the name and the last column, the office.
Result: spills 5 rows × 1 column
Picks the column by its header, so the formula still works if someone moves the Email column.
How to use CHOOSECOLS
- Click the top-left cell of an empty area.
- Type =CHOOSECOLS( and select the data.
- Add the column numbers you want, separated by commas, counting from the first column of your selection.
- Close the bracket and press Enter.
Common CHOOSECOLS mistakes
Counting sheet columns
Numbers count from the left edge of array, not from column A. In CHOOSECOLS(C2:E6,1), 1 is column C.
A column number out of range
0, or a number larger than the number of columns, returns #VALUE!. Five columns allow 1 to 5 and -1 to -5.
Hard-coded numbers that drift
If columns get inserted, 4 may no longer be Email. Look it up with XMATCH("Email",A1:E1) instead.
Using it in older Excel
CHOOSECOLS needs Microsoft 365, Excel 2024 or Excel for the web. Older versions show #NAME?.
CHOOSECOLS FAQ
How do I return only certain columns from FILTER?
Wrap FILTER in CHOOSECOLS: =CHOOSECOLS(FILTER(A2:E6,C2:C6="Sales"),2,4) returns the names and emails of the Sales rows.
How do I reorder columns with a formula?
List the columns in the new order: =CHOOSECOLS(A1:E6,2,1,4) puts Name first, then ID, then Email.
What is the difference between CHOOSECOLS and INDEX?
INDEX(A2:E6,0,2) returns one whole column. CHOOSECOLS returns any number of columns in one step, in any order.
Related functions
CHOOSEROWSPicks rows, spillsFILTERKeeps matching rows, spillsXMATCHPosition of a value, modernHSTACKJoins arrays side by side
Updated