In this tutorial, we will know and understand what is #Spill error in Excel. Also, we will provide possible ways to fix it to ease our frustration when it comes to Excel errors.
Definitely, errors can be more frustrating every time we execute a formula. Particularly, if we don’t know what causes and what it means.
Since the #spill error is a common error we encounter in excel, it becomes more confusing every time it appears.
Moving on let’s explain what #spill error…
What is #Spill error in Excel?
#Spill error in Excel normally occurs when the formula contains multiple results however it cannot output them all on the sheet.
When Excel 365 launch its dynamic array all the formula yields multiple results upon calculation “spills” automatically to the next cells. Including any of the formulas, even those that were not designed to handle arrays.
In fact, the range of cells which contains the result is called spill range. Therefore if something prevents from filling the range that’s when the #Spill error occurs.
Interestingly, this error is understandable and predictable. Take a look at this, if your formula is expected to return more than one result however the proceeding cells contain some other data.
All you have to do is to delete the data in it, and the error will be gone.
What makes this error more confusing is the causes that aren’t obvious.
Maybe you have copied the formula on the entire column block of spill range, or may you have utilized some functions which do not support dynamic arrays. Or might be a spilled area have space or non-visible character to the human eye.
What Causes the Spill Error in Excel?
What causes of this error aren’t obvious and that is what makes this error more confusing.
Mainly, the causes maybe you have copied the formula on the entire column block of spill range, or may you have utilized some functions which do not support dynamic arrays. Or might a spilled area have space or non-visible character to the human eye.
Therefore fixing this kind of error requires a thorough analysis in order to determine the root of this error.
So here are what we gathered to fix #spill error in Excel…
How to fix #spill Error in Excel
Below are possible reasons of the #Spill error though we provide possible ways to fix it.
Spill range isn’t blank
This means that the area or range of cells to be filled with the formula contains non-empty cells perhaps it has space or non-visible characters.
To fix this consider the following steps below:
- Clear the entire spill range after the dynamic array formula. Right Click on the range of cells and Click Clear Contents.
- Another option is to transfer the dynamic formula to another location.
- If the out is clear though spill error kept appearing, just click the Select Obstructing Cells option under the error message. This will show the cells obstructing the range spill. All you have to do is to remove or clear its contents.
Reminder do not drag the dynamic array formula in direction.
Spilling Inside Tables
This time Spill error in Excel thought that the dynamic formula is not supported by Excel table. If it’s the case. To fix it’s either to relocate the formula or covert table to ranges.
The reason why dynamic array formulas are not working with Excel tables is still not clear. However, converting table to a normal range and placing the formula in another location is the most useful thing to fix it.
To confirm the main cause of the error, just click the icon with an exclamation mark. There you will see in the first suggestion, Spill range in the table.
Apparently, the best thing to do is to convert the table to normal range. All you have to do is to right-click within the table then select and click Table and Convert to range.
The result is shown below:
Spill range contains merged cells
And now if the error is caused by spill range that contains merged cells.
To fix this you should unmerge cells or delete cells having merged cells. However, if you cannot find the merged cells, click the Select Obstructing cells to highlight them.
Spill Range is Unknown
If the spill range is unknown it will return #spill error. This means you are using two or more dynamic array functions.
In case you do not know, other functions are volatile and should not be used along with dynamic array function. Hence, the results are unknown. Currently does not support arrays with unknown lengths.
For instance, the following formula will return a #SPILL! error:
=SEQUENCE(RANDBETWEEN(1,200))
Because the RANDBETWEEN is volatile and Sequence is an array that returns an unknown length. The only solution is to avoid dynamic array formulas that create arrays or ranges of an unknown length.
Spill Range is Too Big
Creating a formula in Excel that spill range extends beyond worksheet edges is possible. It is because Excel cannot produce split arrays.
Despite many reasons why this error occurs. One of the usual cases is the poor choice of formula wherein columns are used instead of range of cell references.
For instance, the formula uses the full column reference D:D.
To fix this steps are as follows:
- Use range of cells in the formula instead of full-column reference. Take note the normal formula will turn into dynamic array formula.
- Therefore to continue utilizing dynamic formula, implicit intersection is must used. All you have to do is add “@” in front of the column reference which is the root of the error.
- This is a fancy way of asking of forcing multiple values onto a single cell.
Let’s Wrap Up!
In this tutorial, we learned what is #SPILL error and what causes this error. We also explained the various ways to fix these errors using examples. Just familiarize yourself and understand them, as they will come in handy anytime.
If you have any insights, or clarification feels free to leave your comment below.
Thank you for reading 🙂