Diagnose an Excel #SPILL! error with AI

You put =UNIQUE(A2:A6) in D2 and get #SPILL!. Everything below D2 looks empty. An AI cannot see your sheet, so this page is about what to tell it and what to check yourself.

The sheet and the cases

The CSV below is a made-up sheet. The first column is the row number, and D4 holds a single space. On screen it looks empty.

,A,B,C,D,E1,Region,Item,Qty,Unique regions,2,East,Pen,10,,3,West,Pen,5,,4,East,Ink,7, ,5,North,Pen,2,,6,West,Ink,9,,

UNIQUE(A2:A6) returns three values, so D2:D4 must be empty, and D4 is not. Two more common cases go into the same question.

The prompt

Instructions, context and input are split into tags, as Anthropic's prompting guide recommends, so the cases cannot be mistaken for instructions. The <context> block says why: knowing that a wrong guess costs an hour pushes the model to separate what it knows from what it assumes.

<instructions>A user gets #SPILL! in Excel (Microsoft 365). Below are three cases. For each case give: (1) the most likely cause in one sentence, (2) what you cannot know from the description, (3) one check the user can run in the sheet, naming the cells or formula to use.Do not invent cell contents. If a cause is only a guess, say "guess". No other text. Max 4 lines per case.</instructions> <context>I cannot tell by eye what is wrong, and I will apply your advice to the real file. A wrong guess costs me an hour, so separate what you know from what you assume.</context> <input>Case 1: D2 contains =UNIQUE(A2:A6). It shows #SPILL!. The cells below D2 look empty. Nothing else is in column D except the header in D1. Case 2: The data is an Excel Table named tblSales (columns Region, Item, Qty). In a new column of the table, row 1 has =FILTER(tblSales[Item],tblSales[Qty]>5). It shows #SPILL! in every row of that column. Case 3: In C2 of a normal range I typed =A:A*2 (whole-column reference). It shows #SPILL!. Row 1 holds headers.</input>

Case 1 deliberately does not say "D4 has a space". Someone stuck on the real problem does not know that either.

The result

Run twice with claude -p --model claude-sonnet-5-5 --max-turns 1 --tools "" --setting-sources "" --strict-mcp-config. The first answer, abridged:

Case 11. Cause (guess): something invisible sits in D3:D6, such as a space, a formula returning "", or a merged cell, so the 5-row spill D2:D6 is blocked.2. Unknown: which cell blocks it, and what it contains. I also can't tell whether UNIQUE returns fewer than 5 rows.3. Check: select D2, click the warning icon, and choose "Select Obstructing Cells". Or enter `=COUNTA(D3:D6)` in F1. Anything above 0 confirms a blocker, but merged cells won't be counted. Case 21. Cause: Excel Tables don't allow spilling formulas, so FILTER in a table column returns #SPILL! regardless of the data.…3. Check: enter `=FILTER(tblSales[Item],tblSales[Qty]>5)` in an empty cell outside the table with free cells below it. If it spills there, the table was the cause. Case 31. Cause: `=A:A*2` returns 1,048,576 values, so starting in row 2 it runs past the last worksheet row.…3. Check: enter `=ROW(C2)+ROWS(A:A)-1` in an empty cell. A result of 1048577 exceeds the 1048576-row limit and confirms the overflow. Then replace C2 with `=A2:A1000*2` (use your real last row) to see it spill.

• Case 1 was marked "guess" in both runs, Case 2 was stated as known behavior in both. Case 3 was stated flatly in both.
• The first answer assumed UNIQUE returns five rows ("the 5-row spill D2:D6"); the input never said how many distinct values A2:A6 holds, and the answer itself listed that as unknown. It returns three here.
• Case 1's check differed: the first run offered =COUNTA(D3:D6), the second told the user to click D2, use Formulas > Error Checking > Select Obstructing Cells, or select D3:D6 and press Delete. The COUNTA check works for a space and for a "" formula (both count as non-empty), which is more than the second run's advice tells you.

Find the invisible blocker yourself

The AI cannot see the space in D4, so it offers candidates. You confirm in the sheet.

[1] Put =LEN(D3) in E3 and drag it down to E6. Putting it in column D would block the spill again. Any row that is not 0 is a blocker. Here E4 is 1, so the blocker is D4.

[2] Delete that cell with the Delete key. Remove the temporary formulas too.

[3] LEN catches a space, but a formula that returns an empty string ("") also gives 0 and slips through. If every LEN is 0 and the error stays, look for a formula in the cell. "Select Obstructing Cells" from the error indicator does the same job.

[4] After the fix, count the rows that spilled:

=ROWS(UNIQUE(A2:A6))

If it returns 3 and D2:D4 are filled, you are done.

The same check on the CSV export, with a script (spillcheck.py, which measures cell length the way LEN does). It reads the CSV, not Excel.

$ python3 -I spillcheck.py sheet.csv D2 A; echo "exit $?"UNIQUE returns 3 rows -> needs D2:D4BLOCK D4: LEN=1 value=' '1 blocking cellsexit 1

On sheet-fixed.csv, with the space in D4 removed, it prints 0 blocking cells and exits 0.

Cases 2 and 3

Move the table formula to an empty cell outside the table (or convert the table to a range under Table Design > Convert to Range). For Case 3, reference only the real data, for example =A2:A100*2.

Where it goes wrong

• Both runs gave the same cause per case; only the wording of the check differed. Case 3's check was =A2:A100*2 or =A2:A1000*2 in different runs. The number is arbitrary, so replace it with your last row.
• Leave out "it is inside a table" and Case 2 drops off the candidate list. If the formula is in a table, say so.
• After pasting any formula the AI gives you, compare =ROWS(UNIQUE(…)) with the number of rows that actually spilled.