Microsoft Excel is packed with features that make managing data in the indispensable spreadsheet software a breeze. Despite this, sometimes, things don’t go as planned and errors arise.
#SPILL! is one error that comes up even if your formula has perfect syntax and context. If you’ve found a #SPILL! error in a cell in your spreadsheet, read on to find out what it is and how to fix it.
What Is the #SPILL! Error in Excel?
The first step to fixing the #SPILL! error in Excel is to understand the conditions which create it. Microsoft Excel is equipped with various formulas that open up many powerful features. A type calledarray formulasapplies functions across a range of cells instead of just a single cell.
In newer versions of Excel, array formulas automatically expand to fit the necessary space for the formula’s operation. This is called the spill range. In the spill range, the other cells involved but not directly containing the array formula display the formula in gray within the formula bar.
An array formula needs enough space to “spill over” the data, so if these cells aren’t available or are interrupted, the formula can’t function properly. This is the most common cause of the #SPILL! error. Whenever an array formula is executed in a range where it overlaps existing data, the #SPILL! error will occur.
You’ll face the error for a similar reason if your spill range includes merged cells, as the data won’t be able to fill in appropriately in that case either.
The #SPILL! error can also pop up if the array size isn’t a fixed value, like if you set the sizeusing the RANDBETWEEN function. The spill range overflowing past the edge of the worksheet or causing Excel to run out of memory also return #SPILL!. Additionally, array formulas don’t work withinExcel tables.
How to Fix the #SPILL! Error in Excel
The error may seem daunting initially, but the solutions are relatively simple once you know what’s causing the error. Fortunately, Excel tells you what’s causing the error right next to the formula. All you need to do is to select the cell and click the warning sign next to it. Once you’ve figured out what’s causing the error, you may take measures to fix it.
If there is an obstruction in the way of the spill range, the first thing you should try is to move or delete the data that is interfering with the formula. Alternatively, you can move the array formula to another area with enough clear space to contain the spill range.
If merged cells are interfering with your array’s spill range, you’ll need tofind and unmerge the merged cells. You can do this by selecting the merged cells and clicking theMerge and Center > Unmerge Cellsoption on theHometab.
Another type of #SPILL! error happens when Excel cannot compute and fit the sheer size of the output value. Normally, you wouldn’t need a function that returns billions of values. In such cases, the formula itself is likely what’s causing the problem. You’ll need to edit the formula so it outputs the proper array, which is much smaller.
If you can’t find any other reason why your formula isn’t working, double-check your formula to ensure that all the required arguments in your function are included and correctly entered.
Once the array formula has enough space to operate again, it should automatically recalculate. Now you can get back to work without any further problems! If the formula does not automatically recalculate, it may have been manually calculated initially. You can force it to recalculate by pressingF9on your keyboard.
If your array formula is unwieldy or your worksheet is cluttered, you may want tocreate a different sheetand move the formula there. You can then edit the formula to reference the original sheet.
Easily Clean Up Your #SPILL! Issues
It can be frustrating, challenging, or even downright scary when something goes wrong with your data in Microsoft Excel, especially if you can’t figure out what’s causing it. The #SPILL! error in Excel usually occurs with array formulas and can have various causes.
However, solutions to the most common issues are often close at hand. Familiarizing yourself with frequently encountered problems and basic troubleshooting can empower you to dive deeper into unlocking the power of Excel.
