The #SPILL error in Excel shows up when a formula tries to return multiple results but something is blocking the cells it needs. Excel cannot place all the results, so it throws the error instead. The fix is almost always simple: clear the blocked cells or adjust your formula.
This guide covers every common cause, every fix, and some things most people miss.
What Is the #SPILL Error?
Excel introduced dynamic array formulas with Microsoft 365. These formulas can automatically return results across multiple cells. That output range is called the spill range.
When Excel cannot fill that spill range because something is in the way, you get the #SPILL! error.
Think of it like this: you pour water into a container, but there is already something sitting inside. The water has nowhere to go.
The error itself is not a bug. It is Excel telling you exactly what the problem is. Once you understand that, fixing it becomes straightforward.
Why the #SPILL Error Happens: All the Causes
There are several distinct reasons this error appears. Each one has a different fix.
1. Another Cell Is Blocking the Spill Range
This is the most common cause. You have data, text, or even an empty-looking cell sitting where Excel wants to write its results.
Example: You enter =UNIQUE(A1:A10) in cell C1. Excel calculates it will need cells C1 through C8. But C4 has the word “Total” in it. Excel stops and shows #SPILL!.
Fix: Click the cell showing the error. You will see a blue dotted border around the intended spill range. Look for anything inside that range and move or delete it.
Watch out for cells that look empty but contain a space character or invisible formatting. Select each cell in the spill range and press Delete to be sure.
2. The Spill Range Overlaps a Merged Cell
Merged cells are one of the most overlooked causes. Excel cannot spill into a merged cell because merged cells do not behave like normal cells.
Fix: Unmerge any cells in the spill range. Go to Home > Merge and Center and click it again to unmerge. Then reformat your data without merging.
3. The Formula Is in a Table
Excel Tables do not support spilling. If you enter a dynamic array formula like =FILTER(...) or =SORT(...) inside a formatted Table (the kind you create with Ctrl+T), you will get a #SPILL! error immediately.
Fix: Either move the formula outside the Table, or convert the Table back to a normal range. To convert, click inside the Table, go to Table Design > Convert to Range.
4. The Spill Range Extends Beyond the Worksheet Edge
If your formula would need to spill beyond row 1,048,576 or column XFD (the last column in Excel), it cannot complete.
Fix: Move your formula to a higher row or earlier column so the output fits within the sheet.
5. The Spill Range Is Too Large or Unknown
Some formulas produce a dynamic number of results depending on conditions. If Excel cannot determine the size of the output at calculation time, it may throw #SPILL! with the message “Spill range is unknown.”
This often happens with volatile formulas like RAND() or OFFSET() inside dynamic array formulas where the size changes constantly.
Fix: Refactor the formula to use a fixed or bounded range. Replace volatile functions where possible.
6. Out of Memory
Extremely large spill ranges can cause a memory-based #SPILL! error. This is rare but can happen when a formula tries to generate millions of cells worth of data.
Fix: Reduce the scope of the formula. Instead of running it on an entire column, limit it to the actual data range.
How to Diagnose the #SPILL Error Fast
Excel actually tells you the specific reason when you hover over or click the error cell.
Follow these steps:
- Click the cell showing
#SPILL! - A small yellow warning icon appears to the left
- Click that icon
- Read the message in the dropdown
You will see one of these messages:
- “Spill range isn’t blank” (something is blocking)
- “Spill range has merged cells”
- “Table doesn’t support spill”
- “Spill range is unknown”
- “Spill range is too large”
- “Out of memory”
Each message maps directly to one of the causes above. This saves you guessing.
Step-by-Step Fix for the Most Common Cause
Since a blocked spill range is the most frequent issue, here is exactly how to fix it.
Step 1: Click the cell with the #SPILL! error.
Step 2: Look at the blue dotted border that appears. That is the intended spill range.
Step 3: Click the yellow warning icon and select “Select Obstructing Cells”. Excel will highlight exactly what is in the way.
Step 4: Move that content somewhere else, or delete it if it is not needed.
Step 5: Your formula will recalculate and the results will spill correctly.
If the obstructing cell looks blank but is still causing the error, press Delete on it directly. Sometimes a cell holds a space, a line break, or leftover formatting that is invisible but still counted as content.
Formulas That Commonly Trigger #SPILL Errors
These are the dynamic array functions most likely to cause this error because they return multiple results by design:
| Function | What It Does | Why It Spills |
|---|---|---|
FILTER | Returns rows matching a condition | Returns variable number of rows |
UNIQUE | Returns distinct values | Output size depends on data |
SORT / SORTBY | Sorts a range | Returns full sorted array |
SEQUENCE | Generates a number sequence | Fills rows and columns |
RANDARRAY | Generates random numbers | Fills a defined grid |
XLOOKUP | Returns matching data | Can return arrays |
TEXTSPLIT | Splits text into parts | Fills multiple columns |
All of these are powerful. They just need room to work.
Using the @ Operator to Suppress Spilling
Sometimes you do not want a formula to spill at all. You want just one result.
You can use the implicit intersection operator (@) to tell Excel to return only the value from the current row or column.
Example: Instead of =SORT(A1:A10), use =@SORT(A1:A10).
This returns a single value and will not cause a #SPILL error. It is useful when you are working inside Tables or in situations where spilling would create layout problems.
#SPILL Error in Excel Tables: The Real Problem
Tables are great for structured data but they have a hard restriction against dynamic arrays. This trips up a lot of users who expect modern formulas to work everywhere.
Here is what happens:
You create a Table with Ctrl+T. You then enter =FILTER(...) in a column. Excel shows #SPILL! no matter how clear the range looks. The formula is not wrong. The Table itself is the restriction.
Your options:
- Write the formula in a cell outside the Table
- Convert the Table to a plain range
- Use a helper column outside the Table that references the Table data
Microsoft has acknowledged this limitation. As of 2026, dynamic array formulas still cannot spill inside Tables. This is by design because Tables use structured references that conflict with spill behavior.
For more technical background on how dynamic arrays work in Excel, the Microsoft support documentation on dynamic array formulas is a reliable reference.
Preventing #SPILL Errors Before They Happen
A few habits will save you time:
Leave buffer space. When you write a dynamic array formula, leave a few empty rows and columns below and to the right. This gives the formula room even if your data grows.
Avoid merged cells near formulas. Merged cells cause problems beyond just #SPILL errors. Keep them out of areas where you use formulas.
Do not use entire column references with large outputs. Writing =SORT(A:A) on a sheet with 500,000 rows of data will generate a massive spill range. Use =SORT(A1:A500) or a named range instead.
Convert Tables before using dynamic arrays. If you need dynamic array outputs, work outside your Table structure or convert the Table first.
Check your worksheet layout. Before placing a dynamic array formula, look at the area it will occupy. A quick visual check prevents most #SPILL errors.
Real Examples With Fixes
Example 1: UNIQUE Formula Getting Blocked
You want a unique list of product names from column A, starting in cell D2.
You type: =UNIQUE(A2:A100)
You get #SPILL!.
You click the warning icon and see “Spill range isn’t blank.” You click “Select Obstructing Cells” and Excel highlights D15, which has a stray number from old data.
You delete D15. The UNIQUE formula now spills correctly from D2 down.
Example 2: FILTER Inside a Table
You have a Table called SalesData. In a column named FilteredSales, you enter:
=FILTER(SalesData[Amount], SalesData[Region]="North")
You get #SPILL!. The warning says “Table doesn’t support spill.”
You click outside the Table, in cell H2, and type the same formula there. It works perfectly.
Example 3: SEQUENCE Near the Bottom of the Sheet
You are in row 1,048,570 and you type =SEQUENCE(100). Excel tries to fill 100 rows but only 6 rows remain before the sheet ends.
You get #SPILL!. The fix: move the formula to row 1 or any row where 100 rows fit below it.
Quick Reference: #SPILL Error Causes and Fixes
| Error Message | Cause | Fix |
|---|---|---|
| Spill range isn’t blank | Data in the spill path | Clear obstructing cells |
| Merged cells in spill range | Merged cells block output | Unmerge the cells |
| Table doesn’t support spill | Formula inside a Table | Move outside Table or convert to range |
| Spill range is unknown | Formula size can’t be determined | Use fixed ranges, avoid volatile functions |
| Spill range too large | Output exceeds sheet limits | Restrict formula to smaller range |
| Out of memory | Formula output too large | Reduce data scope |
What Changed in 2026
Dynamic array support in Excel has matured significantly. Microsoft has continued improving performance for large spill ranges. However, the core limitations remain the same. Tables still do not support spilling. Merged cells still block spill ranges.
One notable improvement is that Excel now highlights the obstructing cells more clearly in newer builds of Microsoft 365. The “Select Obstructing Cells” feature in the error dropdown is more reliable than it was at launch.
If you are using Excel 2019 or earlier, dynamic array functions are not available. You would use older array formulas with Ctrl+Shift+Enter instead, and the #SPILL! error would not apply.
For a deeper dive into array functions and Excel best practices, Exceljet’s guide to dynamic arrays offers clear, practical examples.
Conclusion
The #SPILL! error in Excel is one of the easier errors to fix once you know what causes it. It always comes down to one thing: the formula needs space and something is blocking it.
Click the error, read the warning message, and follow the fix for that specific cause. In most cases you will resolve it in under a minute.
The key things to remember:
- Clear any data blocking the spill range
- Unmerge cells in the output area
- Never place dynamic array formulas inside Excel Tables
- Give your formulas enough room to expand
- Use the @ operator when you only want a single result
Dynamic array formulas like FILTER, UNIQUE, SORT, and SEQUENCE are some of the most useful tools in modern Excel. The #SPILL error is not a reason to avoid them. It is just Excel asking for a little more room.
Frequently Asked Questions
What does #SPILL mean in Excel?
The #SPILL! error means a formula is trying to return multiple results into a range of cells, but one or more of those cells are occupied or cannot receive data. Excel cannot complete the output, so it shows the error instead. Clearing the blocked cells or fixing the layout resolves it.
Why am I getting a #SPILL error when the cells look empty?
Cells can look empty but still contain a space, a special character, or leftover cell formatting. Click each cell in the spill range and press Delete to properly clear them. You can also use the “Select Obstructing Cells” option from the error icon to find the exact cell causing the problem.
Can I use dynamic array formulas inside an Excel Table?
No. As of 2026, Excel Tables do not support dynamic array spilling. If you place a formula like FILTER or UNIQUE inside a Table, you will always get a #SPILL! error. The solution is to write the formula in a cell outside the Table, or convert the Table to a normal range first using Table Design > Convert to Range.
How do I stop a formula from spilling?
Use the implicit intersection operator (@) before the formula. For example, =@SORT(A1:A10) returns only one value instead of spilling the full sorted list. This is useful when you want a single result in a specific cell without affecting surrounding cells.
Does the #SPILL error affect older versions of Excel?
The #SPILL! error only appears in Excel versions that support dynamic arrays, which means Microsoft 365 and Excel 2021 onward. In Excel 2019 or earlier, dynamic array functions are not available, so you would not encounter this specific error. Those versions use legacy array formulas entered with Ctrl+Shift+Enter instead.
