Free Excel workbook
Get the #SPILL! Error Practice Workbook
Download the ready-to-use workbook and practice the exact fixes from the tutorial.
- Practice fixing blocked spill ranges and hidden obstructions
- See examples for merged cells, tables, and whole-column formulas already set up
- Follow the tutorial without recreating every error scenario from scratch
Enter your email below for instant access.
Free. Instant access.
What is the #SPILL! error in Excel?
The #SPILL! error is a product of the new Dynamic Array calculation engine in use by Excel for Office 365 subscribers.
Historically, a formula would return a single result to a cell, such as calculating the SUM of a range of cells.
=SUM(A1:A10)
If we were to reference a range of cells (ex: =A1:A10), the results would be limited to the first encountered cell reference (A1).
This is because the formula is trying to display the contents of 10 cells within the confines of a single cell. Excel says to itself, “I can’t show ten results, so I’ll just show the first one.”
In the “old days” this issue was solved using array notation (CTRL-Shift-Enter) and cell pre-selection. Trust me, it’s not a road you want to go down.
With the new Dynamic Array engine, if a formula returns multiple results, the results are “spilled” into the adjacent cells.
This allows for multiple results to be displayed without the need for array notation.
All Microsoft 365 dynamic array functions use the spill feature:
- SEQUENCE
- FILTER
- TRANSPOSE
- SORT and SORTBY
- RANDARRAY
- UNIQUE
- XLOOKUP and XMATCH
- VSTACK and HSTACK
- TEXTSPLIT, TOCOL, and TOROW
- CHOOSEROWS, CHOOSECOLS, TAKE, and DROP
- GROUPBY and PIVOTBY
- MAP, REDUCE, SCAN, and LAMBDA
The spill feature also applies to traditional functions like SUM, MATCH, and FREQUENCY when they receive array inputs.
For a deeper look at how spill behavior works and why Microsoft 365 introduced it, see our guide on how Excel dynamic arrays changed everything.
How do I fix a #SPILL! error caused by obstructing cells?
One of the requirements for the spill behavior to operate properly is that there must be an area of unoccupied cells for which to spill into.
If you are trying to display 10 results, but there is data in any of the 10 needed cells, Excel will respond with a #SPILL! error message.
The dotted-blue border indicates the area that is needed to display the results.
To solve this problem, either move or delete the data located in the spill range.
If you are uncertain where the obstruction lies, you can select the Error “floatie” (exclamation button) and click “Select Obstructing Cells” to have attention drawn to the offending cell(s).
This is especially useful for situations where you may need to spill into hundreds or thousands of cells. Selecting each cell in the spill range to look for anomalous data would be impractical.
What causes hidden #SPILL! errors? (Font color, number formatting)
Sometimes, the reason for a #SPILL! error is not as obvious as the example above.
An easy example is text that has been formatted with the same font color as the cell’s fill color.
Custom Number Formatting can also play a part in hiding text. Take for example the following number format code set.
; ; ;
This is a sneaky way to hide anything placed in a cell, regardless of font color or cell color.
The three semi-colons are saying, “Show no positive numbers, show no negative numbers, show no zeroes, and show no text.”
If you wish to learn more about Custom Number Formatting, check out these posts:
Learning Excel Custom Number Formatting
Why you SHOULD be USING Custom Number Formatting
What are the other causes of #SPILL! errors?
There are some more exotic reasons for why the #SPILL! error can occur. The previously mentioned reasons are the most common, but here are other reasons you may wish to investigate if the obvious fails.
Why does SEQUENCE with RANDBETWEEN cause a #SPILL! error?
Excel was unable to determine the size of the spilled array because it’s volatile and resizes between calculation passes. For instance, the following formula will trigger this #SPILL! error:
=SEQUENCE(RANDBETWEEN(1, 1000) )Dynamic array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated. If the size of the array continues to change during these additional passes and does not stabilize, Excel will resolve the dynamic array as #SPILL!.
How do I fix “spill range extends beyond worksheet” errors?
For example, if we placed in cell B2 the following formula…
=VLOOKUP(A:A, A:C, 2, FALSE)The results would cause a #SPILL! error because Excel will lookup the entire column, return 1,048,576 results, and hit the end of the Excel grid. So close; we only needed one more cell.
It’s generally considered poor practice to select entire columns when selecting ranges.
Why do dynamic array formulas fail in Excel Tables?
Spilled array formulas are not supported in Excel Tables. Spilled formulas should only exist in a single cell. Excel Tables will repeat the formula to every cell in the table’s column. This creates catastrophic interference between every cell in the column.
If you need to use the formula as a spilled array formula, you will need to revert the Excel table to a “plain table” using the Table Design (tab) -> Tools (group) -> Convert to Range option.
How do I fix an “out of memory” #SPILL! error?
The spilled array formula you’re attempting to enter has caused Excel to run out of memory. In these cases, try referencing a smaller array or range.
Why do merged cells cause #SPILL! errors?
If the results of the spilled array encounter a cell that has been merged with other cells, a #SPILL! error will occur.
How do I prevent #SPILL! errors before they happen?
Prevention is faster than debugging. Apply these five habits when writing dynamic array formulas and #SPILL! errors become rare.
- Place formulas in columns with no data below. If FILTER or UNIQUE might return 50 rows, make sure rows 2 through 51 are clear before writing the formula.
- Avoid whole-column references. Use bounded ranges like A2:A1000 instead of A:A. Whole-column references push the spill range past row 1,048,576 and trigger #SPILL!.
- Never put dynamic array formulas inside Excel Tables. Tables repeat formulas down every row, which conflicts with how spills work. Convert to a plain range first.
- Unmerge cells before writing the formula. Even one merged cell in the spill path breaks the output. Check Home, Merge & Center.
- Bound your dynamic ranges. If you use SEQUENCE or RANDARRAY, set fixed row and column counts. Dynamic counts that change on recalc can trigger the “indeterminate size” variant of #SPILL!.
If you want to go beyond fixing errors and actually master the dynamic array functions that cause them, our Modern Excel Functions course walks through FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE, VSTACK, and every other function in the dynamic array family with practical business examples.
Featured Course
Master Excel’s Essential Modern Functions
Can Copilot debug #SPILL! errors?
Yes. With Edit with Copilot in Microsoft 365, you can ask Copilot to diagnose a #SPILL! error by describing what you’re trying to do. Copilot reads the surrounding cells, spots the obstruction, and suggests a fix.
Example prompts
Open the Copilot pane in Excel and type any of these:
“My FILTER formula in cell E2 is showing #SPILL!. Find what’s blocking it and tell me how to fix it.”
“Why is this UNIQUE formula returning #SPILL! and how do I fix it without deleting any data?”
“Convert this Excel Table to a regular range so my dynamic array formula works.”
Copilot is especially useful for spotting invisible blockers like text with matching font color or custom number formatting. It reads cell values directly, so hidden text is visible to it even when it isn’t visible to you.
Requirements
- Subscription: Microsoft 365 Personal, Family, or Premium with AI credits; or a commercial Microsoft 365 Copilot license
- File location: Workbook must be an .xlsx file saved to OneDrive or SharePoint
- Version: Microsoft 365 desktop (Windows or Mac) or Excel for the web
If you don’t have Copilot, the manual fixes above cover every case. Copilot just speeds up the hunt for the invisible causes.
#SPILL! Error FAQ
What is the #SPILL! error in Excel?
The #SPILL! error means a formula tried to return multiple results but something blocked the cells where those results need to appear. It only happens with dynamic array formulas like FILTER, UNIQUE, SORT, XLOOKUP, and SEQUENCE in Microsoft 365 Excel.
How do I fix a #SPILL! error?
The fastest fix is to click the warning icon next to the cell with the #SPILL! error, choose Select Obstructing Cells to jump to the cell blocking the spill range, then delete or move its contents. The spill range clears and results appear automatically.
Why does my #SPILL! error appear when the cells look empty?
The cells aren’t actually empty. Common hidden blockers include text with the same font color as the cell background, custom number formats like ;;; that hide content, single spaces or other whitespace, and cells protected from formula changes. Click each cell and check the formula bar to see what’s really there.
Why does #SPILL! happen inside an Excel Table?
Excel Tables automatically copy formulas down every row of the column, which conflicts with how dynamic array formulas spill. To fix this, click inside the table, go to Table Design, and choose Convert to Range. The table becomes a regular range and the dynamic array formula works.
Why do merged cells cause #SPILL! errors?
Dynamic array formulas need individual unmerged cells to write results into. If the spill range contains any merged cells, Excel returns #SPILL! until the merge is removed. Select the merged cells and click Merge & Center to unmerge.
How do I prevent #SPILL! errors in the first place?
Place dynamic array formulas in empty columns with no data below, avoid whole-column references like A:A, never use dynamic arrays inside Excel Tables, unmerge cells before writing formulas near them, and bound your ranges (A2:A1000 not A:A).
Download the Practice Workbook
Free Practice Workbook
Every #SPILL! example from this guide, ready to try.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
