Excel like a finance pro.
CHOOSEROWS & CHOOSECOLS
Dynamic arrays (Microsoft 365)Pull specific columns out of an export, in the order your template wants them.
Difficulty
1What is it?
An export arrives with twenty columns in the system's order; your template needs four of them in a different order. CHOOSECOLS takes exactly the columns you name, in the sequence you name them, in one formula — the reshaping step most people do by hand every month with copy, paste and drag. CHOOSEROWS does the same for rows. Both accept negative indexes to count from the end, and both accept the same index twice if you genuinely want a column repeated.
2What it looks like
CHOOSECOLS(array, col_num1, [col_num2], …) · CHOOSEROWS(array, row_num1, …)- array
- The range to pick from.
- col_num / row_num
- Which ones, in the order you want them out. Negative counts from the end: -1 is the last column.
3When you use it
- Cut a 20-column export down to the four your template uses, reordered.
- Grab the first and last columns of a growing monthly table.
- Reorder a range without dragging columns around.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
The export's order is the system's. Yours is the one the template needs.
=CHOOSECOLS(A1:E3, 3, 5, 1)Acme 4,200 Sep 3| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Ref | Customer | Dept | Amount | Result |
| 2 | Sep 3 | INV-1041 | Acme | Sales | 4,200 | Acme 4,200 Sep 3 |
| 3 | Sep 4 | INV-1042 | Globex | Marketing | 9,800 | Globex 9,800 Sep 4 |
| 4 | They come out in the order you LISTED them — that's the whole point. | |||||
| 5 | Positional, so a new column in the export breaks it. XMATCH on the header finds it by name instead. |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#VALUE!An index is 0, or larger than the array has.Fix: Indexes are 1-based. Use a negative to count from the end rather than guessing the width.
Columns come out in source orderThey come out in the order you LISTED them — so this means the list was in source order.Fix: Reorder the arguments. That's the whole point of the function.
#SPILL!The output is blocked.Fix: Clear the destination region.
Breaks when the export gains a columnPositional indexes are positional.Fix: Use XMATCH on the header to find the column by NAME: CHOOSECOLS(data, XMATCH("Amount", headers)).
6Better functions & alternatives
- INDEX with an array of column numbers — Works everywhere, reads terribly.
- Power Query — Better when the reshape is a monthly ritual — it remembers the steps and refreshes. See the tip.
Want CHOOSEROWS & CHOOSECOLS already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.