"Array Result Was Not Expanded" Error in Google Sheets
What it means: Your formula produces multiple cells of output, but at least one of the cells it needs to fill already contains a value, so Sheets refuses to overwrite it.
Quick fix
=UNIQUE(A2:A100)
Click the formula cell, then clear everything below it in that column. Once the spill range is empty the results appear automatically.
Why you see Array result was not expanded — and how to fix each cause
1. Cells in the spill range are not empty
If =UNIQUE(A2:A100) sits in C2 but C5 already has text, the array can't expand past C4.
Fix: Delete the blocking values (the error message names the exact cell, e.g. “would overwrite data in C5”).
2. Another formula's output overlaps this one
Two spilling formulas aimed at the same area collide — one wins, the other errors.
Fix: Move one formula so their output ranges no longer overlap.
3. A merged cell sits inside the spill range
Merged cells count as occupied and block expansion.
Fix: Unmerge the cells in the output area (Format → Merge cells → Unmerge).
Before and after
| Broken | Working |
|---|---|
=SORT(A2:B50) with old data still sitting in the rows below |
=SORT(A2:B50) after clearing the rows it needs to fill |
The results only appear once every cell in the output rectangle is empty.
How to stop it happening again
Put spilling formulas (ARRAYFORMULA, FILTER, UNIQUE, SORT, QUERY) at the top of an otherwise empty column, and don't type anything into the cells below them — they belong to the formula's output.
FAQ
Which cell is blocking the spill?
The error message names it directly — “would overwrite data in C5”. Go to that cell and clear it.
Does this happen with FILTER and QUERY too?
Yes. Any function that returns more than one cell (FILTER, QUERY, SORT, UNIQUE, ARRAYFORMULA) needs an empty output area.
→ Google Sheets formulas cheat sheet — 34 copy-paste formulas