Spill errors are one of the most irritating problems that occurs in excel, mainly because it's not apparent what is causing them. Before we tackle the dreaded Spill error let me explain what a spill is in excel terms.
What are Spills
So if you are using legacy version of Excel than latest the Office 365 subscription then spills will not mean anything to you as they were available in prior versions. Office 365 subscription comes with a set of formulas that support Dynamic arrays (see the Dynamic Arrays in Excel) which means that certain formulas can do multiple calculations and return multiple values at the same time, unlike a normal Excel formula.
This is basically meaning then if there are many results coming from that one formula, Excel will fill in the cells in the rows or columns adjacent with all the all the results. You can probably already appreciate that you will need to make sure all those adjacent cells are clear of data.
Spill errors in Excel: The cause
There can be a few reasons why the spill error is occurring, so you will need patience and a few drops of perserverance. So here is what to look for:
-
Blockage: The formula is expecting return multiple values, but instead it returns #SPILL! because there is already something in the spill range. There is a blockage. Fortunately the spill range will produce an area with a strong border to show the cells were the data will lie, so simply make sure you clear everything in those cells
-
Tables: You are using a defined table for your data but one reason or another excel does not support dynamic arrays on tables (why???). You can fix this with removing the table and making it simply into a range of data.
- Volatile functions: Some functions are volatile (See Volatile functions in excel) and can't be used with dynamic array functions because the result are unknown. The only way round this is to get to grips with understanding which functions are volatile and removing them.
- Implicit intersections: Before dynamic arrays were ever invented Excel quietly applied a behaviour called implicit intersection that made sure certain formulas with the potential to return multiple results only returned a single result. In non-dynamic array Excel, these formulas return a normal-looking result with no error. In a number of cases that same formula used as a dynamic could create a #SPILL error.