What Is Spill Error and How to Spot One
A spill error is when you enter a formula in Excel and it comes back with an error because the range is too big or there is not enough memory, for example. So Excel will either display an error or it will show these results in different cells. So these multiple values (answer to the query) are entered in neighboring cells. Some other spill errors that you may encounter are:
spill range is emptyspill range is unknownspill range is out of memoryspill occurred due to fallbackspill range is too bigspill range isn’t blankspill range has merged cell
A simple way to troubleshoot the Spill range error is to paste the formula into an empty excel sheet and recreate the error. That way, you don’t lose valuable data and hours of hard work. Let me give you an example. You have a table with the names, customer ID, and addresses of employees. You enter a formula to fetch the mobile number of the employees from another sheet when the customer ID matches. Excel will throw a spill error when more than two phone numbers are associated with the same customer ID. Now, the range is larger and cannot be displayed in a single cell resulting in spillage to neighboring cells. Does this example help fix the spill error on your Excel sheet? If not, here are some solutions.
Fix Spill Range Error in Microsoft Excel
I have created a dummy sheet with a sequence that is returning a #SPILL! error in Excel. Let’s see what we can do to fix the issue. Here is how it looks.
1. When Spill Range Is Too Big
Let’s take a look at what I entered in the cell first. The range is too wide for it to calculate array size resulting in a spill error. If the array is dynamic in nature then Excel will respond will spill error to make sure that other calculations in the spreadsheet are complete. Try reducing the range width if you can to see if that helps.
2. When Spill Range Is Not Blank
When entering the formula in the Excel sheet but the spill range for the array is not blank., this is what you get — spill error with a dashed border line around. The border denotes the area of the spill range. This spill error can be fixed quickly. Right-click to select the Error floatie, and choose the Select Obstructing Cells option to go to the obstructing cell(s). You can then clear the error by either deleting or moving the obstructing cell’s entry. As soon as the obstruction is cleared, the array formula will spill as intended. In this case, the number 4 needs to be deleted or moved to clear the blockage for the spill to occur.
3. When Excel Is Out of Memory
This one is easier to fix. All you need to do is reference a smaller range or array because the current range is too big and is causing Excel to ‘run out of memory’.
4. When Spill Error Extends Beyond Sheet’s Edge
Do you know how many cells are there in an Excel column? The answer is 1,048,576. Yes, there is a limit and the formula that you just entered (was it VLOOKUP?) is causing Excel to search or lookup the entire column and come back with 1,048,576 results. But then it tries to go beyond the sheet’s range resulting in the spill error. The solution is then to try with a smaller range. In this case, moving the formula to cell F2 should resolve the spill error. Dynamic Arrays wasn’t always part of Excel sheets. With its addition, Excel will go for all values in the lookup_value, which means if an entire column is offered as an argument (in our case that’s D:D), Excel will lookup 1,048,576 values in that column. After that, it will try and spill these to the grid but hit the end or edge of the Excel sheet resulting in the spill error. There are three ways to fix this spill error and the one you choose will depend on the end result you want.
5. Table Formula
Tables in Excel simply do not support spill formulas and so using one inside a table will throw a spill range error. Either remove the table, which is easier said than done, or move the spill formula out of the table. Another solution, a middle ground, can be converting the table into a range. To do so, click on Table Design > Tools > Convert To Range.
6. Spill Into Merged Cells
Do you have merged cells in your Excel spreadsheet? If yes, spilled array formula cannot spill successfully into merged cells. To solve this spill error in Excel, you need to unmerge the cells. If that is not possible then move the formula to a different cell so that the merged cells are no longer in the way or intersect with the range. As you will notice in the screenshot above, selecting the formula will create a dashed border line highlighting the spill range. Again, right-click and select the Select Obstructing Cells option to find the merged cells if you aren’t sure where they are.
Spill It Over
Spill errors in Excel sheets may occur due to a number of reasons. They could vary depending on the data you have input. Finding the reason behind the spill in the array range is more important. Only then can you apply a suitable fix. For further reading, we recommend learning how to password protect an Excel file.
