Picking columns with CHOOSECOLS
Make a copy of a table with only some of its columns.
CHOOSECOLS takes the table and the numbers of the columns you want, in any order.
Make a mailing list with the Email column first and the Name column second, starting in G1. Type one formula in G1.
Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Department | Phone | Start year | |||||
| 2 | Tariq Li | Sales | tariq.li@example.com | 07700 900290 | 2012 | ||||
| 3 | Nadia Patel | Marketing | nadia.patel@example.com | 07700 900711 | 2017 | ||||
| 4 | Daniel Fischer | Operations | daniel.fischer@example.com | 07700 900957 | 2020 | ||||
| 5 | Quinn Evans | Finance | quinn.evans@example.com | 07700 900531 | 2015 | ||||
| 6 | Jamal Novak | HR | jamal.novak@example.com | 07700 900862 | 2017 | ||||
| 7 | Isla Kowalski | Marketing | isla.kowalski@example.com | 07700 900589 | 2024 | ||||
| 8 | Kofi Patel | Marketing | kofi.patel@example.com | 07700 900827 | 2022 | ||||
| 9 | Yusuf Dubois | Operations | yusuf.dubois@example.com | 07700 900999 | 2021 | ||||
| 10 | |||||||||
| 11 | |||||||||
| 12 | |||||||||
| 13 |
How CHOOSECOLS works
What does
CHOOSECOLSdo?It returns only the columns you ask for, by number, from a range. It is in Excel 365 and Excel 2024 (older versions show #NAME?). The result spills, so one formula makes a whole new table:
=CHOOSECOLS(A1:E9,1, 3) How are the columns numbered?
From the first column of the range, which is 1. A negative number counts from the end: -1 is the last column.
Can it change the order?
Yes. The columns come out in the order you list them, so
=CHOOSECOLS(A1:E9,3,1)puts column 3 first. You can list the same column twice too.When is it useful?
Making a short version of a wide table to share or print, feeding just the right columns to another function such as
SORTorFILTER, or putting columns in the order another system expects. CHOOSEROWS does the same for rows.Is there a way without formulas?
The Table Workspace on this site has a Choose Columns step that reorders and drops columns from a whole file.
Know someone fighting with a spreadsheet? Send them this page.