Dynamic Array Functions FILTER SORT and UNIQUE Explained

For years, getting a filtered list in Excel meant an autofilter, a copy, a paste and the knowledge that it would be stale by Monday. Dynamic array functions replaced that routine with a formula. Type =FILTER(...) in one cell and the matching rows appear below, and when the source changes they update. This article covers FILTER, SORT, SORTBY and UNIQUE, how they combine, how to read their errors and what happens to them when a program reads the file. It belongs with Excel formulas data people use every week and extends the lookup patterns in XLOOKUP examples that replace VLOOKUP pain.

How spilling works

A dynamic array formula returns more than one value. The first value goes in the cell where you typed the formula, and the rest spill into the cells below and to the right. The block they occupy is the spill range, outlined in blue when you select a cell in it. You cannot overwrite the spilled cells directly; you change the formula in the top left cell.

If something already sits in the way, the formula shows #SPILL!. Clear the obstacle, which can be a value, a merged cell or a table (spills do not work inside Excel tables). To refer to the whole spilled result from elsewhere, add a hash after the anchor cell: =SUM(F2#) sums everything in the spill range starting at F2, and it grows or shrinks with the result.

Sample data in A1:D8, with headers in row 1:

FILTER

=FILTER(A2:D8, A2:A8="North", "no rows")

The first argument is the data to return, the second is the include test, an array of TRUE and FALSE values with the same number of rows as the data, and the third is what to show when nothing matches. The result is the three North rows. Without the third argument, an empty result produces #CALC!, so supply one wherever the condition can match nothing.

Conditions combine with multiplication for AND and addition for OR:

=FILTER(A2:D8, (A2:A8="North")*(D2:D8>=1000), "none")
=FILTER(A2:D8, (A2:A8="North")+(A2:A8="East"), "none")

The first returns North rows with a value of at least 1,000. The second returns North and East rows. Wrap the include test in parentheses; operator precedence otherwise gives surprising results. To return only some columns, point the first argument at them: FILTER(B2:B8, A2:A8="North"), or use CHOOSECOLS on the result for a custom order.

Compare the SQL form, SELECT * FROM sales WHERE region = 'North' AND value >= 1000. The formula is a WHERE clause that lives in a cell.

SORT and SORTBY

=SORT(A2:D8, 4, -1)

SORT takes an array, a sort index (the column number within the array), and an order, 1 for ascending (the default) or -1 for descending. This sorts by the fourth column, largest first. A final argument TRUE sorts by rows instead of columns, which is rarely wanted.

SORTBY sorts one array by another, which need not be part of it:

=SORTBY(A2:D8, A2:A8, 1, D2:D8, -1)

That orders by region ascending, then by value descending within each region. SORTBY is the one to use for multi-key sorts and for sorting by a computed column, such as SORTBY(A2:D8, LEN(B2:B8)). Sorting is stable for ties, and text sorts according to the system locale, which can differ between machines.

UNIQUE

=UNIQUE(A2:A8)

returns the distinct regions in order of first appearance: North, South, East. For multiple columns it works on whole rows, so UNIQUE(A2:B8) returns distinct region and customer pairs. The second argument TRUE compares columns instead of rows. The third argument returns rows that occur exactly once:

=UNIQUE(B2:B8, FALSE, TRUE)

returns customers who appear once only (Cobalt and Delta here). This answers "who bought only once" without a pivot. The comparison is case-insensitive, which matches Excel's usual text behavior.

The SQL forms are SELECT DISTINCT for UNIQUE, and GROUP BY ... HAVING count(*) = 1 for the exactly-once variant.

Combining them

The power comes from nesting, because each function takes an array and returns one.

=SORT(UNIQUE(FILTER(B2:B8, A2:A8="North")))

reads inside out: take the customers in the North region, remove duplicates, sort them. The result is Acme and Cobalt. A live "customers by region" list now needs no helper column.

A top-N list: =TAKE(SORT(A2:D8, 4, -1), 3) returns the three largest rows, where TAKE is one of the newer array-shaping functions. Without TAKE, use INDEX(SORT(...), SEQUENCE(3), {1,2,3,4}).

A dropdown that depends on another cell uses a spill reference in data validation: if F2 holds =SORT(UNIQUE(B2:B8)), then a list validation with the source =$F$2# always offers the current distinct customers.

For a total by region, UNIQUE pairs well with SUMIFS:

=LET(r, SORT(UNIQUE(A2:A8)), HSTACK(r, SUMIFS(D2:D8, A2:A8, r)))

The SUMIFS runs once per region because r is an array, and HSTACK puts the regions next to their totals. LET and HSTACK need a recent build (LET and LAMBDA for readable formulas). For older versions, put UNIQUE in one column and the SUMIFS in the next, referencing the spill with the hash.

Errors you will meet

Two more habits help. First, never type over a spill range; edit the anchor. Second, if a column of values arrives as text, FILTER comparisons to numbers silently return nothing, so clean types first.

What a script sees in the file

A spilled formula is stored in the file in two parts: the anchor cell holds the formula marked as a dynamic array, and each spilled cell holds a cached value. A library like openpyxl reads the anchor as a formula and, with data_only=True, returns the cached values in all cells. A library that writes formulas must mark the anchor as a dynamic array or Excel will treat the formula as a legacy array formula, or add an implicit intersection @ that collapses the result to a single value. That is why a formula written from a script sometimes shows =@FILTER(...) and returns one cell (array formulas and why they break imports explains the storage details).

If you need dynamic arrays in a generated file, test a round trip in Excel. If you only need the data, compute the result in code or SQL and write values.

When dynamic arrays are the wrong choice

They are the wrong choice for a workbook that goes to people on Excel 2019 or earlier, where every spill is #NAME?. They are the wrong choice inside Excel tables, which do not allow spills. They are slow on very large ranges recalculated often, because each dependency change recomputes the whole array. And they are the wrong place for logic that others must audit: a nested SORT(UNIQUE(FILTER(...))) is clear to its author and dense to everyone else. For a one-time clean-up, Power Query records steps and refreshes (Power Query for repeatable Excel cleanups). For anything large or scheduled, use SQL, where DISTINCT, WHERE and ORDER BY do the same work with indexes (removing duplicates in Excel and SQL). Microsoft's FILTER function reference and UNIQUE function reference list every argument.