Spill Range is Not Blank
In Excel, you will get the #SPILL! error when you are using a dynamic function that returns the result in a range of multiple cells, but that range already has some values in it, and now that dynamic function can’t overwrite values to return the result in that range. Now to get the result you need to clear values for the range.
In the above example, you can see we are using the RANDARRAY function that returns random values in a specific range of cells that you define. And in the function, I have defined five rows and five columns for the result values which is the range A1:E5.
Now, as you can see in cell B3, I already have a value there and that’s the reason this dynamic formula can’t extend its result and returns the #SPILL! Error.
To deal with this problem, you need to delete the value from cell B3, and the dynamic function (RANDARRAY) expands itself and returns the result in the range A1:E5 which is five rows and five columns.
Spill Range Error in a Table
If you are using dynamic tables, in that case, there’s a chance that you will get the #SPILL! error. The reason for this is that tables don’t support dynamic formulas yet.
In the above example, we have Table1 where we are trying to get the random number in one column and ten rows, but as we are using the table, it has returned a spill error.
As per Microsoft, there’s no support yet on Excel Tables for the dynamic functions. The way out is to convert the table into a normal range or move your formula to a normal range.
And when you are converting your table into a normal range make sure to delete and re-enter the formula, otherwise it will still show you the error for all the values other than the last value.
When you convert the table into a normal range Excel tries to expand the dynamic formula to the other cells, but those cells still have the spill error (other than the last cell with the formula) and that’s why the dynamic formula won’t be able to get the result for you.
#SPILL! Error Problem with VLOOKUP
There’s might a situation where you get the spill error with the VLOOKUP. Well, it’s not common to get this error with the VLOOKUP, but there is a chance to get it. To understand it, you need to understand the situation and how you are using the formula in the wrong way.
In the above formula, in the lookup value instead of referring to a single cell I have referred to the entire column and this makes it a dynamic formula.
Now as I have referred to the entire column where I have a total of 1048576 cells, and with the dynamic formula Excel will return 1048576 results. Correct?
Now the problem is to get the 1048576 results I need to have 1048576 cells, but I am short by one cell as my formula starts from the D2.
And when I use the same formula from the first cell of a column, I get all the formulas accurately.
Merge Cell can also Cause a #SPILL! Error
If you have a merged cell in the spill range while entering a dynamic formula, in that case also, you will get the spill error in the result.
In the above example, we have a merged cell for the range B2:C3 and when we try to enter RANDARRAY in cell A1, it returns the spill error in cell A1 as it can’t re-write a result in a merged cell.
Selecting Obstructing Cells to Fix the #SPILL! Error
When you click on the cell where you have the #SPILL! error, it gives a drop-down where you have a few options to deal with it.
On the third position, it shows an option to select the obstructing cells, cells that block the result. And the moment you click on this option it will select all the cells that have value.
If you have multiple cells, it will select all those cells and you can delete the values from those cells to get the result in the range that is defined in the formula.
Use @ for Implicit Intersection
When you use a dynamic function that uses a spill range (return result in multiple cells), For avoiding the #SPILL! error, you can use @ operator at the start of the formula to make the formula return results in a single cell instead of an array for multiple cells.
