A raw data table has 12 columns; the report needs 3 of them, in a different order, with the totals row on top. The old fix was a nest of INDEX and SEQUENCE, or a helper sheet the reader inherited from someone who has since left the team. CHOOSECOLS and CHOOSEROWS collapse that to a single spill formula: pass the source array, list the column or row numbers you want, and Excel returns exactly those slices in exactly the order you asked for. This post walks through the small syntax, the reorder pattern that turns them into report tools, the negative-index trick, the pipelines they build with FILTER and SORT, and the two errors that trip people up on real workbooks.
What CHOOSECOLS and CHOOSEROWS actually do
Both functions are dynamic-array natives introduced with the 2022 function refresh. They take a source array and one or more index numbers, and they spill a new array containing only the requested columns or rows, in the order given. CHOOSECOLS keeps every row of the source and picks columns; CHOOSEROWS keeps every column and picks rows.
=CHOOSECOLS(array, col_num1, [col_num2], ...)
=CHOOSEROWS(array, row_num1, [row_num2], ...)array is any 2-D range or array expression — a plain range like A1:F100, a table reference like Sales[#All], or the output of another dynamic-array formula. Each index argument is a whole number: positive counts from the start, negative counts from the end, and a value that resolves to zero or exceeds the source dimension throws #VALUE!. Repeating an index is legal and duplicates the slice.
#NAME? when opened there. Check the official CHOOSECOLS reference on Microsoft Support before rolling a shared workbook out to a team on mixed versions.
The smallest useful example
Take a five-column source at A1:E7: Date, Rep, Region, Revenue, Margin. To spill just Rep and Revenue into G1:
=CHOOSECOLS(A1:E7, 2, 4)Column 2 is Rep, column 4 is Revenue. The result is a two-column spill starting at G1, seven rows tall — headers included, because the source range started at row 1. That’s the whole idea. Everything below is variations on which index numbers you pass and where they come from.
Reordering columns for a report
Real reports rarely want the source order. A finance summary might want Revenue first, then Rep, then Margin — columns 4, 2, 5 from the source above. The naive way is to list them out:
=CHOOSECOLS(A1:E7, 4, 2, 5)That works, but the index list gets awkward once you’re picking eight or ten columns, and any teammate reading it has to count columns in the source to make sense of it. The tidier pattern is an array constant — a "column recipe" you can name and reuse:
=CHOOSECOLS(A1:E7, {4,2,5})The braces make the intent obvious: the formula picks three columns in this order. Store the recipe on a config sheet as a real range and reference it, and non-formula people on the team can edit the report layout without touching the formula.
| A | B | C | |
|---|---|---|---|
| 1 | Report layout | Column # | Source header |
| 2 | Position 1 | 4 | Revenue |
| 3 | Position 2 | 2 | Rep |
| 4 | Position 3 | 5 | Margin |
Feed B2:B4 straight into the formula as =CHOOSECOLS(A1:E7, TRANSPOSE(B2:B4)) — the TRANSPOSE is only there because CHOOSECOLS reads the index list horizontally when it comes from a range. Now the report layout is data, not code.
Negative indexes: pick from the end
Negative numbers count backwards from the last column or row. -1 is the last, -2 the second-to-last, and so on. The two most common uses are grabbing the totals row from the bottom of a table and pulling the last few periods off a rolling monthly sheet.
=CHOOSEROWS(Sales[#All], 1, -1)Row 1 of the [#All] reference is the header row; row -1 is the totals row that Excel adds when you turn on Total Row under Table Design. The spill returns just those two rows — a compact header-plus-totals card for a dashboard, no extra formulas required.
=INDEX(A2:E7, ROWS(A2:E7), 0)Reads the last row, but breaks the moment rows are added or removed unless the range is a table.
=CHOOSEROWS(A2:E7, -1) Same result, one function, and the intent ("last row") is on the page instead of hidden in a ROWS() call.
Trailing window instead of trailing row
Extend the same idea to a window. To spill the last three periods of a monthly range:
=CHOOSEROWS(Monthly[#Data], -3, -2, -1)Order matters — the spill preserves the argument order, so -3, -2, -1 gives oldest-to-newest and -1, -2, -3 gives newest-to-oldest. If the report wants only the newest, drop the extras.
Combine with FILTER, SORT, and UNIQUE
The real payoff is chaining. CHOOSECOLS and CHOOSEROWS both accept dynamic-array output as their array argument, so the “shape then slice” pipeline is one formula deep. The pattern below builds a top-five revenue report from a full transactions table.
=CHOOSEROWS(
SORT(
FILTER(Sales[#Data], Sales[Region]="APAC"),
4, -1
),
SEQUENCE(5)
)FILTER keeps only APAC rows, SORT orders them by column 4 (Revenue) descending, and CHOOSEROWS takes the first five via SEQUENCE(5). Change the region text in one cell and the whole report refreshes. If the report should also drop the internal-only columns before display, wrap the whole thing in a CHOOSECOLS:
=CHOOSECOLS(
CHOOSEROWS( ... as above ... ),
{1,2,4}
)The same shape works with UNIQUE when the source has duplicate rows: run UNIQUE first, then slice. See the FILTER function walkthrough for the criteria-side patterns, the SORT and SORTBY guide for multi-column ordering, and the GROUPBY primer if the report is a summary rather than a row selection.
Common errors and what to do
Two errors account for almost every real-workbook failure with these functions: an out-of-range index and a blocked spill. Both have simple root causes once you know where to look.
#VALUE! from CHOOSECOLS means at least one index is zero or larger than the source column count. If the source is a table and a column was recently deleted, every downstream CHOOSECOLS pointing at the old position breaks silently until you edit the formula.
#VALUE! errors. The fix is almost never the formula — it’s the index list. Add a helper cell that shows =COLUMNS(source) next to the recipe range so a shrunk source is visible at a glance. If the recipe lives on another sheet, wrap each index in MIN(index, COLUMNS(source)) only as a defensive last resort; silently clamping to the last column can hide real breakage.
#SPILL! errors. The spill target has to be empty for the full result. If the report is 20 rows tall and something is sitting in row 15 of the spill area, the whole formula returns #SPILL!. Click the yellow warning triangle and Excel highlights the exact blocking cells. Move them, delete them, or spill the formula somewhere the source can grow into without hitting other content.
The insert-column trap
Index numbers are literal, not named — insert a new column in the middle of the source and every downstream CHOOSECOLS(source, 3, 5) is now pointing at different data with no error at all. This is the most dangerous class of bug because nothing turns red. Two habits keep it from biting: use a named recipe range so the recipe travels with the source, or reference the source by table column name and let a wrapper convert names to positions with MATCH.
CHOOSECOLS vs TAKE, DROP, and INDEX
Excel has three other ways to slice an array. Each is right for a different shape of problem.
| Function | Best for | Weakness |
|---|---|---|
| CHOOSECOLS / CHOOSEROWS | Non-adjacent columns or rows, custom order, repeats | Index numbers are positional and drift when the source is edited |
| TAKE / DROP | First-N or last-N contiguous rows or columns | Can’t reorder or pick out of sequence |
| INDEX with SEQUENCE | Rectangular slices computed from other formulas | Verbose, and older readers of the workbook expect it to return a single value |
| FILTER | Row selection by a condition, not a position | Can’t select columns; pair with CHOOSECOLS when you need both |
The heuristic: if the report says "the first three columns" use TAKE; if it says "columns 1, 4, and 7 in that order" use CHOOSECOLS; if it says "every row where Region is APAC" use FILTER; and if it says both "where Region is APAC" and "only columns 1, 4, 7," wrap FILTER in CHOOSECOLS. Related patterns for text extraction sit in the TEXTBEFORE and TEXTAFTER walkthrough, which fills the row-shaping side of the same toolkit.
Where to start using them
The best first place to reach for CHOOSECOLS is a report that already exists as a copy-paste of a source range — one you edit every week to match the source’s current column order. Rebuild it as one CHOOSECOLS against a named recipe range and the weekly re-alignment disappears. For CHOOSEROWS, the first win is any dashboard tile that reads "the latest row" from a growing table; the -1 index does what the reader assumed =LAST(range) would do in a language that had it.
- ✓ Source is on Microsoft 365 or Excel 2024 — older builds show
#NAME? - ✓ Recipe indexes live in a named range, not hard-coded in the formula
- ✓ Spill target is empty for the full expected height and width
- ✓ If the source table can shrink, a
COLUMNS()sanity cell sits next to the recipe
Neither function does anything you couldn’t do before. They just make the intent visible on the page, which is the difference between a report a teammate can maintain and one only its author understands.