Picking columns with CHOOSECOLS
The staff table has five columns. We only want the emails, then names: columns C and A.
keyboard 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. 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.