TL;DR
- CHOOSECOLS picks columns from an array by position and can reorder or repeat them. CHOOSEROWS does the same for rows.
- Syntax: =CHOOSECOLS(array, col_num1, [col_num2], ...). Negative numbers count from the right edge; zero or an out-of-range number returns #VALUE!.
- Wrap a FILTER inside CHOOSECOLS to return only some rows and only some columns in one formula.
- Both functions are available in Microsoft 365 and Excel 2024, including the Mac editions, and they spill into neighboring cells.
- The fastest way to learn them is to type the formulas yourself and see the result immediately.
Table of Contents
What CHOOSECOLS and CHOOSEROWS do
Imagine a sales export with twelve columns, and a report that needs four of them in a different order. The old fix was to copy the columns, paste them elsewhere, rearrange them, and hope nobody changed the source. Or you wrote a separate INDEX or VLOOKUP for every column.
CHOOSECOLS replaces all of that with one formula. It builds a new array from the columns you list, and because it is a formula, it updates when the source data changes. Microsoft's documentation describes it as returning specified columns from an array, and the same page lists CHOOSEROWS alongside it.
The pair belongs to the family of functions that reshape data without touching the original. They are good for:
- Building a clean report block from a wide export.
- Reordering columns for a presentation without cut and paste.
- Repeating a key column, such as an ID, next to the data it describes.
- Pulling the last column or last few rows of a growing list.
CHOOSECOLS syntax, argument by argument
Microsoft documents the syntax as =CHOOSECOLS(array,col_num1,[col_num2],…). Here is what each piece means:
| Argument | Required? | What it is |
| array | Yes | The range or array that holds the columns you want to return |
| col_num1 | Yes | The position of the first column to return |
| col_num2, ... | No | Positions of any additional columns, separated by commas |
The key idea is that column numbers are positions inside the array, not worksheet column letters. If your array is
C2:H100, then column 1 is column C, column 2 is D, and so on. This trips up almost everyone the first time. If you pass
D thinking of the worksheet column, Excel has no idea what you mean. You want 2.
A first example
Suppose the range A2:E7 holds a small table with five columns: Region, Rep, Product, Units and Revenue. This formula returns Region, Product and Revenue:
=CHOOSECOLS(A2:E7,1,3,5)
Type it in one cell, press Enter, and the results fill the cells to the right and below. You do not copy it down or across. That automatic filling is called spilling, and we come back to it below.
Reordering and repeating columns
The order of your numbers is the order of the output. Microsoft's own worked example, =CHOOSECOLS(A2:E7,1,3,5,1), returns columns 1, 3 and 5, and then column 1 again. That shows two things: the output order does not have to follow the source order, and duplicates are allowed.
With our sales table, =CHOOSECOLS(A2:E7,5,1,2) puts Revenue first, then Region, then Rep. You can also repeat a column, for example =CHOOSECOLS(A2:E7,2,2) to return the Rep column twice, which is occasionally useful as a key when you later combine arrays.
Using an array constant
You can supply several indexes in curly braces instead of separate arguments. Microsoft shows this for CHOOSEROWS in =CHOOSEROWS(A2:B7,{1,3},5,1), where {1,3} supplies two row numbers at once. The same pattern works for columns, so =CHOOSECOLS(A2:E7,{1,3,5}) is equivalent to =CHOOSECOLS(A2:E7,1,3,5). Use braces when the list of positions is long and you want it to read as one unit.
Negative numbers count from the right
A negative column number counts backward from the last column of the array. -1 is the last column, -2 is the one before it, and so on. This is the feature that makes CHOOSECOLS handy for tables that grow wider, because you do not need to know how many columns there are to grab the last one.
Microsoft's example, =CHOOSECOLS(A2:D7,-1,-2), returns column 4 before column 3 in a four-column range. In our five-column sales table, =CHOOSECOLS(A2:E7,-1) returns Revenue only, and =CHOOSECOLS(A2:E7,1,-1) returns the first and last columns side by side.
You can mix positive and negative numbers freely. One rule is firm: an index of zero, or one larger in absolute value than the number of columns, returns #VALUE!. There is no column 0, so if you were thinking in zero-based terms from another tool, add one.
CHOOSEROWS: the same idea for rows
CHOOSEROWS takes the same shape: =CHOOSEROWS(array,row_num1,[row_num2],...). Microsoft's example =CHOOSEROWS(A2:B7,1,3,5,1) returns rows 1, 3 and 5 and then row 1 again, while =CHOOSEROWS(A2:B7,-1,-2) returns the last two rows in reverse order.
On a running log, =CHOOSEROWS(A2:E200,-1) gives you the most recent entry, provided your range ends with the last row of data. If blank rows sit at the bottom of the range, "last" means the last blank one, so trim the range or use a table reference.
A useful pattern is to nest the two. To get row 3 and row 5, only the Region and Revenue columns:
=CHOOSECOLS(CHOOSEROWS(A2:E7,3,5),1,5)
Read it from the inside out: first pick the rows, then pick the columns from what is left.
How it differs from TAKE and INDEX
TAKE grabs a block from the start or end of an array. According to How-To Geek's walkthrough, TAKE extracts the first or last columns or rows, or a single one from elsewhere in a table, while CHOOSECOLS and CHOOSEROWS pull any number of columns or rows in any order from anywhere in the data. So: contiguous chunk from an edge, use TAKE. Hand-picked, scattered or reordered, use CHOOSECOLS.
INDEX can also return a whole column when you set the row argument to 0, but it returns one at a time unless you build something more elaborate. If you want a refresher on that older tool, see our INDEX function examples and practice.
Combining CHOOSECOLS with FILTER
This is the pairing that most readers searching this function are really after. FILTER returns the rows that meet a condition, but it returns every column of the range. CHOOSECOLS trims that result to the columns you care about.
The structure is:
=CHOOSECOLS(FILTER(array, condition), col_num1, col_num2, ...)
With the sales table in A2:E7, to list Region, Rep and Revenue for rows where Units is over 100:
=CHOOSECOLS(FILTER(A2:E7,D2:D7>100),1,2,5)
FILTER runs first and produces a block of all matching rows with all five columns. CHOOSECOLS then keeps columns 1, 2 and 5 of that block. Note that the column numbers refer to the filtered array, which has the same columns as the original, so they line up with the source range.
A real-world version of this approach appears in a CQG post on a screening dashboard, where FILTER screens a list of stocks against conditions and returns the matches. The same post wraps the filter in IFERROR so an empty result displays as blank instead of an error.
Handle the "no matches" case
When nothing meets the condition, FILTER returns a #CALC! error. FILTER has a built-in third argument for this, which keeps the formula shorter than IFERROR:
=CHOOSECOLS(FILTER(A2:E7,D2:D7>100,"None"),1,2,5)
Because CHOOSECOLS then receives a single text value rather than a table, it may still complain about column numbers that do not exist in a one-cell result. If you see #VALUE! only when the filter is empty, wrap the whole formula instead:
=IFERROR(CHOOSECOLS(FILTER(A2:E7,D2:D7>100),1,2,5),"None")
Sort the result too
You can add SORT around the outside to rank the output. To list the same matches by revenue, highest first, sort on the result's second returned column or the third, depending on what you kept. Our SORT function practice exercise lets you try the sort argument on its own before layering it on.
Filter on columns you do not return
A quiet strength of this combo is that the condition does not have to involve the columns you display. In the example above, the filter looks at Units, which is column 4, but the output shows only columns 1, 2 and 5. The condition is evaluated against the full source, and only then is the output narrowed.
Which Excel versions have CHOOSECOLS
The functions are not in older, perpetual versions such as Excel 2021 or 2019. Microsoft's US documentation applies to Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac. If a coworker opens your workbook in an older version, the cells will show an error or a cached value instead of a working formula.
If your file has to travel to people on older versions, consider pasting the results as values in the final copy, or use INDEX with an array of column numbers as a fallback. For a broader overview of which functions to learn first, our Excel functions cheat sheet is a handy reference.
Spilling: why the results fill many cells
CHOOSECOLS and CHOOSEROWS return arrays, so they rely on dynamic array behavior. Microsoft explains that these formulas return multiple values to neighboring cells automatically, which it calls spilling. You enter the formula in the top-left cell, and Excel sizes the output range for you. Only that top-left cell is editable. If you click one of the other cells in the spill, you will see the formula grayed out.
Three practical consequences:
- Leave room. The cells below and to the right of your formula must be empty. If they are not, Excel shows #SPILL!. Clear the obstruction, and the result appears.
- Reference the whole spill. To point at the output from another formula, use the spill reference: type the top-left cell address followed by a hash sign, such as G2#. It grows and shrinks with the result.
- Do not drag the formula. Copying it down creates overlapping spills and more #SPILL! errors.
Common errors and how to fix them
| Symptom | Likely cause | Fix |
| #VALUE! | A column number is 0, or its absolute value is larger than the number of columns in the array | Count the columns in the array you pass in. Remember that after a FILTER, the array still has all its original columns |
| #SPILL! | Something is in the way of the output range, or the range is inside a table | Clear the cells below and to the right. Dynamic arrays cannot spill inside a formatted Excel table, so place the formula outside it |
| #NAME? | Your Excel version does not have the function, or the name is misspelled | Check the version against the list above |
| #CALC! after FILTER | Filter found no rows | Use FILTER's if_empty argument or wrap in IFERROR |
| Wrong columns come back | You used worksheet column positions instead of positions in the array | Recount from 1 starting at the first column of your array |
| Headers missing | The array started below the header row | Include the header row in the array, or add headers with VSTACK or type them above |
A habit that saves time: build the formula in stages. Start with the FILTER alone, check the rows. Then add CHOOSECOLS and the column numbers. When something breaks, you know which layer did it.
Worked scenario: a monthly manager report
Say you export a transaction list with these columns: Date, Invoice, Customer, Category, Amount, Status, Owner. The manager wants unpaid items only, showing Customer, Amount and Owner, with Owner first.
- Put the export in A2:G500, with Status in column 6.
- Filter for unpaid: FILTER(A2:G500,F2:F500="Unpaid").
- Pick and order columns: Owner is 7, Customer is 3, Amount is 5.
- Combine: =CHOOSECOLS(FILTER(A2:G500,F2:F500="Unpaid"),7,3,5).
- Add a safety net: =IFERROR(CHOOSECOLS(FILTER(A2:G500,F2:F500="Unpaid"),7,3,5),"Nothing outstanding").
Next month, paste the new export over the old one. The report rebuilds itself. If the export gains a column in the middle, the column numbers shift, which is the one maintenance risk of this approach. Using a negative number for the last column, or keeping the export layout stable, avoids it.
If you want the condition to be a choice rather than typed text, pair it with a pick list: our guide on creating a drop-down list in Excel shows how, and then "Unpaid" in the formula becomes a cell reference.
Our perspective: learn this by typing it
We built LogicExcel around one observation: people can watch a video on dynamic arrays, nod along, and still freeze when they face an empty cell. The gap is practice. With functions like CHOOSECOLS, the logic is easy to state and surprisingly easy to get wrong in small ways: an off-by-one column number, a negative index in the wrong direction, a spill blocked by one stray cell.
That is why we think the right way to learn them is to write the formula yourself, see what it returns, and read an explanation when it fails. Mistakes teach more than demonstrations do, provided the feedback is immediate. Our approach is practice in an Excel-like environment with instant feedback on what you typed, and no account to create first.
Our advice for this particular function: first do five minutes of plain CHOOSECOLS on a small table, including -1 and a repeated column. Then add FILTER. Then add SORT. Resist the urge to write the three-layer formula on day one. Each layer you understand separately is a layer you can debug later, and debugging is most of real spreadsheet work.
Also keep in mind that the newest functions are not always the right choice. If your audience uses older Excel, an INDEX approach is more portable. Knowing both is what makes you useful on a team.
> — Thomas Houza
Practice these functions
Reading about array formulas only goes so far. On LogicExcel you can work through free Excel exercises where you type the formulas yourself and get instant feedback, and every mistake is explained. If you are newer to spreadsheets, start with the beginner exercises, and when you are ready for more depth, our Excel courses build from the basics up.
If you are preparing for a job test where lookup and reference functions show up, the free Excel test for interviews and assessments is a good place to rehearse under realistic conditions.
Related functions worth knowing
CHOOSECOLS rarely works alone. These neighbors cover the jobs it does not:
- FILTER returns rows that meet a condition, as shown above.
- SORT orders an array. Combine it with CHOOSECOLS for ranked reports.
- INDEX returns values by position and works in every version. See our INDEX practice page.
- VLOOKUP looks up one value and returns something from the same row. Our VLOOKUP examples cover the classic version, which CHOOSECOLS can feed with a rearranged table.
- OFFSET builds a range of a given size from a starting point, a different approach to flexible ranges. See the OFFSET function.
- TAKE pulls a block from the start or end of an array.
If you plan to remove columns from the source itself rather than just from the report, the
insert and delete shortcuts are worth memorizing, though bear in mind that deleting a column inside the source range changes your CHOOSECOLS numbering.
FAQ
Does CHOOSECOLS work in Excel 2021 or 2019?
No. Microsoft lists it for Excel for Microsoft 365 and Excel 2024, including their Mac editions. In older versions you will see #NAME?. INDEX is the usual fallback.
Can I use column letters instead of numbers in CHOOSECOLS?
No, the arguments are positions within the array, starting at 1. If your array begins in column C, then column C is position 1. To avoid counting, you can use MATCH on the header row to find each position, as in =CHOOSECOLS(A1:G100,MATCH("Amount",A1:G1,0)).
What does CHOOSEROWS do?
It returns the rows you name from an array, in the order you name them, with the same positive and negative numbering as CHOOSECOLS. For example, =CHOOSEROWS(A2:B7,-1,-2) returns the last two rows in reverse order, as Microsoft shows.
Why does my CHOOSECOLS formula return #SPILL!?
The cells where the result needs to go are not empty, or the formula sits inside a formatted table. Clear the blocking cells or move the formula outside the table. Microsoft notes that Excel sizes the output range automatically from the top-left cell.
Can I use CHOOSECOLS with FILTER?
Yes, and it is the most common use. Put the FILTER inside, for example =CHOOSECOLS(FILTER(A2:E7,D2:D7>100),1,2,5), so that the filter chooses rows and CHOOSECOLS chooses columns. Wrap it in IFERROR if the filter might return nothing.
Sources
Recommended
