In Microsoft Excel, SPILL errors occur when a formula returns multiple results, and Excel cannot return the results to the grid. In this tutorial, we will explain the procedure to remove the SPILL error in Excel
What is the #SPILL formula error in Excel?
The SPILL error in Excel means that formula has resulted in multiple values and has been placed in the neighboring cells.
How to remove SPILL error in Excel
Follow the steps below to fix these types of #SPILL formula errors in Excel:
- Spill range is not blank
- Spill range is too big
- Extends beyond the worksheet edge
- Table formula
- Out of memory
- Spill into merge cells
- Unrecognized/ Fallback
1] Spill range is not blank
This error occurs when a spill range for the spilled array is not blank.
When the SPILL error is selected, a dashed border will indicate the intended spill range.
Select the error icon and choose the Select Obstructing cells option to immediately go to the obstructing cell. Delete the error by deleting the obstructing cell’s entry. When the obstruction is cleared, the array formula will occur.
2] Spill range is too big
This error can occur when Excel is unable to determine the size of the spilled array because it’s unpredictable and resizes between calculation passes. Dynamic array resizes may cause calculation passes to ensure the spreadsheet is fully calculated. If the array continues to change during these additional passes and does not steady, Excel will resolve the dynamic array as SPILL.
3] Extends beyond the worksheet edge
In Excel, the formula will cause a SPILL error if Excel will lookup the entire column, return 1,048,576 results, and hits the end of the Excel grid.
There are three methods to fix this issue:
Method 1:
Reference the lookup values you are interested in; this style of formula will return a dynamic array but does not work with Excel tables.
Method 2:
Reference the value on the same row, and then copy the formula down. The traditional formula style will work in tables but not return a dynamic array.
Method 3:
Use the @ operator, and then copy the formula down. The traditional formula style will work in tables but not return a dynamic array.
4] Table formula
Spilled array formulas are not supported in an Excel table. Move the formula out of the table or convert the table to a range: Click Table Design > Tools > Convert to range.
5] Out of memory
The spill array formula you are trying to enter has caused Excel to run out of memory. Please try to reference s smaller array of range.
6] Spill into merge cells
In Microsoft Excel, spilled array formulas cannot spill into merged cells. Unmerge the cells, or move the formula to another range that does not interconnect with merged cells.
Select the error icon and choose the Select Obstructing cells option to immediately go to the obstructing cells. Unmerge the cell. When the merged cells are cleared, the array formula will occur.
7] Unrecognized/ Fallback
Excel doesn’t recognize or can’t reconcile the cause of this error. Please make sure your formula contains all the required arguments for your scenario.
Read: Microsoft Excel Formulas not updating automatically.
We hope this tutorial helps you understand how to remove Spill errors in Excel; if you have questions about the tutorial, let us know in the comments.
