How to fix the #SPILL! error
About spilling and the #SPILL! error
With the introduction of Dynamic Arrays in Excel, formulas that return multiple values "spill" these values directly onto the worksheet. The rectangle that encloses the values is called the "spill range". When data changes, the spill range will expand or contract as needed. You might see new values added, or existing values disappear.
Video: Spilling and the spill range
A #SPILL error occurs when a spill range is blocked by something on the worksheet. Sometimes this is expected. For example, you have entered a formula, expecting it to spill, but existing data in the worksheet is in the way. The solution is just to clear the spill range of any obstructing data.
Sometimes however the error might be unexpected and therefore confusing. Read below for how this error might be caused, and what you can do to resolve.
Spill behavior is native
It's important to understand that spill behavior is automatic and native. In Dynamic Excel (currently Office 365 Excel only) any formula, even a simple formula without functions, can spill results. Although there are ways to stop a formula from returning multiple results, spilling itself can't be disabled with a global setting.
Similarly, there is no option in Excel to "disable #SPILL errors. To fix a #SPILL error, you'll have to investigate and resolve the root cause of the problem.
Fix #1 - clear the spill range
This is the simplest case to resolve. The formula should spill multiple values, but instead it returns #SPILL! because something is in the way. To resolve the error, select any cell in the spill range so you can see its boundaries. Then either move the blocking data to a new location, or delete the data altogether. Note that cells in the spill range must be empty, so pay attention to cells that contain invisible characters, like spaces.
In the screen below, the "x" is blocking the spill range:
One the "x" is removed, the UNIQUE function spills results normally:
Fix #2 - add @ character
Before Dynamic Arrays, Excel silently applied a behavior called "implicit intersection" to ensure that certain formulas with the potential to return multiple results only returned a single result. In non-dynamic array Excel, these formulas return a normal looking result with no error. However, in certain cases the same formula entered in Dynamic Excel may generate a #SPILL error. For example, in the screen below, cell D5 contains this formula, copied down:
=$B$5:$B$10+3
This formula wouldn't throw an error in, say Excel 2016 because implicit intersection would prevent the formula from returning multiple results. However, in Dynamic Excel, the formula automatically returns spill multiple results onto to the worksheet, and which crash into each other, since the formula is copied down from D5:D10.
One solution is to use the @ character to enable implicit intersection like this:
= @$B$5:$B$10+3
With this change, each formula returns a single result again and the #SPILL error disappears.
Note: this partially explains why you might suddenly see the "@" character appear in formulas created in older versions of Excel. This is done to maintain compatibility. Since formulas in older versions of Excel can't spill into multiple cells, the @ is added to ensure the same behavior when the formula is opened in Dynamic Excel.
Fix #3 - native dynamic array formula
Another (better) way to fix the #SPILL error shown above is to use a native dynamic array formula in D5 like this:
=B5:B10+3
In Dynamic Excel, this single formula will spill results into the range D5:D10, as seen in the screenshot below:
Note there is no need to use an absolute reference.
