Excel is one of the world’s most popular spreadsheet management programs and has been since its inception. That said, while it’s incredibly easy to get started in the beginning, as your spreadsheets become more complex, so does the software.
In this article, we’re talking about the #spill error in Excel, its causes, and what you can do to fix the problem.
What causes this error?
The error usually occurs when a particular formula generates multiple results but cannot output them on the spreadsheet. There are several reasons why this happens:
- Incorrect use of dynamic arrays.
- Cells in the spill area contain a space or non-printing character.
- The same formula is copied across the entire column block.
- The spill range isn’t blank.
When you run into this error, Excel will also show a warning icon (yellow diamond with an exclamation mark). Hovering over this icon will show you the exact cause of the error.
How to fix this?
Here are six fixes you can try out.
The spill range isn’t blank
One of the most common reasons why you’d run into the error is because the spill range isn’t blank. You can do it by either extending the spill range or selecting the obstructed cells and deleting or moving the obstructing cell’s entry.
The spill range contains merged cells
Similar to the spill range not being blank, if it has merged cells, you can run into problems with spill errors. The solution is also similar; all you have to do is get rid of the obstructed cells by either moving or deleting them.
Out of memory
A spilled array formula that runs out of bounds of Excel’s memory will also cause spill errors. This can be mitigated by referencing a smaller array or range.
In case Excel reports the cause of the error as unrecognised, there’s a chance that either something is wrong with the formula’s syntax or it doesn’t contain all the required arguments for your specific scenario.
Using spilled array formulas in tables
Spilled array formulas aren’t supported in Excel tables, so if you’re trying to implement your formula in one, it will not work. Instead, you can convert the table in a range and try again.
Output extends beyond the sheet
Check to ensure that your formula isn’t extending beyond the screen. In case your dynamic array Excel formula is looking up the entire column and not outputting the result, there’s a good chance that the output is extended beyond the sheet.
In such cases, you can either copy the formula down, using the @ operator to request implicit intersection or only reference the values you’re interested in, keeping in mind that it’ll not work with tables.