Imagine you have a dataset with a dozen columns, but you only need five of them, and they’re scattered across the sheet like breadcrumbs. You might hide a few columns, drag others into place, or even copy and paste the five columns into a new tab just to focus on them there.
You can go through all of that effort, or you can use a single formula to pull exactly the columns you need in the order you want, without any extra messy Excel sheet cleanup. Since I mastered CHOOSECOLS, I’ve stuck with that approach.
Precise column selection
A clean way to pick and reorder columns in one step
Screenshot by Ada
CHOOSECOLS lets you extract specific columns from a data range and return them in whatever order you need, even if those columns are not sitting next to each other in your dataset. The syntax is easy to remember:
=CHOOSECOLS(array, col_num1, [col_num2], …)
The array is your source data range, while col_num1 is the index of the first column you want to pull and is required. From there, you can add as many column numbers as you like as optional arguments to return exactly what you need.
One of the most useful aspects of this function is its flexible indexing. You don’t have to count strictly from the left. You can use positive numbers to reference columns from left to right, or negative numbers to count backwards from the right. In the formula below, 1 returns the first column, 3 returns the third column from the left, and -1 returns the last column in your array:
=CHOOSECOLS(A1:N100, 1, 3, -1)
The old VLOOKUP workaround
Smart workaround, terrible user experience
Screenshot by Ada
Before CHOOSECOLS arrived in Microsoft 365, there was another workaround to pull multiple, specific columns at once: using curly brackets inside a VLOOKUP. If you’ve been using Excel for a while, you might have seen this trick in a tutorial or two. It looked something like this:
=VLOOKUP(897751939, G2:N100, {1,3,8}, FALSE)
Because the order IDs are in the seventh column of my dataset, I have to start my lookup range in column G. The curly braces create a static array, which forces VLOOKUP to return the 1st, 3rd, and 8th columns relative to column G (G is 1, I is 3, and N is 8) all at once. For its time, this trick was impressive, but it’s never been intuitive. You had to know it existed, define the correct lookup range, understand how the braces worked to pass the right numbers, and remember that it was static, meaning it wouldn’t adapt if the structure changed.
CHOOSECOLS replaces all of that with a function designed specifically for column selection. If my full range is A1:N100, I can extract columns 1, 3, and 14 like this:
=CHOOSECOLS(A1:N100, -1, 1, 9)
It’s immediately clear that I’m selecting columns 1, 9, and 14, and I can reorder them however I like.
Related
5 Excel functions that are officially too old in 2026 (use the modern alternatives instead)
Don’t let Excel move on without you.
Pairing CHOOSECOLS with modern functions
Take full control over your output
Even if you’ve already moved from VLOOKUP to XLOOKUP or FILTER (and you should), CHOOSECOLS still plays a valuable role alongside them. These newer functions are powerful, but each has a structural limitation that CHOOSECOLS addresses.
Take FILTER, for example. It does an excellent job of isolating rows that meet specific criteria, but on its own, it can’t selectively return non-contiguous columns. You get the rows you need, but you’re stuck with all the columns in the source range. When I wrap CHOOSECOLS around a FILTER result, I can narrow that output to only the columns I care about:
=CHOOSECOLS(FILTER(A2:N100, C2:C100=”Household”), -1, 1, 9)
This formula spills the rows of household items while returning just the three columns I want, in the exact order I choose.
XLOOKUP has a similar limitation. It can return a continuous block of columns from a match, but it struggles when you need specific non-adjacent columns or want to repeat a column in your output. That’s where CHOOSECOLS works as a wrapper, giving me precise control over what gets returned without complicating the XLOOKUP logic. For example, I can use it to find the row for an order ID while only displaying the region and total revenue:
=CHOOSECOLS(XLOOKUP(897751939, G2:G100, A2:N100), 1, 14)
Together, these functions form a powerful trio for clean, flexible data retrieval.
Spilling done right
No dragging and no resizing
Screenshot by Ada
CHOOSECOLS fits naturally into the modern Excel environment, especially in Microsoft Excel 2024 and Microsoft 365, where dynamic array formulas are a core feature. If you’ve worked with functions like FILTER or SORT, you’ll already recognize how spilling works: you enter a formula once, and Excel automatically fills as many adjacent cells as the result requires.
That automatic spilling is another benefit of CHOOSECOLS. I write a single formula, and the result appears exactly as I need it, with the right columns and the right number of rows, without any dragging or manual resizing.
CHOOSECOLS is also strict in a helpful way when it comes to errors. If I pass a column index of 0 or a value that exceeds the total number of columns in the array, the function returns a #VALUE error, which is a common Excel error, instead of pulling in incorrect data. That built-in guardrail makes it easier to trust the output and catch mistakes early.
Related
The Excel functions I use most (and why they’re so useful)
I use these four excel functions every time I have to handle messy spreadsheets.
Let Excel do the rearranging
For the often frustrating task of wrangling column layouts, there’s hardly a better function than CHOOSECOLS. With it, I can leave behind most of the manual reshuffling, hidden columns, and messy copy-paste work that used to slow everything down.
When you combine it with XLOOKUP, FILTER, and dynamic arrays, you can streamline how you search and shape your data so that you only see the columns you need, in the exact order you want.
