It can take quite a while, especially for new users, to understand the reason behind the #SPILL! error. This article will assist you if you’re in a similar situation. Keep reading to fix the #SPILL! error in Excel so you can avoid it the next time.
How Do You Get the #SPILL! Error?
Fix #SPILL! Error in Excel
The solutions to the #SPILL! error depends on the issue you might be facing. After you skim through the list of causes, you can move on to the relevant fixes from the solutions we’ve discussed below. Below, we’ve listed the causes for the #SPILL! error in MS Excel:
Blockage in Spill RangeVarying Spill RangeValue Exceeds Excel GridArray Spilled in TableLarge Array SizeSpill Cell Merged
Remove the Blockage
To spill an array, you need to have certain cells empty. For example, if your array contains five cell items, five cells that fall under the spill range must be empty. If any cell in between contains data, Excel will display the #SPILL! error. For your value to spill, you must remove the blockage in the range. If you do not see anything in between, your cell must include either space or a hidden character. In that case, you’ll need to use the Sort & Filter tool. Through this feature, you can inspect the cells under a row and delete them. Here are the steps you can follow to remove the blockage using the Sort & Filter tool:
Convert Table to Range
Although sorting your data in tables might make it easier to view your data, it does not support the spill feature. If you try spilling your array in Excel tables, you’ll be met with the #SPILL! error. You can keep the look of a table and still spill your formula. For this, you will have to convert the table to a range. Follow these steps to change your table to a range in Excel: You will now be able to spill your value into the corresponding cells.
Unmerge Cells
The values won’t spill if a cell in the spill range is merged. The merged cells act as an interference in the range. This then triggers Excel to display the #SPILL! error to notify users that there’s been an error spilling values to the cell. If you have any cells merged in the spill range, you must split them. Separating cells is just as easy as merging them. Follow these steps to unmerge cells in Excel:
Avoid Volatile Functions
Excel won’t spill value if it cannot determine the array size. Some array functions such as RAND(), NOW(), and TODAY() are extremely volatile with uncertain spill ranges. Additionally, some arrays return values that exceed the Excel grid. When the range isn’t specified or exceeds the grid, Excel will return the #SPILL! error. Your only way to solve this issue is to avoid spilling dynamic array. Swap the function with less volatile Excel functions or smaller arrays if you can.
Spill to a Different Cell
You cannot reference an entire column to a cell other than the one cell first in a row. Excel will try to fit every element, including blank cells from the referenced column to the range under the cell with the formula. Excel cannot fit the results in its grid as it does not have enough cells to fit each element. You can easily solve this issue by changing the cell to enter your formula. If you want to spill value from an entire column, use the first cell in a row to enter your formula. This will allow Excel to fit every element in its grid.
