In Excel, Dynamic Array Formulas like FILTER, UNIQUE, and SORT can return a number of values (array) at once. These results are spilled into the adjacent cells. But, when the formula cannot return all values, you’ll get #SPILL! Error.
Apart from that, the formula will also result in #SPILL! Error when there are merged cells, Table Data, Nested Volatile Functions, and Full Column References.
Since we know the main causes, you can correct this error with these 6 fixes.
Clear Spill Range
You’ll get a #SPILL! Error when there is a non-blank cell within the result range which blocks the output from spilling.
So, make sure your spill range is blank to solve this error. Look for values in the Range and clear or move them to make space for the results.
However, if you do not see any values within the spill range, I suggest you click on the Error Floatie > Select Obstructing Cells to check. Sometimes, you might have a value in the cell that is hidden.
For Example, a Formula that returns a Null String.
Once it identifies the cell, delete the value. The formulas will return the results immediately after there’s no blockage and the formulas can spill.
Unmerge Cells
Next, when you have Merged Cells, it does not allow the Array formula to spill. Thus, it results in a #SPILL Error.
When you hover your cursor over the Error Floatie, you can also see the “We can’t spill into a merged cell” message.
To correct this error, all you need to do is find Merged cells and unmerge them. For that, select all the Result Range and click on Merge and Center in the Home Tab.
Limit Cell References
Using entire Column or Row References could be another reason for #SPILL! Error.
In Excel 2019 and older versions, there is a Legacy Array formula. Meaning, to use the array formula, you would have to enter Ctrl + Shift + Enter which returns only one result. So, even if you reference an entire column or row, the array formula will work and result in a single value.
But, this is not the case with Excel 365. Since this version supports Dynamic Array, it can return several values in the array.
For Instance, when you select the whole Column Reference for the lookup value of the XLOOKUP function, Excel will dedicate the formula to return the 1048576 results which is till the last grid. Eventually, when the formula cannot spill past the spreadsheet’s edge, you’ll get the #SPILL! Error.
Such a big reference can also lead to “Not Enough Memory” issues and “Excel crashes.”
So, if you use Excel 365, you need to limit the cell references. Check out these two tips given below.
- Use Single Cell or Cell Ranges Referencing in the formula.
For Example,=XLOOKUP(J3,A3:A14, F3:F14, "No Amount", 0).
Here, I have referenced only one cell (J3) for the lookup value.
- Enter @ (Implicit Intersection operator) before the Cell or Range Reference to decrease the array to a single cell value.
For example,=XLOOKUP(@J:J,A3:A14, F3:F14, "No Amount", 0).
Convert Table to Range
At the time of writing this article, Excel’s Table Format only supports the Legacy Array Formula. So, if you’ve entered the Dynamic Array Formula in the Table, it’ll result in #SPILL! Error.
The formula should work as soon as you convert the Table to the normal range. For that, select the Cell in the Table and go to the Table Design Tab.
In the Tools section, choose Convert to Range. On the Prompt dialogue box, hit Yes.
Don’t Use Auto-Fill
The primary purpose of Dynamic Array is to return several matches or values at once in an array. So, you do not need to use the Flash-Fill handle to copy down the formula.
I know this is a pretty basic thing. However, by any chance, if you extended the Fill Handle after getting results, you may get #SPILL! Error when the formula overlaps.
So, avoid using Auto-Fill. Rather, you can extend the range reference in the formula itself.
Avoid Volatile Array Formulas
Volatile functions like RANDBETWEEN, OFFSET, INDIRECT, NOW(), TODAY(), etc, change the returned output in each re-calculation. So, when you pass down these functions to return an array, Excel fails to estimate the exact size to spill the array. As a result, you’ll get #SPILL! Error.
So, avoid using VOLATILE functions in Dynamic array like =SEQUENCE(RANDBETWEEN(300, 3000))
Lastly, by now, the above fixes should solve the #SPILL! Error. But, if the error still isn’t gone, check your formula and make sure all the arguments are correct. Maybe you have entered the wrong argument.