Excel troubleshooting

How to Fix #SPILL! in FILTER

Fix FILTER #SPILL! errors by clearing blocked spill ranges, checking merged cells, and understanding dynamic array behavior.

Problem

  • FILTER returns #SPILL! when Excel cannot place the dynamic array result into the cells below or beside the formula.

Quick fix

  • Clear the spill range, unmerge cells, and make sure the FILTER output has enough empty space to expand.

Why FILTER spills

  • FILTER can return multiple rows or columns, so Excel needs empty cells for the full result.
  • Blocked cells, merged cells, or tables can prevent the dynamic array from expanding.
  • The include argument must align with the filtered array.

Best fixes

  • Select the warning icon next to #SPILL! to see the blocked range.
  • Clear any values or formatting conflicts in the spill range.
  • Move the formula outside Excel Tables if the output needs to spill.

Frequently asked questions

  • Can I put FILTER inside a Table? Spill arrays conflict with Table structure. Place FILTER outside the Table or use structured references in a normal range.
  • What is the if_empty argument? Fourth argument in FILTER — return text like "No matches" instead of #CALC! when nothing matches.
  • FILTER vs UNIQUE for distinct values? UNIQUE returns distinct items; FILTER returns all rows that meet conditions. Combine them: =UNIQUE(FILTER(...)).

Root causes beyond the quick fix

  • Something occupies cells in the spill rectangle — merged cells, values, or another dynamic array.
  • Formula moved to a row with insufficient columns below for the result width.
  • Structured references inside FILTER may return unexpected shapes when Table columns change.
  • Legacy .xls or strict compatibility mode may block dynamic array behavior entirely.

Verification steps

  • Click the spill error and choose Select Obstructing Cells from the error menu.
  • Temporarily wrap FILTER in TAKE(..., 5) to confirm logic before full spill on large results.
  • Clear downstream reports that hard-coded over old static ranges before deploying FILTER.

When FILTER is the wrong tool

  • Unique key lookup returning one row → XLOOKUP or VLOOKUP instead of FILTER.
  • Excel 2019 collaborators → legacy formulas or export values-only for downstream users.
  • Huge unfiltered spill → add criteria or TAKE to limit rows before publishing the sheet.

Extended diagnostic workflow

  • Problem: FILTER returns #SPILL! when Excel cannot place the dynamic array result into the cells below or beside the formula.
  • Quick fix: Clear the spill range, unmerge cells, and make sure the FILTER output has enough empty space to expand.
  • Reproduce on three sample rows before changing production formulas shared with the whole team.
  • Save version history or a copy before bulk find-replace across thousands of cells.
  • If the fix works on a sample but fails in production, compare text vs number storage on key columns.

Related functions to verify

  • FILTER (/functions/filter/): =FILTER(array, include, [if_empty]) — Filters a range of data based on criteria you define. Returns an array of values that meet the specified conditions.
  • SORT (/functions/sort/): =SORT(array, [sort_index], [sort_order], [by_col]) — Sorts the contents of a range or array in ascending or descending order.
  • UNIQUE (/functions/unique/): =UNIQUE(array, [by_col], [exactly_once]) — Returns a list of unique values from a range or array.
  • IFERROR (/functions/iferror/): =IFERROR(value, value_if_error) — Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.

Escalation and documentation

  • Rebuild the formula on a blank sheet with minimal ranges to isolate the failing argument.
  • Document the root cause in a shared runbook so the next teammate does not repeat the same fix.
  • Link to fix-excel-formula-errors (/guides/fix-excel-formula-errors/) when multiple error types appear.
  • Escalate to Power Query or IT when the source system export format changed without notice.