Don’t Panic! Here’s How to Fix #Spill Error in Excel
Are you getting stuck with #spill error in Excel while entering formulas in it? Want to resolve this error but not getting any effective way to so? Well, if your answer is yes, then keep reading this post till the end.
Here, you are going to discover 3 effective ways on how to resolve spill error in Excel. Along with that, you will get to know Zintego the causes of this error.
So, without wasting any more time let’s move ahead:
The “#SPILL!” error in Excel is an error message that appears when you are using the dynamic arrays and formulas that generate multiple results. This error usually occurs when the formula returns more results than the cell or range of cells it is entered in can handle.
Excel displays this #SPILL! Error to indicate that the formula has “spilled” over into neighboring cells that are not part of the original formula’s range.
Why Do You Get #Spill Error In Excel?
Generally, you meet up with this spill error when you are using formulas such as FILTER, SORT, UNIQUE, and RANDARRAY, which are designed to generate multiple results.
The error can also occur when you are using the “@” character in a formula to create an array formula. Apart from these, there are some reasons as well that could often spill error in Excel.
- Formula is not entered as an array formula
- Formula is entered into a range of cells that already contain data
- Formula returns more values than the available space
- Formula uses a function that doesn’t support arrays
After knowing the causes, now it’s a time to figure out the ways to fix spill error in Excel.
How to Fix #Spill Error inExcel?
The #SPILL error occurs in Excel when a formula or function generates more results than the range of cells can hold.
Here are the few fixes applying which you will be able to fix spill error in Excel. So, keep on trying the given fixes one by one.
Fix #1: Resize the Spill Range
If the cells are not large enough to accommodate the results of the formula, then simply increase the size of the cells by dragging the border of the cells.
Or you may also select the spill range, right-click it, and choose “Resize Table” from the context menu. Then, adjust the range to the desired size.
Fix #2: Check If the Cell Already has Data
The next method you can try to solve #spill error in Excel is, check whether the specific cell is blank or it already contains data in it.
In case,the cells already contain data, then you need to clear them to make extra space for the results of the formula.
All you need to do is, select the cells & make a right-click on it.Now, select “Clear Contents” from a context menu then select the spilled cells and right-click on them.
Finally, choose “Clear Contents” from a context menu then, re-enter the formula to see if the spill error is resolved.
Fix#3: Use an Array Formula
If the formula requires multiple cells, you can use an array formula to ensure that the results spill only to the required cells.
To create an array formula, select the cells where you want the results to appear, enter the formula, and then press Ctrl+Shift+Enterkeys instead of just Enter.
Time to Conclude
After reading this post, hope you will able to fix #spill error in Excel. As you know, the spill error normally occurs while working with dynamic arrays or spilled ranges, introduced in Excel 365.
And you can easily fix this error adjusting the the size of the cells or ranges that contain the formula. You can also modify the formula itself so that it returns fewer results or use the “Wrap Text” or “Shrink to Fit” options to make the text fit within the cells.
Furthermore, if you find this article helpful then don’t forget to share itTop of Form
Thanks for reading….!
