Excel Dynamic Arrays: FILTER, SORT and UNIQUE, Explained Properly

By the XLsheetAI Team · Updated August 15, 2026 · 10 min read

The short answer

A dynamic array formula returns a range instead of a single value, and Excel writes that whole range into the sheet from one cell — a behaviour called spilling. FILTER returns the rows that match a condition, SORT returns the same rows in a chosen order, and UNIQUE returns the distinct values, all of them recalculating the moment the source data changes. That is the entire idea. Everything else people find confusing about dynamic arrays — the #SPILL! error, the # operator, the greyed-out formula bar — follows from the fact that one cell now owns many cells.

What spilling actually means

Type =UNIQUE(B2:B200) into E2 and press Enter. If there are twelve distinct values in that column, twelve cells fill up: E2 through E13. Only E2 contains a formula. E3:E13 are the spill range — click any of them and the formula bar shows the formula greyed out, because those cells are output, not input. Delete E2 and the whole result vanishes. You cannot delete E7 on its own.

This is the part that trips people up who learned Excel before 2020. In the old model, one cell held one value and you dragged formulas down to cover a range. In the dynamic array model, the range is the value. A thin blue border marks the boundary of a spill range when you select any cell inside it.

The practical consequence is that you stop maintaining fill-down ranges. If your source grows from 200 rows to 400, an old helper column has to be extended by hand. A dynamic array anchored on a Table, or on a reference wide enough to cover the growth, simply produces more rows.

What these three functions replace

The difference is not only keystrokes. Advanced Filter and Remove Duplicates produce a snapshot that silently goes stale; a dynamic array produces a view that cannot go stale. That matters most in files other people open — the report is right because it recalculated, not because someone remembered to re-run a menu command.

FILTER: the workhorse

The syntax is three arguments, the third optional:

=FILTER(array, include, [if_empty])

array is what you want back, include is a column or row of TRUE/FALSE values the same height as the array, and if_empty is what to show when nothing matches. The simplest case returns whole rows:

=FILTER(A2:C200, C2:C200>1000)

Note that the array and the condition do not have to be the same columns. This returns only the names, judged on a value in a different column:

=FILTER(A2:A200, C2:C200>1000)

Multiple conditions: * for AND, + for OR

This is the one piece of syntax worth memorising. AND and OR do not work inside FILTER, because they collapse an entire array down to one TRUE or FALSE. Instead you do arithmetic on the tests, since Excel treats TRUE as 1 and FALSE as 0.

Multiplication is AND — the product is only 1 when every test passes:

=FILTER(A2:C200, (B2:B200="North")*(C2:C200>1000))

Addition is OR — the sum is non-zero when at least one test passes:

=FILTER(A2:C200, (B2:B200="North")+(B2:B200="South"))

Each condition needs its own brackets, and when you mix the two, bracket the OR group so it is evaluated before the multiplication:

=FILTER(A2:C200, ((B2:B200="North")+(B2:B200="South"))*(C2:C200>1000))

Excluding blanks is the same pattern with a not-equal test:

=FILTER(A2:C200, (C2:C200>1000)*(A2:A200<>""))

If you are used to building multi-column matches with concatenation tricks, this is the modern replacement — and the same logic underlies lookups on more than one criterion.

The empty case, and #CALC!

When nothing matches and you have not supplied a third argument, FILTER returns #CALC!. It is not a mistake in your formula; there is simply no empty array for Excel to return. Give it something to say instead:

=FILTER(A2:C200, C2:C200>1000, "No matches")

Use "" if you would rather the cell looked blank. It is worth adding the third argument as a habit in any file someone else will open, because #CALC! reads as breakage even when the answer is legitimately "none".

A #CALC! that surprises you usually means a criteria mismatch rather than an empty result: text with trailing spaces, numbers stored as text, or a date compared against a string. Test the condition on its own in a spare cell before blaming FILTER.

SORT and SORTBY

SORT orders an array by one of its own columns:

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

sort_index is the column number within the array, counted from 1, and sort_order is 1 for ascending or -1 for descending. So the largest values in the third column first:

=SORT(A2:C200, 3, -1)

Set by_col to TRUE to sort left-to-right instead of top-to-bottom, which is occasionally useful for a row of monthly headers.

SORTBY is the more flexible sibling: it orders one array using values from another, and the sort key does not have to be part of the output.

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

This returns just the names and regions, ordered by a revenue column you never display, and breaks ties alphabetically:

=SORTBY(A2:B200, C2:C200, -1, A2:A200, 1)

The rule of thumb: use SORT when the sort key is one of the columns you are showing, and SORTBY when it is not, or when you need more than one sort level.

UNIQUE, including the two arguments nobody uses

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

Pointed at a single column, it returns the distinct values in the order they first appear:

=UNIQUE(B2:B200)

Pointed at several columns, it returns distinct rows — combinations, not values. Two rows must match across every column to count as duplicates:

=UNIQUE(A2:C200)

by_col, the second argument, flips the orientation. Set it to TRUE when your data runs across a row rather than down a column:

=UNIQUE(B1:Z1, TRUE)

exactly_once, the third argument, is the genuinely different one. Set it to TRUE and you get only the values that appear exactly once in the source — the items with no duplicate at all, rather than one copy of each item:

=UNIQUE(B2:B200, FALSE, TRUE)

That distinction matters. The default answers "what are the different values here?"; exactly_once answers "which values are not repeated?" — useful for finding invoice numbers that appear in one system but not the other. Note the second argument still has to be supplied as FALSE to reach the third.

Unlike the Remove Duplicates command, none of this touches your source data. If you need the destructive version, or a comparison of the approaches, see how to remove duplicates in Excel.

Nesting them together

The three functions compose, and the standard pattern reads inside out: filter the rows, reduce to distinct values, then order the result.

=SORT(UNIQUE(FILTER(B2:B200, C2:C200>1000)))

That single cell answers "which regions had a sale over 1000, listed once each, alphabetically". Before dynamic arrays that was a filtered copy, a Remove Duplicates pass and a sort, repeated by hand on every refresh.

Add the empty-case argument and a descending sort on a fuller version:

=SORT(FILTER(A2:C200, (B2:B200="North")*(C2:C200>1000), "None this month"), 3, -1)

One caution on nesting order: UNIQUE outside FILTER de-duplicates the filtered result, which is nearly always what you want. Putting FILTER outside UNIQUE requires the condition array to match the de-duplicated height, which it will not, and you get a #VALUE! error.

The # spill operator

Once a formula spills, you often need to point at the whole result — but the result changes size, so a fixed range like E2:E13 is wrong by tomorrow. The # operator solves this. Written after the anchor cell, it means "everything this formula produced, whatever size that is today".

=COUNTA(E2#)
=SUM(F2#)
=TEXTJOIN(", ", TRUE, E2#)

It works in most places a range does. A chart whose series is set to =Sheet1!$E$2# grows and shrinks with the data. A dropdown built from a live distinct list needs nothing more than a data validation source of:

=$E$2#

You can also feed one spill range into another formula, which is how small dashboards get built without a single helper column:

=SORT(UNIQUE(B2:B200))

in E2, then in F2:

=SUMIF($B$2:$B$200, E2#, $C$2:$C$200)

The second formula spills to exactly the same height as the first, permanently. Add a new region to the source and both grow by one row together.

Why #SPILL! happens

#SPILL! never means the formula is wrong. It means the answer has nowhere to go. Excel shows the intended spill area with a dashed border, and the warning triangle offers Select Obstructing Cells, which jumps straight to the blocker.

The Table one catches nearly everyone. Excel Tables are the recommended way to hold source data, so it feels natural to put the FILTER in a spare Table column — and that is precisely the one place it cannot work. Dynamic arrays read from Tables happily; they just cannot live inside one.

The whole-column case has a neat consequence: referencing a Table column, as in =UNIQUE(Sales[Region]), is both safe from #SPILL! and automatically sized, because the reference grows as rows are added.

Which Excel versions have this

Dynamic arrays need Microsoft 365, Excel 2021, Excel 2024, or Excel for the web. Google Sheets has had equivalents for years, with its own FILTER, SORT and UNIQUE and slightly different arguments.

They are absent from Excel 2019, 2016 and everything before, and they are not coming. The feature rests on a rebuilt calculation engine, not a new function library, which is why no update or add-in brings it to those versions.

Open a dynamic array workbook in Excel 2019 and the formulas survive in the file but do not run. They appear with an _xlfn prefix, which is Excel's marker for "a function this build does not recognise":

=_xlfn._xlws.FILTER(A2:C200, C2:C200>1000)
=_xlfn.UNIQUE(B2:B200)

Every affected cell shows #NAME?. Nothing is corrupted — reopen the same file in Microsoft 365 and the results come back — but anyone on the older build sees a sheet of errors. If a workbook has to work everywhere, build the shared version with SUMIFS, INDEX and MATCH, or paste the dynamic array output as values before sending it.

Not sure how to phrase the condition? Describe what you want to filter, sort or de-duplicate in plain English and XLsheetAI writes the dynamic array formula — or paste one you inherited and get it explained argument by argument, including why it is spilling where it is.

Bottom line

Learn three things and the rest follows: a formula can return a range, that range spills from one anchor cell, and # lets you refer to the whole of it. FILTER handles the rows, UNIQUE handles the duplicates, SORT and SORTBY handle the order, and nesting them replaces most of what helper columns used to do.

Leave room below and to the right of anything that spills, add the if_empty argument before you share a file, and check who has to open it — because in Excel 2019 none of this exists.