In Excel (Excel 365 or newer version), users often face a problem called “SPILL Error”. It is a new error code that can be found in Excel along with the usual error codes. Although the word “SPILL” sounds new to the users, the characteristics of “spilling” can be found in older versions of Excel too. On this page, we have discussed the following topics:
- What is “SPILL Error” and why does “SPILL Error” code appear in the work sheet?
- How to fix spill error?
What is SPILL Error?
In the earlier versions of Excel, users may have come across the problem of “SPILL Error” while using dynamic array functions. When a user uses dynamic array functions like SORT, UNIQUE, FILTER etc., he gets an array of values in return, instead of a single value.
Since the array includes a set of values, it needs to occupy multiple cells in the worksheet. When the values require more than one neighboring cell, this behavior is called spilling.
Sometimes this spilling of values in multiple cells cannot occur properly. Something in the worksheet prevents the array formula to spill in more than one cell and that is called “SPILL Error”.
Let’s see an example of spilling of array formula below:
In the following picture, we have entered the SORT formula in cell B2 only, but the function gives us an array of 8 values.
That means all of the 8 values get spilled in the neighboring cells from B3 to B9. This range of cells (B2:B9) is called the “Spill range”. Let’s see the following example where a value 3 in cell B5 causes the “SPILL Error”.
The value 3, which is in the spill range of the SORT function, is obstructing the results from spilling into the range.
Note: If you want to see the spill range (marked by dotted lines in the above picture), click into the cell containing the spill formula.
How to fix spill error?
Spill error occurs due to multiple reasons. In order to prevent spill error, we need to identify the cause of the error first. The easiest way to do so is to click on the warning icon, which is a Green Triangle next to the “#SPILL!” error code, or on the “!” icon that will appear over the cells . In the first line of the pop-up, we can see the reason for the spill error. Picture demonstrating cause of spill error is as follows:
Let’s discuss the reasons for spill errors and the ways to fix those ones.
- Spill range is not Blank:
In Excel, the dynamic array function cannot overwrite non-blank cells with their returned array. That’s why the function shows spill error.
Solution: Clear the designated spill range
The simple solution is to erase the content of the non-blank cell and make sure that the spill range is clear. You can move the content to the other cell too.
In order to know the spill range, select the formula and you will find a dashed border around the area of the spill range. Then simply erase or move the content to another cell. In this way, the function will have the designated area to spill the results.
Sometimes the content in the spill area may not be visible. For example, the font color of the content may be similar to the cell background. This will make the content obscured.
In this case, simply change the font color of all the cells of the spill range. To do so, first, you have to select all the cells of the spill range and change the font color by clicking on the “Cell Styles” button in “Home” and selecting “Normal”, as shown in the picture below.
In this way, the hidden content will be visible. Now you can remove or shift this content to another cell and make sure that the spill area is clear.
Note: Sometimes, despite having clear cells in the spill range, the formula shows error, because some cells contain space characters. In this case, just select the whole spill range area and click delete.
- Merged cells in the spill range:
Spill error occurs when there are merged cells or a part of the merged cells in the spill range. That prevents the formula to spill the results. For example:
Solution: Unmerge the spill range cells
To solve this problem, we need to unmerge the cells in the spill range. Simply select the spill range, then click “Merge & Center” and click the “Unmerge cells” option in the “Home” menu.
Another way is to simply move the merged cells to somewhere else in the worksheet and make the spill range clear. The picture is given below:
- Spill range is in a table or in part of the table:
In excel, the table does not support spilled array formulas. If the spill range is in a table or one or more cells of the spill area fall in a table, the formula will show an error. For example,
Solution: Change the spill range by shifting the formula outside the table or converting the table into a normal range.
To solve this problem, make sure that you write the formula in a cell, which is not close to a table in the excel sheet.
Alternatively, we can make the table in the excel sheet a normal range by selecting the table and clicking on “Convert to Range” in the “Table Design” menu. The picture is as follows:
- Spill range is unknown:
There are some formulas that include volatile functions. For example, the RAND, RANDARRAY, and RANDBETWEEN. In these formulas, the array keeps resizing the range every time it passes each calculation. This causes the result to destabilize and makes it difficult for excel to determine the size of the spilled array.
In the following picture, we used the RANDBETWEEN function. This function keeps recalculating and making the sequence function return arrays of different sizes between each calculation pass. As a result, a Spill error occurs.
Solution: Rewrite the formula
To solve this problem, you must fix the formula by rewriting it accurately. Make sure that the volatile function in it returns a determinable and fixed-size array. An example is given below:
- Too big Spill range:
If we refer to the entire column instead of a subset of the column in the formula, the function will show a spill error.
For example, if we use the formula =SORT(A : A), which means sorting all numbers in column A and showing the result in cell B2 as a spilled array, the spill array goes over the edge of the spreadsheet. This is because the formula started at row B2. This means the range is bigger than the number of cells available in the spreadsheet.
Solution: Write the formula at the top of the column or simply avoid using column references
Generally, we do not use an entire column for dynamic array functions. It is always better to use a subset of columns such as (A2:A9) that contains required data.
If you have to refer to the entire column, then write down the formula at the top of the column so that the resulting array does not exit the number of cells in the spreadsheet.
These are some possible reasons and solutions for Spill errors in excel. Hope these solutions will help you get rid of the spill error in excel.