Why the Spill Range Isn't Blank Even When It Looks Empty
Excel's dynamic array engine - introduced in Excel 365 in 2019 and covering 7 functions (SEQUENCE, FILTER, UNIQUE, SORT, SORTBY, XLOOKUP, and XMATCH) - will not write output into any cell that isn't truly empty. "Truly empty" means ISBLANK() returns TRUE. A cell that displays nothing but contains a space, a zero-length string from a formula, or invisible formatting fails that test.
The five causes, ranked by how long they typically waste your time:
| Cause | Looks Empty? | ISBLANK() | LEN() | Fix |
|---|---|---|---|---|
Formula returning "" | Yes | FALSE | 0 | Delete or replace formula |
| Space character | Yes | FALSE | 1 | Delete |
| Non-breaking space (Unicode 160) | Yes | FALSE | 1 | Find & Replace the char |
| Merged cells in output range | No visible border | N/A | N/A | Unmerge |
Number formatted as ;;; (hide) | Yes | FALSE | varies | Clear All formatting |
The formula-returning-empty-string case is the hardest to catch. A cell in your spill path contains something like =IF(Assumptions!$B$4="","",Assumptions!$B$4) that evaluated to "" when the assumption was cleared. The cell looks completely empty. There's no value, no visible content, no formatting hint. But ISBLANK() returns FALSE and Excel will not spill through it.
How to Find What's Blocking Your Spill Range in 10 Seconds
The fastest path:
- Click the cell showing
#SPILL!. A dotted blue border appears around the intended output range. - Press F5 → Special → Blanks. This selects only the truly empty cells, leaving the blocker unselected. The blocker is whatever cell in the range is not selected.
- Alternatively, press Ctrl+G → Special → Formulas to highlight any formula cells sitting in the range. Anything that comes back is your problem.
For a P&L model pulling 1,200 rows of transaction data with a FILTER formula targeting 340 output rows, you won't find the blocker by scrolling. The F5 approach finds it in 10 seconds.
If you want a reusable audit before you even anchor a spill formula, this SUMPRODUCT pre-check counts how many cells in the target range aren't truly blank:
-- Audit target range before dropping a spill formula into it
=SUMPRODUCT(--(NOT(ISBLANK('P&L'!C4:C343))))
Zero means the range is clean. Anything above zero means you have blockers - and the count tells you exactly how many.
5 Causes of a Spill Range That Isn't Blank (With Fixes)
1. Formula Returning Empty String
A formula elsewhere in your model populated the spill target with ="". This is common in models where conditional logic clears cells to suppress display: =IF(DCF!$B$2="Final","Terminal Year",""). When the condition flips, the cell holds "" instead of nothing.
The DCF period header scenario comes up constantly. You have 10 fiscal year headers (FY2025 through FY2034) and someone added a "" fallback in the terminal year column. Then you try to SEQUENCE across that row. The SEQUENCE sees 1 occupied cell and stops.
Fix: Replace ="" fallbacks with =NA() and wrap consuming formulas in IFERROR. Or delete the formula from cells that should be vacant.
2. Invisible Characters
Paste data from any ERP export - SAP, Workday, Oracle - and you'll frequently get non-breaking spaces (Unicode character 160) in cells that display nothing. Your eyes say blank. LEN() says 1.
Fix: Home → Find & Replace. In the Find box, hold Alt and type 0160 on the numpad. Replace All with nothing. If that doesn't catch it, try copying a space from one of the suspect cells and pasting it directly into the Find field.
3. Merged Cells
If any cell in the spill path belongs to a merged range, Excel treats the entire merge as occupied. This surfaces in DCF models where someone merged the header row across 10 fiscal year columns and then tried to SEQUENCE below it. The merge ends at column K but Excel's spill path still runs through the merged area's logical footprint.
Fix: Select the merged area → Home → Merge & Center → Unmerge Cells. Then set horizontal alignment to Center manually.
4. Phantom Formatting
Cells cleared with the Delete key (rather than right-click → Clear All) can retain formatting that flags them as non-empty in edge cases. Less common, but worth running when the other causes don't pan out.
Fix: Select the full suspect range → Home → Clear → Clear All.
5. Competing Spill Output
Two dynamic array formulas can't share output space. If a SEQUENCE in 'Returns Analysis'!C4:C43 is already occupying that range, a second formula targeting the same area will #SPILL!.
Fix: Move one formula. There's no way to coexist - they need separate real estate.
Google Sheets: Same Blocking Problem, Different Error Code
If you're converting an Excel LBO or DCF to Google Sheets - or debugging a model that lives in both - you'll hit the same underlying problem but with a different error.
Google Sheets doesn't use #SPILL!. When an array formula's output range is blocked, you get #REF!, with the detail: "Array result was not expanded because it would overwrite data in [cell reference]." According to Google Sheets documentation (current as of June 2026), ARRAYFORMULA expands into cells below and to the right unless blocked by existing content - the same occupancy check Excel runs.
The behavioral differences matter when you're working cross-platform:
| Behavior | Excel 365 | Google Sheets |
|---|---|---|
| Error code | #SPILL! | #REF! |
| Error message | "Spill range isn't blank" | "Array result was not expanded because it would overwrite data" |
| Wrapper required | No - dynamic arrays spill natively | ARRAYFORMULA still needed for most functions |
| FILTER / SORT / UNIQUE | Native spill | Native spill as of 2023 |
Formula returning "" blocks | Yes | Yes |
| Merged cells block | Yes | Yes |
| ISBLANK diagnostic | Works | Works identically |
The SUMPRODUCT pre-check works the same in Sheets:
=SUMPRODUCT(--(NOT(ISBLANK('P&L'!C4:C343))))
Run it in a utility row before anchoring an ARRAYFORMULA in a column you've inherited from someone else's model.
One non-obvious conversion trap: Excel models often use ="" in helper columns to suppress display without deleting values. In Excel, those helper columns might sit outside the spill range entirely - the SEQUENCE or FILTER targets a different area. When you convert to Sheets and rewrite formulas, the ARRAYFORMULA might land in a different column alignment, walking directly into the ="" cells. Check every helper column for LEN() = 0 AND ISBLANK() = FALSE before you assume the Sheets conversion is clean.
Google Sheets native spill functions (FILTER, SORT, UNIQUE) added in 2023 behave more like Excel's dynamic arrays now. But ARRAYFORMULA-wrapped formulas still use the older expansion model - blocked by the same invisible characters and formula strings, cleared by the same diagnostic approach.
Preventing the Error Before It Hits
The rule is simple: any cell intended to be empty must be truly empty. Not ="", not =IF(x,"",y). If you need conditional display suppression, use =IF(x, value, NA()) and wrap the consuming formula in IFERROR.
For multi-tab models with several dynamic array outputs - a returns tab using SEQUENCE for period headers, a cash flow tab using FILTER to pull from the transaction log - the SUMPRODUCT pre-check belongs in a model audit row at the bottom of each tab. Wire it to a conditional format so it turns red when non-zero:
-- Drop this in a model audit row on each tab
=SUMPRODUCT(--(NOT(ISBLANK('Returns Analysis'!C4:C343))))
If ModelMonkey is part of your workflow, it runs a pre-write cell check before writing any range - flagging non-empty cells in the target before the write executes rather than after. That catches the ="" problem at the moment it would cause a spill error, not when you're debugging a broken board pack the morning it's due.
For more on what Excel blocks inside structured tables specifically, see Spilled Array Formulas Not Supported in Excel Tables.
Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.