Spreadsheets

#SPILL! error in an Excel expense summary: causes and fixes

Fix the #SPILL! error in an Excel expense summary: find the blocking cell, clear merged cells, handle tables, and resize formulas that spill too far.

Editorial illustration for #SPILL! error in an Excel expense summary: causes and fixes

#SPILL! means a formula wants to fill several cells but something is in the way. Click the cell showing the error, then the small warning icon, and choose Select Obstructing Cells. Clear or move whatever it highlights, and the results fill in. This applies to Excel 365 and Excel 2021, where formulas such as UNIQUE, FILTER, SORT and GROUPBY can return many values at once.

What does a spill range actually mean?

When a formula returns several results, Excel reserves a rectangle of cells below and beside it. Say you type =UNIQUE($B$2:$B$300) in E2 and your log has nine categories. Excel needs E2 to E10 free. If E6 holds anything at all, even a space or a leftover total, the formula cannot spill and shows #SPILL!. The blue border you see around a working result is the spill range.

What are the usual causes in an expense sheet?

  • A stray value inside the range, such as an old category list, a note or a total you typed earlier.
  • An invisible character. A cell can look empty but contain a space, or a formula returning an empty string.
  • Merged cells in the path. Spilling cannot cross merged cells.
  • The formula sits inside an Excel Table. Tables do not support spilled results.
  • The result is too large, for example a formula pointing at whole columns that would need more rows than the sheet has.
  • Another dynamic formula nearby is already using the cells you need.

How do I find the blocking cell quickly?

  1. Click the formula cell that shows #SPILL!. A dashed border outlines where the results want to go.
  2. Open the warning icon beside the cell. The message names the reason, such as the range not being blank or containing merged cells.
  3. Choose Select Obstructing Cells. Excel jumps to the cell or cells in the way.
  4. If the cell looks blank, press Delete on it anyway. A hidden space is cleared by that.
  5. If the obstruction is a merged cell, select it and choose Home, Merge and Centre to unmerge it.
Reason shownWhat it meansFix
Spill range is not blankSomething occupies a target cellClear it or move the formula
Spill range has merged cellsA merge lies in the wayUnmerge those cells
Tables do not support spilled arraysThe formula is inside a TableMove it outside, or convert the Table to a range
Spill range is too bigThe result would run past the sheetLimit the range or use a fixed row count

Excel's exact wording differs a little between versions, so read the message on your screen and match it to the closest row.

How do I fix a summary that sits inside a table?

A common setup is a log formatted as a Table with a UNIQUE category list placed in a column right beside it. As soon as the formula touches the Table, you get a message about tables. Move the summary to its own sheet, or to cells outside the Table, and the spill works. If you want the formula to stay where it is, select any cell in the Table, choose Table Design, Convert to Range, and accept that the range will no longer grow on its own.

What if the spill is too big?

Pointing a formula at a whole column such as $B:$B asks Excel for over a million rows, and the result may not fit. Use a bounded range like $B$2:$B$500, or wrap the result in a formula that removes blanks, for example =UNIQUE(FILTER($B$2:$B$500,$B$2:$B$500<>"")). Without the FILTER, UNIQUE adds an entry for the blank cells and shows 0 for it.

How do I stop it coming back?

  • Give each spilling formula its own area, with nothing typed below it.
  • Put a heading above the formula and leave the cells beneath for results only.
  • Avoid merged cells in summary sheets; use Centre Across Selection instead.
  • Keep notes in a column to the left, not in the path of a result.
  • If you use a pivot table instead, you avoid spills altogether, at the cost of pressing Refresh.

Sheets built from statements by hand break in small ways like this. If you would rather spend the time reading your spending, SpendixAI keeps imported transactions and category totals ready in reports, so the summary does not depend on empty cells below a formula.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports