Dynamic Arrays in Excel: FILTER, SORT, UNIQUE and SEQUENCE Explained
Dynamic arrays are the biggest change to the Excel calculation engine since the product launched. One formula in one cell can now return an entire table of results that automatically resizes as your data changes — no dragging fill handles, no Ctrl+Shift+Enter, no helper columns. This guide covers the four core functions (FILTER, SORT, UNIQUE, SEQUENCE), how spilling actually works under the hood, and the patterns power users chain together to replace whole blocks of legacy formulas.
Dynamic arrays require Excel 2021, Excel 365, or Excel for the web. Excel 2019 and earlier fall back to old single-value behavior.
What spilling is (and why it changes everything)
Type =A2:A10 into an empty cell in modern Excel and you get nine cells of output from one formula. That overflow into neighboring cells is called spilling. The cell you typed the formula into is the only cell that contains a formula; the rest is the spill range, marked with a thin blue border when selected.
Three things make spilling more than a convenience:
- The spill range resizes automatically. Filter results grow from 12 rows to 40 rows when the data changes — nothing to redrag.
- You can reference the whole spill with
#. If a formula inE2spills,=COUNTA(E2#)counts every result, however many there are today. The#operator (the spilled range operator) is the glue for chaining dynamic functions. - Implicit intersection is gone. Old Excel silently grabbed one value from a range in many contexts. Modern Excel returns the whole array unless you prefix with
@to force a single value. If you’ve inherited a workbook full of@signs, that’s the compatibility layer at work. For background on how Excel evaluates arrays generally, see our primer on arrays in Excel.
The #SPILL! error
A formula that needs to spill into occupied cells fails entirely with #SPILL! rather than overwriting your data. The fix is almost always to clear the blocking cells — click the error, click the warning icon, and choose Select Obstructing Cells to find the culprit. Merged cells and Excel Tables also block spilling: a dynamic array formula cannot live inside a table, though it can happily reference one. Our dedicated walkthrough on fixing the #SPILL! error covers every variant, including the rarer “out of memory” and “unknown” cases.
FILTER: return only the rows that match
=FILTER(array, include, [if_empty])FILTER is the function people actually wanted when they reached for VLOOKUP-with-multiple-results hacks. Given sales data in A2:D100 with region in column B:
=FILTER(A2:D100, B2:B100="West", "No matches")That returns every West row — all four columns — and updates live as the source changes. Always supply the third argument; without it, zero matches returns #CALC!.
Conditions combine with * for AND and + for OR (these are array multiplications, not the words AND/OR, which don’t return arrays):
=FILTER(A2:D100, (B2:B100="West")*(D2:D100>5000))
=FILTER(A2:D100, (B2:B100="West")+(B2:B100="East"))For a deeper tour of the syntax and edge cases, see our FILTER function guide.
SORT and SORTBY: ordering without touching the source
=SORT(array, [sort_index], [sort_order], [by_col])
=SORTBY(array, by_array1, [sort_order1], ...)=SORT(A2:D100, 4, -1) returns the data ordered by column 4, descending. The crucial difference from clicking the Sort button: the source data never moves. Your raw data stays in entry order; the sorted view lives elsewhere and recalculates itself. That alone eliminates the “someone sorted half the columns and scrambled the data” disaster.
SORTBY earns its keep in two situations:
- Sorting by a column you don’t want in the output.
=SORTBY(A2:A100, D2:D100, -1)returns just the names, ordered by sales. - Multi-level sorts.
=SORTBY(A2:D100, B2:B100, 1, D2:D100, -1)sorts by region ascending, then sales descending — each key gets its own direction.
UNIQUE: deduplicate with a formula
=UNIQUE(array, [by_col], [exactly_once])=UNIQUE(B2:B100) returns each region once. The often-missed third argument changes the question entirely: =UNIQUE(B2:B100,,TRUE) returns only values that appear exactly once — useful for spotting one-off customers or orphaned IDs. UNIQUE also works across multiple columns, deduplicating whole rows: =UNIQUE(A2:B100) keeps each name-plus-region combination.
We cover the function’s quirks (case-insensitivity, blank handling) in the UNIQUE function guide, and if you only need a count rather than a list, see counting unique values — though =ROWS(UNIQUE(B2:B100)) is now the shortest answer.
SEQUENCE: generate numbers on demand
=SEQUENCE(rows, [columns], [start], [step])SEQUENCE looks like a toy until you realize it replaces every “type 1, type 2, drag down” workflow:
| Formula | Result |
|---|---|
=SEQUENCE(10) | 1 through 10 in a column |
=SEQUENCE(1, 12) | 1 through 12 across a row |
=SEQUENCE(12, 1, DATE(2026,1,1), 31) | Roughly monthly dates (use EDATE for exact months) |
=DATE(2026,1,1) + SEQUENCE(365) - 1 | Every day of 2026 |
=SEQUENCE(5, 5) | A 5×5 grid, 1–25, filling across then down |
The killer use is as an index generator inside other functions. Loan amortization periods, dynamic chart axes, and “give me the first N items” logic all start with SEQUENCE.
XLOOKUP with arrays: one formula, many columns
XLOOKUP returns arrays natively in both directions. If headers are in A1:F1 and you look up an order ID in column A:
=XLOOKUP("ORD-1042", A2:A500, B2:F500)returns the entire matching row — five columns spilled from one formula. No more five separate VLOOKUPs with hand-edited column numbers. You can also feed XLOOKUP an array of lookup values: =XLOOKUP(H2:H10, A2:A500, D2:D500) performs nine lookups at once and spills nine results.
If you’re new to the function, start with the XLOOKUP guide; if you’re deciding whether to migrate old workbooks, our comparison of XLOOKUP vs VLOOKUP vs INDEX MATCH lays out the trade-offs.
Combining them: where dynamic arrays earn their keep
Each function is useful alone; chained, they replace entire dashboard back-ends. Functions nest naturally because each one accepts the array another produces.
A sorted, deduplicated report list:
=SORT(UNIQUE(B2:B100))Top 5 by sales — FILTER the sorted data against a SEQUENCE-style row index using TAKE (Excel 365):
=TAKE(SORT(A2:D100, 4, -1), 5)On Excel 2021 without TAKE, the classic pattern is =INDEX(SORT(A2:D100,4,-1), SEQUENCE(5), {1,2,3,4}).
A self-maintaining dropdown. Put =SORT(UNIQUE(B2:B100)) in H2, then create a dropdown list whose Source is =$H$2#. The # makes the list resize automatically — new regions in the data appear in the dropdown with zero maintenance.
A live mini-dashboard. With the dropdown selection in J1:
=SORT(FILTER(A2:D100, B2:B100=J1, "No data"), 4, -1)One formula: filter to the selected region, sort by sales descending, refresh on every edit. This is the connective pattern to internalize — spill a control list, reference it with #, FILTER against the selection, SORT the output.
Replacing old CSE array formulas
Before 2020, multi-value formulas required pressing Ctrl+Shift+Enter, which wrapped the formula in {curly braces}. Those legacy CSE formulas were fragile: fixed-size, easy to break by editing one cell of the block, and confusing to inherit. Migration guidance:
| Legacy CSE pattern | Modern replacement |
|---|---|
{=SUM(IF(B2:B100="West",D2:D100))} | =SUMIFS(D2:D100, B2:B100, "West") or =SUM(FILTER(D2:D100, B2:B100="West", 0)) |
{=MAX(IF(B2:B100="West",D2:D100))} | =MAXIFS(D2:D100, B2:B100, "West") |
Multi-result extraction with SMALL(IF(...)) | =FILTER(...) |
| Frequency-based unique counts | =ROWS(UNIQUE(...)) |
| Fixed-size CSE blocks selected in advance | Any spilling formula — delete the block, enter once |
Old CSE formulas still calculate in modern Excel, so nothing breaks on upgrade. But when you touch one, rewrite it: enter the modern equivalent normally (just Enter, no key chord) and delete the rest of the old block. The result is faster to calculate, resizes itself, and the next person can actually read it.
FAQ
Why does my formula return only one value instead of spilling?
Either you’re on Excel 2019 or older (no dynamic array support), the formula is inside an Excel Table (Tables suppress spilling — move it outside), or there’s an @ prefix forcing implicit intersection. Remove the @ and the formula spills normally.
Can I edit or delete part of a spill range?
No. Only the top-left cell contains the formula; the spilled cells are “ghost” values that show the formula greyed-out in the formula bar. Edit or delete the anchor cell and the whole spill follows. Typing into a spilled cell triggers #SPILL! instead.
How do I reference a spill range in another formula?
Append # to the anchor cell: if E2 spills, use E2#. It resizes automatically and works in charts, data validation, and other formulas. In a chart series, wrap it in a defined name first, since series references don’t accept # directly in all versions.
Do dynamic arrays slow down large workbooks?
Generally they’re faster than the equivalent thousands of individually-dragged formulas, because Excel calculates one expression and spills the result. The exceptions are volatile chains and whole-column references — use A2:A10000, not A:A, inside FILTER and SORT.
What replaces Ctrl+Shift+Enter — do I ever need it now?
On Excel 365 and 2021, never. Every formula is entered with plain Enter, and Excel decides whether it spills. The curly braces you see on old formulas can’t be typed by hand; they’re just the legacy CSE marker.
