HOW TO CORRECT THE #SPILL ERROR IN EXCELMar 06, 2022
Today, we're going to cover a common question I get: what is the #SPILL! Error, and how can I get rid of it? Sometimes it's easy to find a reason for it, but sometimes, it's not so obvious. So let us show you here the Spill Excel error and how to fix it—also referred to as a #spill or hash Spill excel error.
This Excel error occurs if you reference a range too extensive for the workbook (such as referencing entire Excel columns) or when the Spill excel range is not empty. You may also see this error if you use one of the new Excel functions such as the FILTER function, UNIQUE functions, or SORT Excel.
#Leak! A new type of Spill Excel error mainly occurs when a formula that produces multiple computational results tries to display its output in a spill range. Still, that range already contains some other data.
The block data can be anything, including text value, merged cell, a regular space character, or there isn't enough room to return the results. The solution of Spill excel is simple, either clear the range of any blocking data or select an empty set of cells that do not have any data in it.
A spill error usually occurs when calculating dynamic array formulas because dynamic array formulas are a formula that outputs results in multiple cells or an array. Let's take a look in more detail and understand why this error occurs in Spill Excel and how to solve it.
What causes a spill error?
Since the launch of dynamic matrices in 2018, Excel formulas can handle multiple values at once and return results in more than one cell. Dynamic arrays are scalable arrays that allow formulas to return multiple results to a range of cells in a worksheet based on a formula entered into a single cell.
When a dynamic array formula returns multiple results, those results are automatically spread out to neighboring cells. This behavior is called "Spill" in Excel. The range of cells in which the results spill out is called the "spill range." The Spill's scope will automatically expand or contract based on the source values.
If the formula is trying to populate a spill range with multiple results but is blocked by something in that scope, a #SPILL error will occur.
Excel now has nine functions that use the dynamic arrays function to solve problems, these include:
Dynamic array formulas are only available in Excel 365 and are not currently supported by offline Excel programs (e.g., Microsoft Excel 2016, Microsoft Excel 2019).
Spill errors are not only caused by data obstruction. There are many reasons why you can get the #Spill errors. Let's explore the different situations you might encounter #SPILL! Error and how to fix it.
The spill range isn't blank.
One of the leading causes of spill errors is that the spill scope is not empty. For example, if you are trying to return ten results, but if the file includes any cells with data in the spill area, the formula returns the #SPILL! Error. #spill range isn't blank. You can see #SPILL! error - Spill range isn't blank
And when you click on the formula cell, you will see a dashed blue border indicating the spill area/range (C2:F2) required to display the results below. You will also notice a yellow warning sign with an exclamation point on it. #non empty cells
To understand the cause of the error, you can click on the warning icon next to the error and see the message in the first line highlighted in gray. As you can see, it says "spill scope is not empty" here.
The problem here is that the cells in the spill range D2 and E2 contain text characters (not empty), hence the error.
How to fix this:
The solution is simple, either wipe (move or delete) the data within the Spill or move the formula to another location with no obstruction.
Once you delete or move the block, Excel will automatically fill in the cells with the formula results. When we scan the text in D2 and E2, the formula moves the column to row as intended.
Even though the spill scope appears empty in the example below formula, the formula still returns the Spill! Error. This is because the Spill is not empty and has an invisible space character in one of the cells.
It's hard to locate space characters or any other invisible character hiding in what appear to be empty cells. To find those cells containing unwanted data, click on the error float (warning sign) and select "Select obstructed cells" from the list, and it will take you to the cell containing the blocked data.
As you can see in the screenshot below, cell E2 contains two spaces. Therefore, you will get the appropriate output when you clear this data.