Skip to content
Sheet ErrorSheet ErrorSheet Error

Array result was not expanded because it would overwrite data

UNIQUE can return #REF! even when the source list is sitting on the sheet. The formula is fine. Sheets will not spill into a cell that already has data.

Recreate it on a blank sheet

Section titled “Recreate it on a blank sheet”
  1. Open a blank spreadsheet. In C1 type apple. C2 apple. C3 banana.
  2. In A2 type the word block. Leave A1 empty.
  3. In A1 enter =UNIQUE(C1:C3). The cell shows #REF!. The tooltip is Array result was not expanded because it would overwrite data in ‘A2’.

A1 selected. Formula bar shows =UNIQUE(C1). A1 is #REF!. A2 is block.

What is actually wrong

Section titled “What is actually wrong”
  • UNIQUE returns an array. Here that is two cells: apple, then banana. It needs A1 and A2.
  • A2 already holds block. Sheets will not overwrite it, so the whole result stays #REF! in A1.
  • This is not a deleted reference, not a VLOOKUP column index past the range, and not IMPORTRANGE waiting for Allow access. The formula still points at C1:C3. The spill is blocked.

Fix

Section titled “Fix”

Do not change the UNIQUE formula.

Delete the value in A2 so that cell is empty. A1 stays =UNIQUE(C1:C3). The array spills: A1 is apple, A2 is banana.

A1 selected. Formula bar shows =UNIQUE(C1). A1 is apple and A2 is banana after A2 was cleared.

If #REF! is an overwrite, clear the cells in the spill path. Leave the formula where it is.