8 errors · Excel 365 only

Dynamic Array Errors

The new class of errors introduced with dynamic arrays in 2020. If you've never seen #SPILL! or #CALC! before but suddenly are — this page tells you exactly what's blocking your formulas.

💡 Why these errors exist

Dynamic arrays let one formula return many values that automatically spill into surrounding cells. That's the good news. The bad news: if anything is in the way of that spill, or if the calculation can't complete, Excel invents new error codes to explain what happened. #SPILL! and #CALC! are those codes — and they're specific to modern Excel.

These errors do not appear in Excel 2019 or earlier because those versions don't have dynamic arrays.

The two you'll see most

#SPILL!

Blocked spill range

Cause: Your dynamic formula wants to spill into surrounding cells, but something is there. Usually a value in one of the target cells, a merged cell in the range, or the spill would go past the sheet edge.

Fix: click the yellow icon → "Select Obstructing Cells" → delete or move them. Or move the formula to open space.
Full #SPILL! diagnostic guide →
#CALC!

Calc engine can't compute

Cause: Excel's calc engine ran into something it can't handle — an empty array, unsupported nested arrays, LAMBDA returning inconsistent types, or a recursive LAMBDA overflow.

Fix: check that FILTER results aren't empty, avoid arrays inside arrays, and add IFERROR wrappers on complex LAMBDAs.
Full #CALC! diagnostic guide →
#SPILL!

Merged cell blocking spill

Cause: One of the cells the formula wants to spill into is part of a merged group. Merged cells are incompatible with dynamic array spilling.

Fix: unmerge the blocking cells (Home → Merge & Center → Unmerge). Move formula if unmerging isn't an option.
Merged cell spill fix →
#SPILL!

Spill in an Excel Table

Cause: Dynamic array formulas cannot spill inside an Excel Table (Ctrl+T table). Tables have fixed column boundaries that conflict with unpredictable spilling.

Fix: convert the Table back to a range (Table Design → Convert to Range) OR move the dynamic formula outside the Table.
Table spill fix →

All 8 dynamic array errors

Diagnostic flow — which #SPILL! is it?

  1. Click the yellow warning icon next to the errored cell. Excel gives a plain-English hint about which specific cause it detected.
  2. Try "Select Obstructing Cells" from the dropdown — Excel jumps to the cells blocking the spill.
  3. If cells are highlighted: delete their contents. Formula should then work.
  4. If Excel can't identify obstruction: the spill is likely trying to go into merged cells, a Table, or past the sheet edge. Check the visual layout around the formula.
  5. If the source range is huge (like A:A — a full million-row column), Excel might return #SPILL! from memory constraints. Narrow the range.

Prevention patterns

  • Leave open space around dynamic formulas: if you know a FILTER might return 50 rows, don't put anything in the 50 rows below it
  • Avoid dynamic arrays inside Tables: if you need Table features, use SUMIFS / COUNTIFS or a Pivot Table instead
  • Unmerge cells throughout data areas: merged cells break many modern features, not just dynamic arrays
  • Use bounded ranges: A2:A10000 instead of A:A reduces memory pressure
  • Wrap in IFERROR when empty is possible: =IFERROR(FILTER(range, condition), "No matches") prevents #CALC! from empty results
  • Use spill range operator (#) to reference: =SUM(A1#) is safer than hardcoded ranges when spill size varies

Frequently asked questions

Why didn't my formulas do this in the old Excel?

Because dynamic arrays didn't exist before 2020. Excel 2019 and earlier only allowed single-value formulas. When Microsoft added spilling, they had to invent new error codes for when spilling failed — that's where #SPILL! and #CALC! come from.

Can I turn off dynamic arrays to avoid these errors?

Not without turning off Excel 365 features. If you truly need old-school behavior for compatibility, use @ before formulas (the implicit intersection operator) — that forces single-value return. But it's rarely what you actually want.

Why does #SPILL! keep appearing even after I delete blocking cells?

Cache issue. Press F9 to recalculate. If still stuck, delete the formula and retype it. Occasionally a stale spill lock persists — closing and reopening the workbook clears it.

Are #SPILL! errors bad for performance?

Not really — the formula didn't compute so no CPU was used. However, having many broken formulas visible clutters the sheet and confuses users. Fix them or wrap in IFERROR to hide them from viewers.

Do these errors happen in Google Sheets?

Sheets has similar spilling behavior with slightly different error messages. Sheets uses "Array result was not expanded because it would overwrite data" — the exact same concept, different wording.

#SPILL! and #CALC! solved instantly.

The Add-in's Error Explainer highlights the exact obstruction, explains why the spill failed, and gives you the fix — right inside Excel.

Get the Add-in →