Dynamic Arrays in Excel: FILTER, SORT, UNIQUE and the Formula That Fills Its Own Range

For thirty years, a formula in Excel meant a formula in a cell. One cell, one answer. If you wanted twenty answers you wrote the formula once and dragged it down twenty rows, and from that moment on the sheet had a shape you had to maintain by hand. Rows arrive, you drag further. Rows leave, you delete the leftovers. Someone forgets, and the report quietly stops covering the last four records.

Dynamic arrays ended that. A single formula can now return a whole block of values and spill them into the cells below and beside it, sizing itself to the answer instead of to whatever you dragged last Tuesday. The list of regions grows to four; the formula returns four. A deal is deleted; the filtered table shrinks the same second.

This guide covers the five functions that make it work — UNIQUE, SORT, SORTBY, FILTER and SEQUENCE — the # operator that lets you point at a range whose size you don't know, and the two error codes that only exist because spilling exists.

Tip: Every example below runs on the table shown after section 1. Copy it into a blank sheet starting at A1 and the cell references will line up exactly. You need Excel 365 or Excel 2021 (or Excel for the web) — section 9 covers what happens if a colleague opens the file in something older.

1) One Formula, Many Cells

Type this in H2 of a sheet holding the table below, and press Enter once:

=UNIQUE(B2:B11)

Three values appear — H2, H3, H4 — from one formula. Click H3 and the formula bar shows =UNIQUE(B2:B11) greyed out; click H2 and it shows the same formula in black, editable. That's the whole model:

Two consequences follow immediately, and both take some getting used to. You cannot delete or edit one cell of a spill range — Excel answers "You can't change part of an array" and refuses. And you no longer control how many rows the result occupies; the data does. That second one is the entire point, but it means a formula can now grow into cells you were using for something else, which is where #SPILL! comes from in section 8.

2) UNIQUE: A Distinct List That Maintains Itself

🎯 Scenario: You need the list of regions for a summary block, and you need it to still be right when someone opens a deal in a region that didn't exist last quarter.

=UNIQUE(array, [by_col], [exactly_once])

=UNIQUE(B2:B11)

Result: EMEA, LATAM, AMER — three cells, in order of first appearance, not alphabetical.

That ordering catches people. UNIQUE preserves the order it met the values in; if you want them alphabetical you have to say so, which is section 3's job.

The third argument is the one almost nobody knows about:

=UNIQUE(A2:A11, FALSE, TRUE)

Result: Sofia Nyberg

Set exactly_once to TRUE and you get the values that appear once and only once — not one copy of each value, but the values with no duplicates at all. Priya appears three times, Tom twice, Marco twice, Dana twice; only Sofia is alone. This is the fastest one-off audit in Excel: reps with a single deal, invoice numbers that were never paired with a credit note, IDs that failed to match.

UNIQUE also works across whole rows. =UNIQUE(B2:C11) returns the distinct region + product combinations, comparing each row as a unit rather than each cell:

Result: seven rows — EMEA/Analytics, EMEA/Storage, LATAM/Analytics, AMER/Storage, EMEA/Compute, LATAM/Compute, AMER/Analytics. Count them in the data before you trust the count in the sheet; that habit is worth more than the formula.

Pitfall: UNIQUE treats a trailing space as a difference. "EMEA " and "EMEA" are two regions as far as it is concerned, and they look identical on screen. If a distinct list comes back longer than you expected, wrap the source in TRIM — =UNIQUE(TRIM(B2:B11)) — and see if the count drops.

3) SORT and SORTBY: Order Without Touching the Source

🎯 Scenario: A leaderboard that reorders itself when a number changes, without anyone re-running Data ▸ Sort.

=SORT(array, [sort_index], [sort_order], [by_col])

=SORT(UNIQUE(A2:A11))

Result: Dana Whitlock, Marco Silva, Priya Raman, Sofia Nyberg, Tom Beckett

sort_index is the column within the array you passed, not the sheet column. Handing SORT the six-column table and asking for column 5 sorts by deal value:

=SORT(A2:F11, 5, -1)

Result: the whole table, biggest deal first — Dana's 31,200 on top, Sofia's 4,900 at the bottom. -1 is descending; 1, the default, is ascending.

SORTBY is the version for when the thing you want to sort and the thing you want to sort by are different ranges:

=SORTBY(A2:A11, E2:E11, -1)

Result: the rep names alone, ordered by deal value descending — Dana Whitlock (31,200), Marco Silva (27,300), Priya Raman (24,500), Priya Raman (20,100), Tom Beckett (18,200), … Names repeat, because deals repeat; the list is ten rows long, same as the source.

The difference matters more than it looks. SORT can only order by a column that is inside the array it returns, so a name-only list can't be sorted by value with SORT. SORTBY takes the sort keys separately, which also means you can sort by something you never display — priority, a hidden score, a date the reader doesn't need to see. Multiple keys just keep going: =SORTBY(A2:A11, B2:B11, 1, E2:E11, -1) sorts by region ascending, then by value descending within each region.

Pitfall: the by_array in SORTBY must be exactly as tall as the array being sorted. One extra row in either range and you get #VALUE! with no hint as to which side is wrong.

4) FILTER: The Query You Used to Write by Hand

FILTER is the function that changes how the sheet is built. Everything before it — helper columns of IF, an AutoFilter someone has to remember to re-apply, a pivot table refreshed by hand — was a workaround for not having it.

=FILTER(array, include, [if_empty])

🎯 Scenario: A live list of closed-won deals, feeding a commission summary that must never quietly go stale.

=FILTER(A2:F11, D2:D11="Won")

Result: four rows — Priya/EMEA/Analytics/24,500, Dana/AMER/Storage/31,200, Tom/EMEA/Compute/12,750, Priya/EMEA/Compute/20,100.

The second argument, include, is not a condition in the English sense — it's an array of TRUEs and FALSEs exactly as tall as the data. D2:D11="Won" evaluates to ten booleans, and FILTER keeps the rows where it found TRUE. Once you see it that way, combining conditions stops being a syntax question and becomes arithmetic:

=FILTER(A2:F11, (B2:B11="EMEA")*(D2:D11="Won"))

Result: three rows, totalling 57,350 — the EMEA won deals only. Note the brackets around each condition: they are not optional, because * binds tighter than = and without them Excel tries to multiply text by text.

Now the OR case, which is where hand-written filters usually fall apart:

=FILTER(A2:F11, (E2:E11>20000)+(F2:F11>60))

Result: six rows — the four deals above 20,000 plus the two that have been open more than sixty days. A deal that satisfies both conditions still appears once; FILTER only cares whether the number is non-zero, not what it is.

🎯 Scenario: The same filter, wired to a cell someone can type in.

=FILTER(A2:F11, B2:B11=$H$1, "No deals in this region")

Put LATAM in H1 and two rows appear. Put APAC in H1 and you get the message instead of an error — which is what if_empty is for. Omit it and an empty result returns #CALC!, and a report that shows #CALC! whenever a filter happens to match nothing looks broken even though it is working perfectly. Fill in the third argument every time.

FILTER composes with the aggregates you already use, and this is where most of its day-to-day value sits:

=SUM(FILTER(E2:E11, D2:D11="Won"))

Result: 88550

That is a SUMIFS you could have written another way. But =AVERAGE(FILTER(F2:F11, D2:D11="Open")) → 25.25 is the median-style calculation AVERAGEIFS handles, and =MEDIAN(FILTER(E2:E11, B2:B11="EMEA")) is one there is no MEDIANIFS for at all. Any function that accepts a range accepts a filtered array, which quietly gives you a conditional version of every statistic Excel has.

5) SEQUENCE: Numbers and Dates Out of Nothing

🎯 Scenario: A rank column beside a list whose length changes, or twelve month-ends for a forecast that used to be typed by hand.

=SEQUENCE(rows, [columns], [start], [step])

=SEQUENCE(5)          → 1, 2, 3, 4, 5 down five rows
=SEQUENCE(1, 5)       → 1, 2, 3, 4, 5 across five columns
=SEQUENCE(5, 1, 10, 5) → 10, 15, 20, 25, 30

On its own that looks like a party trick. It earns its place when the count comes from the data rather than from you:

=SEQUENCE(ROWS(H2#))

That numbers a spilled list — however long it currently is — and renumbers it the moment the list changes length. Dates are the other everyday use, since Excel dates are just numbers:

=EDATE(DATE(2026,1,1), SEQUENCE(12, 1, 0, 1))

Result: the first of each month for all of 2026, once you format the cells as dates. SEQUENCE(12,1,0,1) produces 0 through 11, and EDATE adds that many months to the start date — which is why this handles the short months correctly where +30 never would.

6) The # Operator: Pointing at a Range You Didn't Size

Here is the problem spilling creates. =UNIQUE(B2:B11) in H2 returns three regions today. If you write =SUMIF(B2:B11, H2:H4, E2:E11) next to it and a fourth region appears next month, your summary covers three of four regions and gives no indication that it is now wrong.

The spill range operator fixes it. H2# means the whole range H2 currently spills into, whatever that is:

=SUMIF($B$2:$B$11, H2#, $E$2:$E$11)

Result: 85150, 42700, 42900 — one total per region, spilled to match. Add a region to the data and both columns grow together, because the criteria argument is itself a spilled array and SUMIF returns one result per criterion.

That trailing # is the piece that turns dynamic arrays from a nicer way to write formulas into a different way to build a sheet. Anywhere you would once have hard-coded a range and hoped:

=COUNTA(H2#)                    → how many rows the list currently has
=INDEX(H2#, 1)                  → the first item, whatever it is
=TEXTJOIN(", ", TRUE, H2#)      → the whole list as one sentence

Two places worth knowing about specifically:

• Data validation. In the Source box, type =$H$2# and the dropdown grows and shrinks with the list. This is the single best reason to build dropdowns from SORT(UNIQUE(...)) rather than a typed list.
• Charts. The series dialog will not accept H2#. The workaround is a defined name — Formulas ▸ Define Name, refers to =Sheet1!$H$2# — and then point the chart's series at the name. The chart then follows the spill.

Pitfall: # refers to the current spill. If the anchor formula is deleted, every # reference pointing at it collapses to #REF! — the same as any other broken reference, but it takes out an entire dependent block at once rather than one cell.

7) Composing Them: The Report That Rebuilds Itself

The five functions were designed to nest, and the useful formulas are almost always two or three of them stacked. Read them from the inside out.

🎯 Scenario: A block on a summary sheet showing EMEA's won deals, rep and value only, biggest first — rebuilt automatically whenever the deal list changes.

=SORT(FILTER(HSTACK(A2:A11, E2:E11), (B2:B11="EMEA")*(D2:D11="Won")), 2, -1)

Result:

HSTACK glues the two non-adjacent columns into one two-column array; FILTER keeps the EMEA wins; SORT orders the survivors by the second column, descending. No helper columns, no intermediate range, nothing to maintain. (HSTACK needs Excel 365 or Excel 2024 — in Excel 2021, =FILTER(CHOOSE({1,2}, A2:A11, E2:E11), …) does the same job.)

🎯 Scenario: The top three deals, whatever they are this morning.

=TAKE(SORT(A2:F11, 5, -1), 3)

Result: Dana's 31,200, Marco's 27,300, Priya's 24,500.

🎯 Scenario: A sorted, distinct list of the reps who currently have something open — the exact source you want behind a dropdown.

=SORT(UNIQUE(FILTER(A2:A11, D2:D11="Open")))

Result: Dana Whitlock, Marco Silva, Priya Raman, Tom Beckett

Point the data validation Source at =$H$2# and the dropdown maintains itself for the rest of the workbook's life. That combination — SORT(UNIQUE(FILTER(…))) behind a validated cell, feeding a FILTER that reads the cell — is most of what people mean when they say a spreadsheet feels like an application rather than a document.

8) #SPILL! and #CALC!: The Two Errors That Are New

Neither of these existed before dynamic arrays, and both mean something specific.

#SPILL! — the result has nowhere to go. The formula is right; the sheet is in the way. Click the cell and the warning triangle names the reason. The five you will actually meet:

The whole-column one is the common one, and it is worth being blunt about: A:A is 1,048,576 rows. =FILTER(A:F, D:D="Won") asks Excel to evaluate a million rows on every recalculation. Point dynamic arrays at a Table instead — =FILTER(tblDeals, tblDeals[Stage]="Won") covers exactly the rows that exist, grows when rows are added, and does no wasted work.

#CALC! — the result is an empty array. In practice this means one thing: a FILTER matched nothing and you left out if_empty. Add the third argument. The other cause is a nested array (an array of arrays), which you have to work fairly hard to produce by accident.

9) The @ Sign, and What Happens in Older Excel

Open an old workbook in a current Excel and you may find @ symbols in formulas you did not put there: =@VLOOKUP(...), =@A1:A10. Nothing is broken. That is the implicit intersection operator, and it is a compatibility marker.

Before dynamic arrays, a formula handed a range where it expected a single value would silently pick the value on the same row as the formula — implicit intersection. Modern Excel doesn't do that; it spills instead. So when it opens a legacy file, it inserts @ wherever the old behaviour was in play, meaning keep taking one value here. Deleting the @ makes the formula spill, which is sometimes exactly what you want and sometimes rewrites the sheet you inherited. Delete it deliberately, not by tidying.

Going the other way is less forgiving:

The #NAME? case is worth understanding rather than fearing, because it is not a graceful degradation: the values do not survive, and saving the file in the old version can strip the formula entirely. If a workbook has to open in Excel 2019, dynamic arrays are not an option in it — no workaround, no polyfill. That is a decision to make when the file is created, not when someone emails to say every cell says #NAME?.

10) When Not to Spill

Dynamic arrays are not free, and three situations are still better served the old way.

Inside Tables. A Table column wants one formula per row, and it fills that down automatically as rows are added — which is already the maintenance-free behaviour you wanted. A spilling formula in a Table just returns #SPILL!. Use per-row formulas inside Tables and dynamic arrays outside them, reading from them.

Where the layout is fixed and printed. A spill that grows will push nothing aside — it will fail with #SPILL! instead. On a template where a total must land in a specific cell of a specific printed page, an unpredictable number of rows is a liability, not a feature.

Very large filtered sets, recalculated constantly. FILTER over 200,000 rows re-evaluates every time anything on the sheet changes. When that gets slow, the answer is usually a pivot table or Power Query — tools that hold a result until told to refresh, rather than recomputing it continuously.

Everywhere else — summaries, dropdowns, dashboards, any list whose length is a property of the data rather than a decision you made — the spilled version is smaller, more honest, and does not have to be re-dragged by whoever inherits it.

Quick Checklist (Before You Trust the Spill)

• Every FILTER has an if_empty third argument, so a no-match reads as text rather than #CALC!
• Each condition inside a multi-criteria FILTER is wrapped in its own brackets
• * for AND, + for OR — and you checked which one you actually meant
• Ranges are Tables or bounded ranges, never A:A
• Anything downstream of a spill refers to it with #, not a hard-coded range
• The cells below and right of each anchor are genuinely empty — no stray spaces
• SORT's sort_index counts columns inside the array, not columns on the sheet
• SORTBY's key arrays are exactly as tall as the array being sorted
• You know which version of Excel the last person to open this file is running

Common Mistakes Summary

1. Omitting if_empty: a filter that matches nothing shows #CALC! and looks like a broken report.
2. Missing brackets in multi-criteria filters: B2:B11="EMEA"*D2:D11="Won" is a #VALUE!, and the fix is four brackets.
3. + where you meant *: an OR filter that returns almost every row is usually this, and it produces plausible-looking output rather than an error.
4. Referencing a spill by its current address: H2:H4 is right until the data grows. H2# is right afterwards too.
5. Whole-column ranges: =FILTER(A:F, D:D="Won") works and then makes the workbook slow. Use a Table.
6. Assuming UNIQUE sorts: it returns first-appearance order. Wrap it in SORT if you need alphabetical.
7. Trailing spaces in the source: UNIQUE counts "EMEA " and "EMEA" separately, and they look identical.
8. Trying to edit a ghost cell: only the anchor is editable — "You can't change part of an array" is not a bug.
9. Building a spill inside a Table: it will always be #SPILL!. Move it out.
10. Shipping a dynamic array workbook to Excel 2019: the formulas become #NAME?, and saving there can destroy them.

Conclusion

The thing worth taking from all of this isn't the function list — it's that the size of a result is now the data's business rather than yours. A dragged formula encodes a guess about how many rows there will be, and every one of those guesses eventually goes stale in a way nobody notices. A spilled formula makes no guess at all.

If you write one thing differently after reading this, make it the dropdown: =SORT(UNIQUE(FILTER(...))) in a spare column, =$H$2# in the validation Source. It takes a minute, and it removes a maintenance job that otherwise comes back every quarter for as long as the workbook exists.

Then add the if_empty argument to every FILTER you write. It costs three words and it is the difference between a report that says No deals in this region and one that says #CALC! in front of your manager.

Want to practise? The lookup and filtering exercises in the app run on the same patterns — filtering by two conditions, sorting by a column you don't display, and the ones where the obvious formula spills onto something that was already there.