Goal
You’ll understand what dynamic array formulas are and how they genuinely differ from traditional Excel formulas, specifically in how they “spill” results across multiple cells automatically.
Learn
Traditional Excel formulas return exactly one value into exactly one cell. Modern versions of Excel introduced dynamic array formulas, which can return multiple values that automatically “spill” into the neighboring cells below and to the right — with no need to manually copy or AutoFill the formula into each cell individually.
=UNIQUE(A1:A100)
Placed in just one cell, this single formula automatically spills a complete list of every unique value found in A1:A100 downward into as many cells as needed — you type the formula once, in one cell, and Excel automatically fills in exactly as many rows as the actual result requires.
=SORT(A1:A100) =FILTER(A1:A100, B1:B100>50)
SORT spills a sorted version of a range; FILTER spills only the rows matching a specified condition — both automatically sizing their spilled output to match however many results actually exist, growing or shrinking automatically as the underlying source data changes.
A genuinely important detail: since a dynamic array formula only exists in the single cell where you actually typed it (the spilled cells below/right are just displaying that one formula’s overflow, not separate formulas of their own), you can’t type anything into a cell that a spill is currently occupying — doing so causes a #SPILL! error, since Excel can’t complete the spill if something is already blocking one of the cells it needs to expand into.
Decision Task
You type =UNIQUE(A1:A50) into cell C1, and it correctly spills 12 unique values down through C12. Before reading on: what would happen if you then typed something directly into cell C5, in the middle of that spilled range?
Show Answer
The UNIQUE formula in C1 would show a #SPILL! error, since Excel can no longer complete the full spill — cell C5 is now occupied by your manually typed value, blocking the formula from filling that specific cell as part of its automatic output range. You’d need to clear C5 first for the spill to work correctly again.
Common Mistake
Typing data directly into cells that a dynamic array formula is currently spilling into, not realizing those cells are actually part of one single formula’s automatic output, not independent empty cells available for separate use. This causes a #SPILL! error, since the formula can no longer complete filling its required output range.
Practice Questions
1. What does the UNIQUE function do when applied to a range?
Show Answer
Automatically spills a list of every distinct unique value found in that range, into as many cells as needed.
2. What does FILTER(A1:A100, B1:B100>50) do?
Show Answer
Spills only the rows from A1:A100 where the corresponding value in B1:B100 is greater than 50.
3. Why does typing a value directly into a cell that a spill formula is using cause a #SPILL! error?
Show Answer
The formula can no longer complete filling its required output cells, since one of them is now occupied by manually entered data blocking the automatic spill.
4. True or False: each cell in a spilled range (below the cell where the formula was actually typed) contains its own separate, independent copy of the formula.
Show Answer
False — only the original cell contains the actual formula; the spilled cells are simply displaying that one formula’s overflow result, not separate formulas of their own.
5. What genuinely useful behavior do dynamic array formulas provide compared to traditional single-cell formulas?
Show Answer
They can return and automatically display multiple values across several cells from a single formula entered in just one place, without needing to manually copy or AutoFill into each cell individually.
Try It Yourself
Without looking back, explain in your own words why you can’t simply type new data into a cell that’s currently part of a dynamic array formula’s spilled output range.
Show Answer
That cell isn’t actually empty or independent — it’s displaying the automatic overflow of a single formula typed in a different cell; typing directly into it blocks the formula from completing its required spill, causing a #SPILL! error, since the formula genuinely needs that cell to remain available for its own automatic output.
Quick Check
1. What do dynamic array formulas do differently from traditional formulas?
Show Answer
They can return multiple values that automatically spill into neighboring cells, rather than returning just one value into one cell.
2. What does UNIQUE do?
Show Answer
Spills a list of every distinct unique value found in a range.
3. What does SORT do?
Show Answer
Spills a sorted version of a range.
4. What error appears if something blocks a formula’s spill range?
Show Answer
#SPILL!
5. Does a spilled cell (not the original formula cell) contain its own independent formula?
Show Answer
No — it’s displaying the overflow of the one formula typed in the original cell.