FILTER, SORT and UNIQUE: Dynamic Arrays in Excel

The FILTER function in Excel returns the rows of a range that meet a condition, spilling the results into the cells below automatically. SORT orders a range, and UNIQUE strips out duplicates. Together these dynamic array functions let you build live schedules from raw data with a single formula. They are available in Microsoft 365 and Excel 2021 and later, not in older versions.

How FILTER, SORT and UNIQUE spill

=FILTER(array, include, [if_empty]) returns every row of array where the matching entry in include is TRUE. The result spills into a range as large as it needs, outlined with a thin blue border. if_empty sets what to show when nothing matches, such as "None".

=SORT(array, [sort_index], [sort_order]) returns the array reordered, and =UNIQUE(array) returns the distinct rows. Because these are dynamic arrays, you write one formula in the top cell and Excel fills the rest.

You can reference a spilled range with the spill operator, a hash sign after the anchor cell. If a FILTER result starts in E2, then E2# refers to the entire spilled block, however many rows it currently holds. A #SPILL! error means something is blocking the cells the result needs.

Worked example: a live filtered schedule

Suppose A2:C20 holds a table of deals with Region in column A, Stage in column B, and Value in column C, and you want only the Closed deals, sorted by value.

1. In E2 write =FILTER(A2:C20, B2:B20="Closed", "None") to pull every Closed row; it spills down from E2.
2. Wrap it to sort by value descending: =SORT(FILTER(A2:C20, B2:B20="Closed"), 3, -1), where 3 is the Value column and -1 is descending.
3. To list the distinct regions present, write =UNIQUE(A2:A20) in another cell.
4. Sum the spilled values with =SUM(E2#), using the spill operator so the total tracks the block as it grows or shrinks.
5. When the source table changes, the filtered schedule and its total update on their own.

E2# refers to the entire spilled range, so the total adjusts as rows are added.

How analysts use them in models

Dynamic arrays replace fragile helper columns and manual refiltering with self updating views.

• Build a live deal or project schedule that filters a master table by status, owner, or date with one FILTER formula.
• Use UNIQUE to generate a clean list of categories that feeds a dropdown or a summary block.
• Use SORT to keep a leaderboard or top accounts list ordered without manual resorting.
• Reference spill ranges with the # operator so totals, charts, and dependent formulas resize automatically.
• Nest FILTER inside SORT or UNIQUE to filter first, then order or deduplicate, all in a single cell.

Pitfalls to watch

These functions only exist in Microsoft 365 and Excel 2021 and later. A workbook built with FILTER opened in Excel 2019 or earlier shows a _xlfn error and will not calculate. Confirm your audience's version before relying on them.

A #SPILL! error means the cells the result needs are not empty. Something, often a stray value or a merged cell, sits in the spill zone. Clear the obstruction and the array fills in.

Pointing other formulas at single cells inside a spill rather than at the spill range can break when the block resizes. Use the E2# spill reference so dependents follow the data instead of pointing at a fixed range that may no longer hold what you expect.