Excel dynamic arrays: FILTER, SORT, UNIQUE and the spill range

Spilling

A dynamic array formula is entered in one cell, and its results spill into the cells below and to the right. Only the top-left cell holds the formula; the rest of the spill range is read-only and updates automatically when the source data changes.

Dynamic arrays are available in Microsoft 365 and Excel 2021 and later.

The core functions

• FILTER(array, include, [if_empty]): returns the rows that meet a condition, for example =FILTER(A2:C100, C2:C100>1000, "None").
• SORT(array, [sort_index], [sort_order]) and SORTBY: return the data sorted, leaving the source unchanged.
• UNIQUE(array): returns the distinct values, for example a list of every region with no duplicates.
• SEQUENCE(rows, [columns], [start], [step]): generates a list of numbers.

Fixing #SPILL!

#SPILL! means the results have nowhere to go. The usual cause is something already in the spill range, even a single space; clear it and the formula spills. Spilling is also not allowed inside an Excel table, so place the formula outside the table.

Referencing a spill range

Add # after the top-left cell to refer to the whole spilled result, however large it grows. If UNIQUE spills into E2, =COUNTA(E2#) counts the list and =SORT(E2#) sorts it, and both keep up as the data changes.