Reshape data faster with Excel CHOOSECOLS and CHOOSEROWS

The first time you paste a wide export into Excel, you hit the same problem twice: half the columns are junk, and the ones you want are in the wrong order. The classic fix is to write an INDEX formula with an array constant, or worse, copy-paste-rearrange by hand. CHOOSECOLS and CHOOSEROWS make the same reshape a single call. They spill, they take negative indices, and they chain neatly with FILTER and SORTBY. This piece shows the syntax, then walks through the fixes those examples usually skip: combining with FILTER, working inside Excel Tables, and reading the errors when the formula breaks.

How the syntax reads

The two functions share the same shape: pass an array, then list the columns or rows you want. Nothing else.

=CHOOSECOLS(array, col_num1, [col_num2], ...)
=CHOOSEROWS(array, row_num1, [row_num2], ...)

Column and row numbers are 1-based, so =CHOOSECOLS(A2:E100, 1, 3) returns columns A and C from the range. Ask for the same column twice and you get it twice: =CHOOSECOLS(A2:E100, 1, 3, 1) spills three columns wide. Negative indices count from the right, so =CHOOSECOLS(A2:E100, -1) returns column E without your having to know how many columns there are.

CHOOSEROWS works the same way. =CHOOSEROWS(A2:E100, 1, 2, 3) returns the first three rows; =CHOOSEROWS(A2:E100, -1) returns the last row, whatever its index. Both spill, so you drop the formula into one cell and the result fans out. If you try to write into that spill range from another formula, you get #SPILL!.

Availability is the other question worth answering up front. CHOOSECOLS and CHOOSEROWS live in Microsoft 365 and Excel 2024. Older builds — 2021, 2019, and the perpetual editions before 2024 — do not have them; the workbook shows #NAME? when opened on those versions. Note that once in the workbook if colleagues on older builds will read it.

When it beats INDEX with array constants

Before dynamic arrays, the standard way to pick specific columns from a range was to wrap INDEX in an array constant: =INDEX(A2:E100, SEQUENCE(99), {1,3,5}). It works, but it hides the intent — a reader has to unpack the constant to know which columns you meant, and any column insert between A and E silently shifts the numbers. CHOOSECOLS reads left to right, so the same reshape is =CHOOSECOLS(A2:E100, 1, 3, 5). If someone inserts a column at C, both formulas break — but the CHOOSECOLS version fails visibly by picking the wrong column, and you fix it by changing one number in one place.

=INDEX(A2:E100, SEQUENCE(99), {1,3,5})

=CHOOSECOLS(A2:E100, 1, 3, 5)

There is one place INDEX still wins: legacy workbooks that need to open in Excel 2019 or the perpetual editions before 2024. Keep INDEX with the array constant for those. Everywhere else, CHOOSECOLS is shorter, self-describing, and survives audit better because the reshape is visible instead of hidden.

If you already lean on VSTACK and HSTACK to combine ranges from several sheets, CHOOSECOLS is the natural companion — stack first, then pick the columns you actually want.

A real reshape: dropping and reordering export columns

Every SaaS export ships with the same columns in the wrong order. Say your billing tool spills out Invoice ID, Client, Line Item, Amount, Currency, Status, and the report you owe your finance lead is Client, Invoice ID, Amount, Currency. That is a reorder plus a drop.

The single-formula fix reads the whole range and picks the four report columns in the order you want them:

=CHOOSECOLS(A2:F500, 2, 1, 4, 5)

The 2 is Client, the 1 is Invoice ID, the 4 is Amount, the 5 is Currency. Drop that into any empty cell and the result spills across four columns. When next month’s export lands in the same range, the formula recomputes. No manual work, no drift.

If you also want to relabel the output, wrap the spill in VSTACK to add a header row: =VSTACK({"Client","Invoice","Amount","Currency"}, CHOOSECOLS(A2:F500, 2, 1, 4, 5)). One formula, one recompute. If the source layout shifts, you change the numbers, not the pipeline.

Combining with FILTER and SORTBY

Most reshapes carry a filter or a sort with them. CHOOSECOLS composes cleanly with both, and the order matters: FILTER or SORTBY on the outside, CHOOSECOLS on the inside, means you narrow the rows first and then trim the columns. It is faster on wide ranges, and the formula reads in the same order you would say it out loud.

To show only paid invoices for one client, filter first, then pick the four report columns:

=CHOOSECOLS(FILTER(A2:F500, (B2:B500="Acme") * (F2:F500="Paid")), 2, 1, 4, 5)

Sort the same result by amount, largest first:

=SORTBY(
  CHOOSECOLS(FILTER(A2:F500, B2:B500="Acme"), 2, 1, 4, 5),
  FILTER(D2:D500, B2:B500="Acme"),
  -1
)

That looks dense, but every piece has one job. FILTER selects rows, CHOOSECOLS picks columns, SORTBY orders the result. When any one of them errors, Excel points at that fragment specifically.

The one order to avoid is CHOOSECOLS wrapping FILTER’s criteria range. FILTER needs the criteria columns to line up with the source rows; if you narrow the source first, you also cut out the columns FILTER is checking against. Filter first, pick second — that order works with any dynamic-array chain, not just this one.

Using structured Table references so formulas survive column inserts

CHOOSECOLS accepts a plain range like A2:F500, but the range is fragile: insert a column at C and every hardcoded index shifts. Microsoft’s CHOOSECOLS reference lists 365 and Excel 2024 as the only builds where the function exists, and that fragility bites hardest on workbooks meant to run for years. The fix is to source from an Excel Table and reference it by name.

Turn the range into a Table with Ctrl+T, name it tblInvoices from the Table Design tab, and the formula becomes:

=CHOOSECOLS(tblInvoices, 2, 1, 4, 5)

The Table absorbs new rows automatically, so your reshape covers next month’s export without any range editing. Insert a column between Client and Line Item and the numeric indices still shift — but the failure is visible in the output, not silent.

For column names that make the intent explicit, mix in MATCH on the header row:

=CHOOSECOLS(
  tblInvoices,
  MATCH("Client",     tblInvoices[#Headers], 0),
  MATCH("Invoice ID", tblInvoices[#Headers], 0),
  MATCH("Amount",     tblInvoices[#Headers], 0),
  MATCH("Currency",   tblInvoices[#Headers], 0)
)

That verbose form buys you rename-safety and reorder-safety: shuffle the source columns however you like, and the formula still returns Client, Invoice ID, Amount, Currency because it is asking by header text, not by position. Wrap the four MATCH calls in a LAMBDA if you write this pattern often.

• ✓ Source is an Excel Table, not a fixed A2:F500 range
• ✓ Table has a descriptive name from Table Design, not the default Table1
• ✓ Column headers are unique, so MATCH resolves without ambiguity
• ✓ Header row exists and is included in [#Headers]

Troubleshooting the errors you’ll actually see

Four errors show up around CHOOSECOLS and CHOOSEROWS, and each has one fix. Learn the shapes and you stop guessing.

#NAME? is the trap that catches teams first, because the workbook looks fine on the author’s laptop and only breaks when a colleague on an older build opens it. If you cannot mandate 365, add a plain-text note next to the formula so the reader knows what to look for. The same version dance bites lots of dynamic-array chains — the spill-error walkthrough covers the mirror case, where the function exists but the array collides with data below it.

When none of those fit, use Formulas → Evaluate Formula. It steps through the calculation one layer at a time. Slow, but for a broken CHOOSECOLS chain it is the fastest way to find which layer is actually wrong.

Where to start

Pick one wide export you rerun every week and rebuild the pipeline in that order:

1. Turn the source into an Excel Table (Ctrl+T) and give it a name.
2. Write one CHOOSECOLS to trim it to the columns your report needs, in report order.
3. Wrap it in FILTER for the row cuts; wrap that in SORTBY only if the ordering matters.
4. Add a VSTACK header row so the output reads as a finished report, not a raw spill.

The first time someone inserts a column upstream the formula will land on the wrong data, and the four error codes above cover almost every other case. The rest of your workflow stays the same. One formula replaces the whole copy-paste dance.