If you’re using Excel 365 or newer versions, you might have come across the SPILL error.
This is a new error code that you will find in addition to the usual error codes in Excel 365.
In fact, although the term SPILL is new, the behavior of spilling is something that dynamic array functions have been demonstrating in earlier Excel versions too.
In this tutorial, we are going to uncover what the SPILL error means, why you might find a SPILL error code in your worksheet and how to get rid of it.
What Does it Mean to SPILL in Excel
If you’ve used dynamic array functions before, the ‘spilling’ behavior might be familiar to you.
When you use dynamic array functions like SORT, UNIQUE, FILTER, etc. in Excel, you get an array in return, instead of a single value.
The array consists of a set of values that need to occupy a group of cells. This behavior of returned values covering more than one neighboring cell(s) is called ‘spilling’.
For example, in the following screenshot, the SORT function returns an array of 9 values, even though we’ve entered the formula in a single cell (B2).
All the 9 returned values simply ‘spill’ into the range B2:B10.
We call this range of cells (B2:B10) that hold the returned array the ‘spill range’.
What is a SPILL Error in Excel?
A SPILL error is an error that occurs when there is something in your worksheet that is preventing an array formula from spilling properly.
In the above image, the value 5, which is in the spill range of the SORT function is preventing the results from spilling into the range.
This is causing a SPILL error, due to which we can see the #SPILL! error code in cell B2.
Note: When you click on the cell containing the formula, you can see its spill range surrounded by dotted lines, as shown in the above screenshot.
How to Fix the SPILL Error in Excel?
The SPILL error mainly occurs when your dynamic array formula’s results are not able to spill into its designated spill range.
There may be multiple reasons preventing your formula from spilling.
Sometimes they’re visible, as can be seen in the above screenshot, and sometimes they’re not all that obvious.
So, if you want to resolve a SPILL error, you need to first diagnose the cause.
Let us look at some common causes of the SPILL error, and how to correct each of them.
Possible Reasons for a SPILL Error in Excel
Here are some possible reasons for a SPILL error:
- The SPILL range may have certain cells containing content (that may or may not be visible to us)
- The SPILL range may contain merged cells
- The SPILL range may be a part of an Excel Table
- The SPILL range may be indeterminate
- The SPILL range may be larger than the space available
A good way to narrow down the reason is by clicking on the warning icon (or green triangle) next to the #SPILL! error code.
Doing this will display the possible cause in the first line of the popup, as shown below:
Let us explore each of the possible causes along with ways to solve them.
Problem: SPILL Range Isn’t Blank
Excel does not allow the dynamic array functions to overwrite non-blank cells with their returned array.
So the function simply returns a SPILL error.
Solution: Clear the SPILL Range
The solution, in this case, is to simply remove the contents from the non-blank cell(s) that are within the spill range.
When you select the formula, you will notice a dashed border around the area of the spill range.
If you see any content within this range, simply remove it or move it to some other cell that is outside the spill range.
Doing this clears the spill area out, so that the function can display its returned results in its spill range.
Sometimes there may be content in some of the spill range cells but they may not be visible.
For example, the font may be of the same color as the cell background. This may get the contents camouflaged.
In such cases, simply select all the cells in the spill range and change font color to Automatic or navigate to Cell Styles -> Normal from the Styles group.
Your hidden cell contents should now be visible.
You can then choose to remove or shift these contents so that your formula can now spill its results in the spill range.
One common scenario is where you may see the SPILL error even when there is no content in the range where the formula result is supposed to come. This could happen if there are cells with space characters. While the space character won’t be visible to you, it will stop the formula to show the result. To correct this, select all the cells in the range and hit delete
Problem: SPILL Range Contains Merged Cells
If any of the cells in your spill range are merged or are part of a merged cell, then your formula cannot spill its results.
Solution: Unmerge the Cells in the SPILL Range
The solution to this problem is to simply unmerge the cells by selecting them and navigating to Home -> Merge & Center -> Unmerge.
Alternatively, you can move the merged cell to some other location that is outside the spill range.
Problem: SPILL Range is in Table
Excel tables don’t support spilled array formulas.
So if your spill area contains a table, or if one or more cells in your spill area happen to fall in an Excel table, you are going to get a SPILL error.
Solution: Convert the table back to a normal range or shift the formula to an area outside the table.
To resolve this, simply move your formula so that its spill area doesn’t fall into the area of the table.
Alternatively, you can convert the table back to a regular range by selecting it and navigating to Table Design ->Tools->Convert to Range.
Problem: SPILL Range is Unknown
When you use volatile functions in your formula, like the RAND, RANDARRAY, and RANDBETWEEN for example, it becomes difficult for Excel to determine the size of the spilled array.
This may be because the array keeps resizing on every calculation pass, causing your result to destabilize.
For example, in the screenshot shown below, the RANDBETWEEN function keeps recalculating, causing the SEQUENCE function to return arrays of different sizes between each calculation pass.
This results in a SPILL error:
Solution: Fix the formula
You can resolve this error by fixing your formula, making sure the volatile function in it returns a determinable and fixed-sized array.
Problem: SPILL Range is too Big
If you refer to entire columns instead of a range containing a subset of columns, you are likely to get a SPILL error.
For example, say you want to use the formula =SORT(A:A) to sort all numbers of column A and display the result as a spilled array in cell B2. Using this formula in Office 365 and later versions returns a SPILL error:
This is because the range A:A is referring to all the thousand or so cells of your column A.
This makes your returned array spill over the edge of your spreadsheet since your formula starts at row 2 (of column B).
This means your spill range is bigger than the number of cells available.
Solution: Avoid Using Column References or move the Formula to the Top of the Column
To resolve this issue, either use references to just the subset of cells containing your data (A2:A10) or move your formula up to the first cell of your column.
In general, when using dynamic array functions, it is always better to steer clear of using entire column references.
In this tutorial, we showed you some possible reasons for getting SPILL errors in Excel.
We also showed you how to find out what’s causing the error and different ways to resolve it.
We hope this helps you in diagnosing and fixing your SPILL errors, if any, in Excel.
Other Excel tutorials you may also find helpful:
- Can’t Type In Excel: 6 Possible Reasons and Solutions!
- Why is Merge and Center Grayed Out?
- Why does Excel Open on Startup (and How to Stop it)
- How to Remove Read-Only From Excel (6 Easy Fix)
- How to Find out What Version of Excel You Have
- #NUM Error in Excel – How to Fix it?
- Subscript Out of Range Error in VBA