#SPILL! Error is one of the errors you may encounter while adding an Excel formula. Did you ever face this error?
It often occurs when you apply an improper concept of spilling an array in an Excel formula.
What Does #SPILL Mean in Excel?
Whenever your formula returns more than one outcome but is unable to operate all those outcomes on the sheet, you will most likely face a #SPILL! error. Let’s have a brief overview of the #SPILL error.
In dynamic arrays, when a formula continues producing more than one result, by default, it will give you spills in adjacent cells. Any formula that handles arrays or not will by default produce spills. A spill range often appears with a range of cells that carry results. And when you find something that stops filling that range is known as a #SPILL error.
Suppose, your formula is producing more than one value and the adjacent cells contain some data then simply delete it and you will find that error gone. It might be possible that in the spilled area one or more cells have a space or a character that is not visible. To fix this issue, you must have to understand the real reason behind it.
How to Fix the #SPILL Error in Excel?
First, you need to identify why this error is occurring before finding a fix to this. You can have a look at this notification by clicking on the yellow triangle in the error cell. Below are some fixes to this error:
Spill Range in Not Empty
This heading shows that some value or formula is stopping the spill range of the dynamic array formula. Follow the steps given below to fix this error:
Once the dynamic array formula, you need to clear the entire spill range. Or else you can move the dynamic array formula to some other site.
When the spill error appears even though the spill range is visible, click on the Select Obstructing option given under the error notification. It will simply select the cells stopping the spill range. You can now delete them or even remove the error.
You will see the Spill Range clearly when the spill error appears.
Under the error notification, choose the Select Obstructing Cells option.
Note that dynamic array formulas must not be dragged in the spill rage.
Spill In Tables
Whenever you will try spilling in the table, a spill error will occur. Spill ranges and Excel tables are not compatible with each other because tables have different syntaxes.
For ease, you can convert the table into a range or else move the formula out of the table. Choose the table and open the Table Design option.
Click on the Convert to Range button and you will see a table without deleting the data.
Spill Range in Unknown
When Excel does not identify how small or big a range would be, you will find some formulas appear with a spill error.
=SEQUENCE(RANDBETWEEN(1,10))
Using the above formula will let you make a series of columns. In this formula, the SEQUENCE argument is a dynamic array function as it returns a spill range. You will find its spilling gets disturbed when the consistent computation of the RANDBETWEEN function is done. You will encounter an error as the spill range size of SEQUENCE does not determine.
Here, you may need to find RANDBETWEEN one time in another cell B2 and paste the value in the SEQUENCE function.
The spill range contains merged cells:
When you have merged cells, spilling does not allow it. Therefore, unmerged cells are used in the spilled area or you can move the formula somewhere else.
In a spilled array, when there are one or more merged cells, you may encounter this error notification – Spill range has merged cell. When the merged cells are unidentifiable, you need to click on the Select Obstructing Cells option to move on to other cells:
Summary:
You now have a clear-cut idea of what does spill mean in Excel. Therefore, you need to be careful about things that let you face this error. Before fixing an error, finding a reason behind it is necessary.
