CHOOSECOLS - Select and reorder explicit columns
Prepare a product/stock export from a wider inventory table without moving or deleting its source columns.
By the end, you can
- Return columns by their positions within the selected array.
- Reorder or repeat columns deliberately.
- Use negative indexes counted from the right.
Before you start: Understand 1-based positions within an array and FILTER's row selection.
In this lesson
Projection is different from filtering or sorting
CHOOSECOLS returns selected columns from an array in the order you request. An inventory export can include product and stock while omitting SKU and unit price, without rearranging the source. It chooses fields, not eligible rows or ranking. Combine it with FILTER when both row eligibility and column selection matter.
Syntax and selectors
=CHOOSECOLS(array, col_num1, [col_num2], ...)Positions are counted within array, starting at 1 on the left. Negative selectors count from the right.
| Argument | Purpose | Example |
|---|---|---|
arrayRequired | The source array or an array returned by another function. | A2:D5 |
col_num1Required | First selected column; -1 means the final column. | 2 |
col_num2, …Optional | Additional positions in the desired output order. Repeats are allowed. | 3 |
A two-field inventory export
Paper| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Product | Stock | Unit price INR | Product | Stock | |
| 2 | P101 | Paper | 12 | ₹250 | Paper | 12 | |
| 3 | P102 | Pens | 48 | ₹15 | Pens | 48 | |
| 4 | P103 | Folders | 20 | ₹40 | Folders | 20 | |
| 5 | P104 | Ink | 0 | ₹900 | Ink | 0 |
=CHOOSECOLS(A2:D5,2,3)Return product then stock for all four records, as a four-row, two-column spill.
- Count source fields within A2:D5: SKU 1, Product 2, Stock 3, Price 4.
- Request positions 2 and 3 in that order.
- Clear F2:G5 and enter the anchor once.
- Check that selecting columns did not silently remove a zero-stock record.
Reordering, non-adjacent fields, and the final column
=CHOOSECOLS(A2:D5,3,2)Selection order determines output order: stock comes before product.
=CHOOSECOLS(A2:D5,1,4)Choose the first and fourth fields while keeping each record's values aligned.
=CHOOSECOLS(A2:D5,-1)-1 selects the price column, which is last within this array.
=CHOOSECOLS(FILTER(A2:D5,C2:C5>0),2,3)FILTER excludes zero-stock Ink; CHOOSECOLS then returns only product and stock.
Selectors, spills, and compatibility
- A selector is a relative position, not a worksheet column letter or header name. Repeating a position repeats that column in the result.
- Zero or an absolute selector beyond the array's width gives #VALUE!. Negative -1 means the last field, not the first field or a descending sort.
- The returned fields keep their source row order unless another function changes it. A blocked spill area gives #SPILL!.
- CHOOSECOLS is available in Microsoft 365 and Excel 2024, not perpetual Excel 2021/2019/2016. Keep the spill anchor outside an Excel Table.
Common mistakes and useful tips
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.