CHOOSECOLS in Excel: how to use it
CHOOSECOLS shows only the columns of a table that you choose, in the order you want. The result spills over and updates itself. Available in Excel 365 and 2024.
Try the first exercises for freeIn the lesson Excel 365 for advanced users
What does CHOOSECOLS do?
CHOOSECOLS makes a copy of a table with only the columns you name. You type the formula in one cell, and the result spills over to the right and down. If something changes in the table, the copy changes with it.
The columns come in the order of your numbers: with 3,1 the third column comes first. A negative number counts from the right, so -1 is always the last column. CHOOSECOLS works in Excel 365 and Excel 2024.
For rows, there are three sister functions. CHOOSEROWS works the same way, with row numbers. TAKE keeps the first rows, or the last ones with a minus sign, and DROP leaves them out. Together with SORT, that's how you make a top three that updates itself.
When do you use CHOOSECOLS?
- An overview with only the names and the totals from a wide table: =CHOOSECOLS(A1:F50,1,6).
- Putting columns in a different order: =CHOOSECOLS(A1:C20,3,1,2).
- The last column of a table, without counting: =CHOOSECOLS(A1:H20,-1).
- Choosing two columns and sorting them straight away: =SORT(CHOOSECOLS(A2:D20,1,4),2,-1).
- The three highest revenue figures, updating themselves: =TAKE(SORT(A2:C50,3,-1),3).
- A list without the header row: =DROP(A1:C50,1).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Only the columns you name, in that order.
Filled in: =CHOOSECOLS(A1:C6,1,3)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
CHOOSECOLS | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | CHOOSECOLS |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
array | The table or range you choose from. | A1:C6 |
col_num1 | The first column you want to see. Count from 1, or from the right with a minus sign: -1 is the last column. | 1 |
[col_num2] | Optional: another column, and so on. The order of the numbers is the order in the result. | 3 |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
CHOOSECOLS step by step
E1 contains =CHOOSECOLS(A1:C6,1,3). The Region and Revenue columns, without the products. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1, with empty cells to the right and below, and type CHOOSECOLS with a bracket.
=CHOOSECOLS( - Select the whole table, A1 to C6, and type a comma.
=CHOOSECOLS(A1:C6, - Type the numbers of the columns you want to see: 1 for Region, a comma, and 3 for Revenue.
=CHOOSECOLS(A1:C6,1,3 - Close the bracket and press Enter: two columns, without the products.
=CHOOSECOLS(A1:C6,1,3)
More examples
The last column
E1 contains =CHOOSECOLS(A1:C6,-1). -1 counts from the right: the last column, Revenue.
In a different order
E1 contains =CHOOSECOLS(A1:C6,3,1). With 3,1, Revenue comes first and Region after it.
Choosing and sorting
E2 contains =SORT(CHOOSECOLS(A2:C6,1,3),2,-1). The regions with their revenue, the highest first: Leeds with 1,200 is at the top.
The header row and the last row
E1 contains =CHOOSEROWS(A1:C6,1,-1). Row 1 with the headings, and with -1 the last row: York, Mouse, 75.
A top two with TAKE
E2 contains =TAKE(SORT(A2:C6,3,-1),2). SORT puts the highest revenue at the top, and TAKE keeps the first two rows: Leeds with 1,200 and Bristol with 980.
Where it often goes wrong
Practise this in the lesson Excel 365 for advanced users. The first exercises are free.
Frequently asked questions
Can I choose rows too?
Yes. CHOOSEROWS works the same way, with row numbers. TAKE keeps the first or last rows and DROP removes them: =TAKE(SORT(A2:C6,3,-1),3) gives a top three.