Fixing SORT/UNIQUE Returning #REF! Because Array Result Would Overwrite Data in Google Sheets

The Problem

You type a SORT() or UNIQUE() formula, hit Enter, and instead of a sorted list you get a red #REF!. Hovering over it shows:

Array result was not expanded because it would overwrite data in...

Google Sheets' array formulas — SORT, UNIQUE, FILTER, ARRAYFORMULA — all "spill" their output into adjacent cells automatically. The moment any of those cells contains something (even a lone space or a zero), Sheets refuses to proceed and throws #REF! instead.

Why This Happens

Say you enter =SORT(A2:A20) in cell C2. Sheets needs C2:C20 completely empty to write 19 sorted values. One stray entry in C5 — a label, a leftover number, anything — and the whole formula dies.

The usual suspects:

• Another formula or static value sitting inside the spill range.
• A stray space ( ) or leading apostrophe (') — the cell looks blank but isn't.
• A previous run of the same formula left behind values that never got cleaned up.
• Merged cells anywhere in the output area — Sheets cannot write into a merged block at all.

Quick Fix: Clear the Spill Range

Find what's blocking the output and delete it. That's it.

• Click the cell showing #REF!.
• A blue-bordered preview appears — it marks exactly where Sheets wants to write results.
• Select every cell inside that blue border, skipping the formula cell itself.
• Press Delete to wipe their contents.
• The formula resolves on its own.

Still broken after deleting? The blocking cell may hold invisible characters. Click it and check the formula bar — if it shows anything, even a single apostrophe, press Delete again to clear it properly.

Dealing with Merged Cells

Merged cells break spill formulas silently. The range looks empty, but the merge itself is the blocker. If clearing data didn't help, check for merges:

• Select the entire intended output range.
• Go to Format → Merge cells → Unmerge.
• Delete any content that reappears, then re-enter your formula.

Permanent Fix: Give the Formula Its Own Column

The real solution is isolation. Keep spill formulas in a column nothing else touches. Mixing dynamic output with static labels in the same column is asking for this error to return the moment your data grows.

Broken layout — formula and static data share column B:

| A           | B                 |
|-------------|-------------------|
| Raw data    | =SORT(A2:A10)     |  ← spill targets B2:B10
| more data   |                   |
| ...         | Header label      |  ← B5 blocks the spill!

Working layout — formula gets column C all to itself:

| A           | B (empty buffer) | C                 |
|-------------|-----------------|-------------------|
| Raw data    |                 | =SORT(A2:A10)     |
| more data   |                 |                   |
| ...         |                 |                   |

Column B acts as a visual buffer. Overkill? Maybe. But it prevents accidental overlap as your sheet grows.

Wrapping in IFERROR as a Safety Net

When the source data changes size constantly and a clear spill range isn't guaranteed, wrap the formula in IFERROR so other users see a message instead of a red error:

=IFERROR(SORT(A2:A100), "Output blocked — clear column C")

This doesn't fix the root cause. It just keeps the sheet looking clean while you sort out the layout conflict.

How Much Space Each Formula Needs

Every formula below spills downward from the cell where it's entered. Clear the entire output column before typing.

=SORT(A2:A50)                        ' needs up to 49 rows in output column
=SORT(A2:A50, 1, FALSE)              ' same range, descending order
=UNIQUE(B2:B100)                     ' up to 99 rows (deduplication reduces it)
=SORT(UNIQUE(B2:B100))               ' combined — still plan for 99 rows
=FILTER(A2:B50, C2:C50="active")     ' row count depends on how many match

Nesting SORT inside UNIQUE doesn't shrink the space requirement. Budget for the full source range length.

Verification Steps

Once the blocking cells are cleared:

• The #REF! cell updates immediately and shows the first value in the sorted or unique list.
• A light blue border frames the entire spill range — that's your confirmation the formula is running correctly.
• Click any cell inside the spill range (not the formula cell itself). The formula bar shows the formula dimmed in grey, meaning it's a spill result rather than an independent entry.
• Add a new value to the source range and watch the output adjust automatically.

If #REF! returns after editing somewhere else on the sheet, a different formula is probably growing and creeping into your output column. Press Ctrl+End to jump to the last used cell and audit what's near your spill area.