Fix SORT and UNIQUE #REF! in Google Sheets (spill cannot expand)

SORT and UNIQUE return arrays. Sheets writes every cell of that array starting at the formula. If a cell in that path already has a value, the whole result is #REF!. The formula is fine. The spill is blocked.

This page is a blocked spill. It is not after you delete (#REF! inside the formula text) and not VLOOKUP bad index or HLOOKUP #N/A / #REF! (an index past the lookup range). A one-column UNIQUE blocked from below is the same error; that recreate is UNIQUE spill.

Recreate it on a blank sheet

1. Open a blank spreadsheet. In A1 type fruit. A2 banana. A3 apple. A4 banana.
2. In C1 type sort. In C3 type the word block. Leave C2 empty.
3. In C2 enter =SORT(A2:A4). The cell shows #REF!. The tooltip is Array result was not expanded because it would overwrite data in ‘C3’.

C2 selected. Formula bar shows =SORT(A2). C2 is #REF!. C3 is block. Tooltip: Array result was not expanded because it would overwrite data in 'C3'.

Leave C2 as the broken formula.

Second recreate — blocked to the right. In E1 type sku, F1 name. E2 101, F2 Widget. E3 101, F3 Widget. E4 102, F4 Gadget. In G1 type unique. In I2 type block. Leave H2 empty. In H2 enter =UNIQUE(E2:F4). The cell shows #REF!. The tooltip is Array result was not expanded because it would overwrite data in ‘I2’.

H2 selected. Formula bar shows =UNIQUE(E2). H2 is #REF!. I2 is block. Tooltip: Array result was not expanded because it would overwrite data in 'I2'.

Leave H2 as the broken formula.

What is actually wrong

• =SORT(A2:A4) returns three cells, sorted: apple, banana, banana. It needs C2, C3, and C4. C3 already holds block. Sheets will not overwrite it, so C2 stays #REF!.
• =UNIQUE(E2:F4) returns two rows and two columns: 101 / Widget, then 102 / Gadget. It needs H2:I3. I2 already holds block. The spill is blocked to the right, not only down.
• A note, a space, or another formula in the path counts as occupied. The cell has to be empty. The source list in A and E:F is fine. The formula text is still SORT and UNIQUE with real ranges.
• FILTER, QUERY, and ARRAYFORMULA can hit the same overwrite. The tooltip is the same. This page is SORT and UNIQUE.

This is not a deleted reference. After a delete, #REF! appears inside the formula bar (=A1+#REF!). See after you delete. C2 and H2 still name A2:A4 and E2:F4.

This is not VLOOKUP or HLOOKUP asking for an index the range does not have. Those formulas look like =VLOOKUP(D2,A2:B2,3,FALSE) or =HLOOKUP(E3,B1:C2,3,FALSE). The tooltip is an out of bounds range. Nothing is spilling. See VLOOKUP bad index and HLOOKUP #N/A / #REF!.

This is not a circular formula. A loop still shows the original cell names and the tooltip is Circular dependency detected. See circular dependency.

Fix

Leave C2 and H2 broken so the overwrite stays on the sheet. Put each fix on empty cells.

Place the formula where it can spill. In J1 type sort_ok. Leave J2:J4 empty. In J2 enter =SORT(A2:A4). The column spills apple, then banana, then banana. In L1 type unique_ok. Leave L2:M3 empty (do not put a value in M2). In L2 enter =UNIQUE(E2:F4). The array spills 101 / Widget in L2:M2 and 102 / Gadget in L3:M3.

L2 selected. Formula bar shows =UNIQUE(E2). L2 is a two-column spill: 101 Widget, then 102 Gadget. J2 is apple, banana, banana. C2 and H2 are still #REF!.

Clear the blockers if the formula must stay where it is. The occupied cell is C3 for SORT and I2 for UNIQUE. Delete that value — do not delete the row. The original formula spills. Undo or retype block if you want the #REF! back. On this sheet, leave C3 and I2 filled so C2 and H2 stay #REF! next to the working spills.

Do not change the SORT or UNIQUE formula to “fix” the error. The functions are not wrong. The path is occupied.

Do not wrap C2 or H2 in IFERROR. That prints a blank and never spills. See IFERROR vs IFNA.

If #REF! is a spill overwrite, clear the cells in the path or move the formula onto empty cells. Read the formula bar. If you still see SORT or UNIQUE with a real range, this is the error.