Dynamic arrays let one Excel formula return many results across neighboring cells. Instead of copying a formula down, Excel spills the answer into as many rows or columns as needed.
Does this affect you?
Use this if a formula fills several cells automatically, or if you want to learn FILTER, SORT, UNIQUE, and SEQUENCE in Excel for Microsoft 365 or Excel for the web. Excel 2019 and older versions do not include the modern dynamic array engine.
The four dynamic array functions worth learning first
Enter each formula in one cell and let Excel spill the results.
- UNIQUE: =UNIQUE(A2:A100) returns each distinct value once.
- SORT: =SORT(A2:B100,2,-1) sorts the range by the second column from largest to smallest.
- FILTER: =FILTER(A2:C100,B2:B100=”Open”) returns only rows where column B says Open.
- SEQUENCE: =SEQUENCE(12) generates 1 through 12 automatically.
Combine dynamic functions
Dynamic arrays become more useful when stacked together. For example, =SORT(FILTER(A2:C100,B2:B100=”Open”),1) first filters the table to open rows, then sorts the result by the first column.
Fix a #SPILL error
#SPILL means Excel cannot place the full result.
- Click the formula cell showing #SPILL.
- Open the warning icon if available.
- Choose Select Obstructing Cells to find what is blocking the output.
- Clear or move the blocking values, spaces, merged cells, or old formatting.
- The formula will spill normally when the output area is free.
More control
Reference the whole spill range
Add # after the first cell reference. If the formula is in A1, use A1# elsewhere to refer to the entire spilled result as it grows or shrinks.
Build quick counts
Put =UNIQUE(A2:A100) in one column, then use =COUNTIF(A2:A100,B2#) beside it to count each unique item without a full pivot table.
Sources
- Microsoft Support – Dynamic arrays and spilled array behavior (2025)
- Microsoft Support – FILTER function (2025)
- Microsoft Support – UNIQUE function (2025)