Excel Dynamic Arrays: FILTER, UNIQUE, SORT and the #SPILL! Error
Dynamic array formulas enter in one cell and fill as many cells as the result needs — automatically. They replaced dozens of complex workarounds with single, readable formulas.
The Key Idea
Enter =UNIQUE(B2:B100) in one cell — Excel fills as many cells below it as there are unique values. No dragging. No Ctrl+Shift+Enter. The result updates automatically when source data changes. That blue outlined range is called the spill range.
The 6 Core Dynamic Array Functions
FILTER=FILTER(array, include, [if_empty])Returns only the rows that meet a condition. The output adjusts automatically as data changes.
=FILTER(A2:C100, B2:B100="North", "No results")Returns all rows where column B is "North". Shows "No results" if nothing matches.
UNIQUE=UNIQUE(array, [by_col], [exactly_once])Returns a list of unique values from a range. Replaces the Remove Duplicates workflow for generating dropdown lists and unique summaries.
=UNIQUE(B2:B100)Returns each distinct value in column B — one occurrence per value.
SORT=SORT(array, [sort_index], [sort_order], [by_col])Returns a sorted version of a range without touching the original data.
=SORT(A2:B100, 2, -1)Sorts by column 2 (B), descending (-1). Original data is unchanged.
SORTBY=SORTBY(array, by_array1, sort_order1, ...)Sorts by one or more columns that do not have to be in the output. More flexible than SORT for multi-key sorting.
=SORTBY(A2:C100, C2:C100, -1, B2:B100, 1)Sort by column C descending, then column B ascending.
SEQUENCE=SEQUENCE(rows, [cols], [start], [step])Generates a sequence of numbers — useful for creating numbered lists, date series, and table headers dynamically.
=SEQUENCE(12, 1, DATE(2026,1,1), 30)Generates 12 dates starting 1 Jan 2026, 30 days apart.
XLOOKUP (spill version)=XLOOKUP(lookup, lookup_array, return_array)When the return_array spans multiple columns, XLOOKUP spills the result across columns automatically — no separate formula per column needed.
=XLOOKUP(G2, A2:A100, B2:D100)Returns all three columns (B, C, D) for the matching row in a single formula.
Referencing a Spill Range
To reference all cells in a spill range from another formula, use the spill operator (#) after the top-left cell of the spill:
=COUNTA(C2#)— counts all values in the spill from C2=SUM(D2#)— sums the entire spill range from D2=SORT(C2#)— sorts the spill range from C2
The # operator makes the reference dynamic — as the spill grows or shrinks, the referencing formula adjusts automatically.
Combining Functions: Filter Then Sort
Dynamic array functions compose cleanly:
=SORT(FILTER(A2:C100, B2:B100="North"), 3, -1)
This filters for "North" rows, then sorts the result by column 3 descending — all in one formula, no helper columns needed.
Fixing the #SPILL! Error
A #SPILL! error means something is blocking the output cells. Click the cell to see the blue outline showing where the formula needs space.
Cells in the spill range contain data
Clear all cells in the highlighted blue spill area. Include cells that appear blank — they may contain spaces.
Cells contain empty strings (="")
Empty strings are not truly blank. Select the spill range → Find & Select → Go To Special → Constants → Text — delete them.
Formula is inside a merged cell
Dynamic arrays cannot spill from merged cells. Unmerge the cell first.
Spill range is inside a Table
Dynamic arrays cannot spill inside an Excel Table structure. Enter the formula outside the table.
Volatile reference that changes size each calculation
Using INDIRECT or OFFSET in the formula can cause an indeterminate spill size. Rewrite to use a fixed or Table reference instead.
Frequently Asked Questions
What is a dynamic array in Excel?
A formula entered in one cell that automatically fills (spills) its results into as many cells as needed. The spill range adjusts automatically when source data changes.
How do I fix the #SPILL! error in Excel?
Click the error cell, look for the blue outline showing the required spill area, and clear anything blocking it — including cells with spaces or empty strings.
Are dynamic arrays available in all Excel versions?
FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, and RANDARRAY are Excel 365 and Excel 2021 only. Not available in Excel 2019 or earlier.
Related Guides
XLOOKUP Guide
XLOOKUP spills multiple columns automatically — the most useful dynamic array behaviour.
Read →
Excel Formulas Hub
All essential formula categories — dynamic arrays sit alongside the classics.
Read →
Fix #N/A Errors
Dynamic array lookups return #N/A when nothing matches — how to handle it.
Read →
Excel Tables Guide
Tables and dynamic arrays work together — FILTER on a table auto-expands.
Read →