6 Best Ways to Fix #SPILL! Error in Microsoft Excel Sheets
Excel is a powerhouse of a spreadsheet app. One of the features is a formula that allows you to perform simple to complex calculations quickly and easily. You get the #spill! error in Microsoft Excel when the formula returns multiple results. This is mathematically wrong which is why Excel, instead of showing the result, displays the SPILL error. Let’s understand in more detail what spill and array mean in Excel, how to identify such errors, and how to fix them.
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:
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 number4needs 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 arethree ways to fix this spill errorand 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 onTable 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 theSelect Obstructing Cellsoption 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 topassword protect an Excel file.
Gaurav Bidasaria
Gaurav is an editor here at TechWiser but also contributes as a writer. He has more than 10 years of experience as a writer and has written how-to guides, comparisons, listicles, and in-depth explainers on Windows, Android, web, and cloud apps, and the Apple ecosystem.
He loves tinkering with new gadgets and learning about new happenings in the tech world.
He has previously worked on Guiding Tech, Make Tech Easier, and other prominent tech blogs and has over 1000+ articles that have been read over 50 million times.
