If you have been using Excel 365 for a while, you have almost certainly run into a cell showing #SPILL! in red, with no obvious reason why. You cleared what looked like a blank area. You checked for data. Nothing. The error stayed.
This guide covers every cause of the Excel spill error, but it focuses on the one that trips up even experienced users: an invisible character hiding in a cell that blocks the spill range without any visual sign it is there.
Everything in this article applies to both Excel 365 and Excel 2021, since both versions support dynamic arrays. Excel 365 gets more attention here simply because it is the version most people are running, and it receives dynamic array improvements on a rolling basis that Excel 2021 does not.
What Is a Spill Error in Excel 365?
Excel 365 introduced dynamic arrays, which let a single formula return multiple results that automatically fill (“spill”) into a range of cells. Functions like FILTER, SORT, UNIQUE, XLOOKUP, and SEQUENCE all rely on this behavior.
When Excel cannot complete that spill because something is already occupying one or more of the destination cells, it shows #SPILL! instead of returning your results.
The formula itself is not broken. The spill range is blocked.
The Common Causes of #SPILL! (And Quick Fixes)
Before getting to the hard case, here are the straightforward scenarios.
1. Visible Data in the Spill Range
Another value or formula is sitting in a cell that your formula needs to write into.
Fix: Move or delete the conflicting content, then press Enter on your formula cell to recalculate.
2. The Formula Is Inside a Table
Excel Tables do not support dynamic array spilling. If your formula sits inside a formatted Table (Insert > Table), Excel blocks the spill.
Fix: Move the formula to a cell outside the Table range, or convert the Table back to a normal range (Table Design > Convert to Range).
3. Merged Cells in the Spill Range
Merged cells are fundamentally incompatible with spill behavior. If any merged cell overlaps your intended spill range, you get #SPILL!.
Fix: Select the merged cells, go to Home > Merge and Center, and click it again to unmerge. Then recheck your formula.
4. The Spill Range Is Too Large
Some formulas produce an array so large it would extend beyond the worksheet’s last row or column (row 1,048,576 or column XFD).
Fix: Revisit your formula logic to limit the output size, or use arguments like [rows] and [columns] in SEQUENCE to constrain the result.
The Hard Case: An Invisible Character Blocking the Spill
This is where most articles stop, and where most users get stuck.
You hover over the spill error indicator. Excel highlights the spill range in a blue dashed border. You look at every cell in that range. They all appear empty. You click on each one. The formula bar shows nothing. You press Delete anyway. The error persists.
The culprit is almost always a space character, or occasionally a non-printing character like a tab or line break, sitting in one of those cells. It is completely invisible to the eye, it does not show in the formula bar on a quick glance, and it makes the cell non-empty as far as Excel is concerned.
This happens more than you might expect. Common sources include:
- Pasting data from a website or another application that left trailing spaces behind
- A formula that once returned a space (
" ") before it was deleted - Importing from a CSV or external system that pads fields with spaces
- Accidental keystrokes in a cell that was then “cleared” by pressing Delete only once
How to Find the Invisible Character
Method 1: Check the Formula Bar Carefully
Click each cell in the highlighted spill range one at a time and look at the formula bar. A space character will show as a tiny gap at the start or after the cursor. It is easy to miss. After clicking, press the End key and then Home to move through any content.
Method 2: Use LEN() to Expose It
In a spare cell next to the suspicious range, enter:
=LEN(B2)
Replace B2 with the cell you want to check. If the cell looks empty but LEN() returns anything other than 0, there is hidden content in it. A result of 1 almost always means a single space character.
Method 3: Use ISBLANK() to Scan the Range
=ISBLANK(B2)
A truly empty cell returns TRUE. A cell containing only a space returns FALSE, which immediately identifies the problem cell.
Method 4: Find and Replace
This is the most efficient method when the spill range is large:
- Select the entire spill range (click the formula cell, then look at the blue dashed border to see what Excel is trying to fill).
- Press Ctrl + H to open Find and Replace.
- In the Find what field, type a single space character.
- Leave the Replace with field completely empty.
- Click Replace All.
Excel will report how many replacements it made. Even one means it found your hidden blocker.
Method 5: Use TRIM() or CLEAN() on Suspect Source Data
If your data comes from an import or paste, wrap your source references in TRIM() to strip leading and trailing spaces before they cause problems:
=FILTER(TRIM(A2:A100), B2:B100=”Active”)
CLEAN() removes non-printing characters (character codes 0 through 31):
=FILTER(CLEAN(A2:A100), B2:B100=”Active”)
Using both together covers the widest range of invisible character types:
=FILTER(TRIM(CLEAN(A2:A100)), B2:B100=”Active”)
Step-by-Step: Diagnosing Any #SPILL! Error
Use this sequence whenever you encounter the error:
- Click the cell showing #SPILL!.
- Look at the blue dashed border Excel draws to show the intended spill range.
- Check the yellow warning icon to read the specific message (“Spill range isn’t blank” is the most common).
- Scan each cell in the spill range visually.
- If nothing obvious appears, run
=LEN()or=ISBLANK()on each cell. - If you find a hidden character, use Find and Replace (Ctrl + H) to clear spaces across the entire range at once.
- If the formula is inside a Table, move it outside.
- If merged cells are present, unmerge them.
- Press Enter on your original formula cell to recalculate.
Preventing #SPILL! Errors Before They Happen
A few habits eliminate most spill errors before they occur.
Leave buffer space. When you write a dynamic array formula, leave several empty rows and columns below and to the right. This gives the spill range room to grow if your data expands.
Clean imported data immediately. Any time you paste or import from outside Excel, run a TRIM/CLEAN pass on the data before using it as a source for dynamic array formulas.
Avoid merging cells in working ranges. Merged cells solve a visual formatting problem but create functional problems across many Excel features, not just dynamic arrays. Use Center Across Selection (Format Cells > Alignment) instead for a visually identical result that does not break formulas.
Keep dynamic array formulas outside Tables. If you need both a Table and dynamic arrays, place the formula in a cell adjacent to the Table rather than inside it.
Quick Reference: #SPILL! Causes and Fixes
| Cause | How to Identify | Fix |
|---|---|---|
| Visible data in spill range | Cell shows content in the blue-bordered area | Move or delete the data |
| Space or invisible character | LEN() returns > 0, ISBLANK() returns FALSE | Ctrl + H, find space, replace with nothing |
| Formula inside a Table | Formula cell has Table formatting | Move formula outside the Table |
| Merged cells in range | Cells visually span multiple columns or rows | Unmerge all cells in the spill range |
| Output exceeds sheet boundary | Spill range reaches row 1,048,576 or column XFD | Constrain formula output size |
Frequently Asked Questions
What does #SPILL! mean in Excel 365?
The #SPILL! error means a dynamic array formula cannot write its results because one or more cells in the destination range are not empty. Excel 365 formulas like FILTER, SORT, UNIQUE, and SEQUENCE need free space to output multiple values automatically.
How do I fix the Excel spill error when the cells look empty?
If the spill range appears empty but the error persists, a space or non-printing character is likely hiding in one of the cells. Use the LEN() function on each cell in the range. Any result above 0 means hidden content is present. Then use Find and Replace (Ctrl + H) to search for a space and replace it with nothing.
Can the #SPILL! error happen because of merged cells?
Yes. Merged cells block spill ranges even if they do not contain any data. Unmerge all cells within the intended spill range and the error will resolve.
Why does my dynamic array formula show #SPILL! inside a Table?
Excel Tables do not support dynamic array spilling. Move your formula to a cell outside the Table, or convert the Table to a normal range using Table Design > Convert to Range.
How do I find which cell is blocking the spill range?
Click the cell showing #SPILL! and look at the blue dashed border Excel draws around the intended spill area. Then use =ISBLANK() or =LEN() on each cell in that range. ISBLANK() returning FALSE on a visually empty cell points directly to the problem.
Does TRIM fix #SPILL! errors caused by space characters?
TRIM prevents spaces in your source data from causing issues, but it does not fix an existing space sitting in a destination cell. To fix that, select the spill range, press Ctrl + H, search for a space character, and replace it with nothing.