Fix the #SPILL! Error

Excel & Google Sheets

How it works

Dynamic array functions like UNIQUE, FILTER, and SORT return many values from one formula, spilling them into the cells below and to the right. #SPILL! means that landing zone isn't empty — even one occupied cell in the way blocks the whole result. The occupant is often invisible: a lone space, an old note, or a leftover formula. In Excel, click the #SPILL! cell and a dashed border outlines the exact range the formula needs; find the non-empty cell inside it and clear it. The formula then spills instantly with no edits needed. A second cause is asking for more room than exists: =UNIQUE(A:A) on a whole column tries to spill a million rows, so reference a bounded range like A2:A20 instead.

UNIQUE(A2:A20)

Returns every distinct value in the range, spilling one per row below the formula.

The spill range

Click the #SPILL! cell — the dashed outline (Excel) shows exactly which cells must be empty. Clear whatever occupies them.

A2:A20, not A:A

A bounded range keeps the spill a predictable size. Whole-column references can demand more rows than the sheet has room for.

When to use it

Expect #SPILL! whenever you drop a dynamic array formula into a sheet that already has content nearby — dashboards, shared trackers, or any column with stray notes below the data.

Common mistakes

• ✕Deleting the formula and typing results by hand.

  ✓The formula is fine — its landing zone isn't. Clear the blocking cells (check for invisible spaces with =LEN) and the spill completes.

• ✕Referencing an entire column like =UNIQUE(A:A).

  ✓That asks for up to a million spill rows and can also drag a header like "Name" into the results. Use =UNIQUE(A2:A20).

• ✕A stray @ in front of the function.

  ✓=@UNIQUE(A2:A20) is the legacy implicit-intersection operator — it forces a single value instead of a spill. Remove the @.