Fix the Excel #SPILL! error: 6 dynamic array causes

You type =XLOOKUP(A2:A50,Products!A:A,Products!C:C), hit Enter, and Excel returns a single red #SPILL!. The formula is correct. The workbook is fine. Yesterday the same pattern returned a clean column of prices. So what changed?

The short answer: Excel wanted to spill the result across many cells, and something in the destination refused to move over. That is the entire contract of a dynamic array, and it is also the entire cause of every #SPILL! error you will ever chase. Six situations trip it, and each has a repeatable fix.

What #SPILL! actually means

Since the Excel 365 dynamic array engine shipped, a single formula can return an entire array — a column, a row, or a two-dimensional block. Excel writes the first value into the cell you typed the formula in, then spills the rest into the neighboring cells to the right and below. Only the top-left cell holds the formula; the others are read-only outputs bound to it.

That behavior needs somewhere to write. If any cell in the spill range is occupied, merged, hidden inside a table, or off the edge of the sheet, Excel refuses to overwrite it and shows #SPILL! instead. The error is not a formula bug — it is a real-estate dispute.

The one-formula, many-results mental shift

Old Excel needed Ctrl+Shift+Enter to force array behavior. New Excel treats it as the default. That flip is why formulas that ran fine as =VLOOKUP(A2,...) can suddenly spill when you paste them into a workbook that already had rows of data below. You didn’t change the formula. Excel changed what a range reference means when it sees more than one value on the left.

Cause 1: something is already in the spill range

The most common trigger: the cells below (or beside) your formula are not empty. Even a single stray character somewhere in the target zone blocks the whole spill.

Say you write =SORT(A2:A6) in C2. Excel wants to write five sorted values into C2:C6. If C4 already contains an old note, spill fails and C2 shows #SPILL!.

Click the yellow warning icon next to the error and pick Select Obstructing Cells. Excel jumps to C4. Delete the content, and the spill resolves in the same keystroke.

Hidden characters — the LEN() diagnosis

Sometimes the “obstructing” cell looks empty. A rogue space, a stray non-breaking character, or a line break left by a paste from a browser is enough. Excel treats it as content and refuses to spill over it.

Put =LEN(C4) next to the suspect cell. Zero means truly empty. Anything above zero — even 1 — means an invisible character. Select the spill range, press Ctrl+H, leave the “Replace with” box blank, and replace a single space to clear the ghosts. If LEN still returns a positive number, the character is a non-breaking space (CHAR(160)) — paste =CHAR(160) into the “Find what” box and replace again.

Cause 2: the spill would land inside an Excel Table

Excel Tables (the ones you create with Ctrl+T) do not support spilling. Tables assume one formula per row, copied down structurally — the exact opposite of a single formula that expands across many rows on its own. Drop a dynamic array formula into a table column and you get #SPILL! in every row, sometimes with the wording “spilled array formulas aren’t supported in Excel tables.”

Two clean options exist. Which one you pick depends on whether you want to keep the surrounding data as a Table.

Inside Table column:
=UNIQUE(Orders[Region])
→ #SPILL!

Outside the Table (e.g. G2):
=UNIQUE(Orders[Region])
→ spills clean

Convert to range vs. move the formula

If the block only exists to give you column names, you can select the Table and click Table Design → Convert to Range. The headers stay, the styling stays, and the range now accepts spilled formulas. If you still want the Table’s structured references and totals row, leave it alone and put the dynamic array formula on a scratch area beside or below the Table instead. Reference the Table columns as usual — Orders[Amount] works whether the referring cell is inside a Table or not.

Cause 3: merged cells and unpredictable sizes

Merged cells sabotage spills two ways. The obvious way is when the spill range crosses a merged block — Excel refuses to write into any part of it. The subtle way is when the merged cell is a formatting decision made by whoever built the template three years ago, and it looks like a single tidy row.

Select the entire potential spill range, open Home → Merge & Center, and click Unmerge Cells. If the visual layout mattered, replace the merge with a “Center Across Selection” alignment — same look, no merged blocks, no #SPILL!.

Cause 4: the spill would run off the worksheet

Excel worksheets stop at row 1,048,576 and column XFD. A dynamic array cannot spill past that boundary. This sounds theoretical until you write =VLOOKUP(A:A, Table, 2, FALSE) — the first argument is a full column, which forces the result to be a full column, starting from wherever you typed the formula. Type it in row 10, and Excel needs a million rows of output starting there. It runs off the sheet in the last few hundred and refuses to spill any of it.

The fix is to constrain the input to a real range. =VLOOKUP(A2:A5000, Table, 2, FALSE) caps the output at 4,999 rows, which fits. Or convert the lookup source into a Table and use =VLOOKUP(Table1[Key], Table2, 2, FALSE) — the structured reference is exactly the used range, no longer than it needs to be.

Cause 5: unknown or unrecognized size

Some formulas cannot be sized at parse time. The classic example is any expression that mixes a dynamic array with an operator Excel doesn’t know how to broadcast, or a lookup where the return array has a different shape than the lookup array. Excel gives up and shows #SPILL! with the message “Excel ran out of resources” or “Unrecognized/Fallback.” The formula is not necessarily wrong — Excel just cannot pre-allocate space for the result.

Break the formula in half. Compute the array in one cell, then reference it from the second with the spilled-range operator #. If cell E2 holds =UNIQUE(Orders[Region]), then =SORT(E2#) in F2 takes whatever E2 spilled and sorts it, with no ambiguity about size.

E2:  =UNIQUE(Orders[Region])
F2:  =SORT(E2#)
G2:  =COUNTIF(Orders[Region], E2#)

Every reference to E2# reads whatever E2‘s live spill range is right now. When the source data changes and the unique list grows, the downstream formulas grow with it. This is the same technique used across dynamic dashboards in Excel, where one lookup drives several derived columns.

Cause 6: you actually wanted a single value

Sometimes the spill itself is the mistake. You wrote =A2:A50 * 1.1 when you meant “the value in this row, times 1.1” — for a formula you plan to copy down. Excel’s dynamic array engine sees a range, spills 49 results, and stomps on whatever was below.

The @ operator forces the old single-value behavior. Prefix the range with it: =@A2:A50 * 1.1. Excel now returns one value per row and copies down like a legacy formula. This is called implicit intersection, and it is the correct fix whenever you want row-by-row math instead of a single spill.

=A2:A50 * 1.1
→ spills 49 rows,
   overwrites B3:B50

=@A2:A50 * 1.1
→ one value in B2,
   copy down as before

The reverse trick is useful too. If a legacy formula returns only the first value when you expected a full array, wrap it in a modern equivalent (SORT, FILTER, UNIQUE) or drop the leading @.

A repeatable diagnosis flow

When a spill error appears, work the causes in order. The first three catch 90% of cases in seconds, and only take a few clicks each. Microsoft’s own reference walks through the same categories in more depth on the #SPILL! error page.

1. Click the warning icon and pick Select Obstructing Cells. Delete whatever is in the way.
2. If the cells look empty, run =LEN(range) on the spill zone. Any positive count means hidden text — clear it.
3. Check whether the formula sits inside an Excel Table. If yes, move it out or convert the Table to a range.
4. Look for merged cells in the target zone. Unmerge everything, then reformat with Center Across Selection if you need the look back.
5. Check whether the input references a full column or row. Cap it at the used range.
6. If you never wanted a spill, put an @ in front of the range reference.

Following the same six checks on every error means you stop guessing. Once the muscle memory is there, #SPILL! becomes one of the fastest Excel errors to fix — much faster than the more ambiguous ones covered in the Excel errors troubleshooting guide.

What “resources exceeded” really means

A rare variant of #SPILL! reads “Excel ran out of resources” and points at a formula that references the entire used range across multiple sheets. This one really is about memory: Excel is trying to allocate an array too big to hold. The fix is the same as Cause 4 — cap the input to the range you actually need, and split the calculation into a helper column with a spilled-range reference. Chunking the work almost always solves it, because dynamic arrays only allocate for the used shape.

Wrap up

Every #SPILL! error is Excel asking for room to write. Give it room — clear the obstructing cells, move it out of the Table, unmerge, cap the input, or opt back into single-value math with @ — and the formula runs. The engine did not change under you; the meaning of a range reference did.

• ✓ Spill target is fully empty (check with LEN)
• ✓ Formula sits outside any Excel Table
• ✓ No merged cells in the spill range
• ✓ Input references a capped range, not A:A
• ✓ Prefix with @ only when a single value is intended

Pick one error you saw this week and walk it through the checklist. The pattern is yours after two or three passes.