How to Fix the Excel Spill Error? — 5 Easy Fixes
Note: This tutorial on how to fix the Excel spill error is suitable for Excel versions included in Office 365.
The # Spill Excel error, commonly referred to as the spill error is a commonly encountered Excel issue affecting many users who use Office 365 versions of Excel.
In this tutorial, I’ll walk you through some important methods to troubleshoot this error.
You’ll learn:
Related:
How to Superscript in Excel? (9 Best Methods)
How to Enable Excel Dark Mode? 3 Simple Steps
How to Shade Every Other Row in Excel? (5 Best Methods)
What Does Spill Mean in Excel?
Before we proceed to look at all the methods to fix the Excel spill error, we need to first understand what ‘spill’ means in Excel.
The latest version of Excel in the Office 365 subscription, comes with a set of formulas that support dynamic arrays. This means that these formulas can do multiple calculations and return multiple values at the same time, unlike a normal Excel formula.
In case there are multiple results, they are filled up in the adjacent cells to the formula cell. This is called ‘Spilling’ in Excel.
What Causes the Spill Error in Excel?
In most cases, the reason is very simple. Some values might already be present in the spill range of a dynamic array function. This will create a conflict and Excel will alert you with a #Spill Error.
But, in some cases, the reasons are not so obvious.
There might be other reasons as well, like invisible values blocking the spill range, dragged formula blocking the spill range, or unsupported or improper use of dynamic arrays etc.
You have to look at each such error carefully to determine what is causing the problem.
Also Read:
How to Apply the Accounting Number Format in Excel? (3 Best methods)
How to Autofit Excel Cells? 3 Best Methods
The FORMULATEXT Excel Function – 2 Best Examples
How to Fix the Spill Error in Excel?
To fix the fill error in Excel, first identify the error message displayed by clicking on the yellow warning triangle inside the error cell.
Then follow the steps provided, for your corresponding error message.
Spill Range Is Not Blank
This means that some value or formula is obstructing the spill range of your dynamic array formula. To fix the error, follow these steps:
- Clear the entire spill range after the dynamic array formula.
- Or move the dynamic array formula to another location.
- If the spill range is visibly clear, but still causing spill error, click on the Select Obstructing cells option below the error message. This will highlight the cells that are obstructing the spill range. Delete them to remove the error.
- Please keep in mind that dynamic array formulas should not be dragged in the spill range direction.
Spilling Inside Tables
Please keep in mind that dynamic array formulas are not supported in Excel tables. This error means that this is the case. To fix this, either move the formula to another location or format the table as ranges.
Spill Range Contains Merged Cells
This means that one or more merged cells are obstructing the spill range. To fix this error, unmerge the merged cells or delete them. If you cannot visually locate them, click on the Select Obstructing cells option to select them.
Spill Range is Unknown
This error message means that you are using a combination of two or more dynamic array functions in the format Function1(Function2()).
Here, Function1 cannot determine the size of the spill array returned by Function2. This means that Function2 is volatile.
To fix this problem, you have to replace this volatile function with a better alternative or find some other way to do the task without using it.
Spill Range is Too Big
This means that your dynamic array function returns a spill array whose length extends beyond the edges of your Excel spreadsheet. (1,048,576 rows)
This can happen due to many reasons. But the usual cause is a poor choice of formula, where columns are used as reference instead of ranges of cells.
To fix this, either use ranges or cells as references inside the formula. But note, this will make the dynamic array formula into a normal formula.
To keep using the dynamic formula, use an implicit intersection inside the formula by adding “@” in front of the column reference that is causing the problem.
An implicit intersection is a fancy way of asking Excel to force multiple values into a single cell value, thus fixing the Excel spill errors.
Suggested Reads:
How to Use the Format Painter Excel Feature? — 3 Bonus Tips
How to Delete a Pivot Table in Excel? 4 Best Methods
How to Sort a Pivot Table in Excel? 6 Best Methods
FAQs
How do I turn off spill in Excel?
To turn off spill in Excel, insert the symbol ‘@’ before the formula that is causing the error. This will turn on the implicit intersection feature, i.e it will automatically reduce multiple values to a single value.
How do I fix the Vlookup spill error?
Excel Spill Errors in Vlookup are usually caused by using columns as a reference inside the VLOOKUP formula. Change all such references to normal range references. This will usually fix the Spill Error.
Closing Thoughts
In this tutorial, we saw what causes Excel spill errors. I have explained the various methods to fix these errors using examples. Keep these things in mind, as they will come in handy anytime.
If you have any questions about this or any other Excel feature, please let us know in the comments section.
You can find more high-quality Excel guides here.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!