This article will explain to you all of the reasons for SPILL Error Excel issues and how to fix Spill Error Excel. #Leak! A new type of Spill error in Excel arises when a formula that generates multiple computational outputs attempts to display its output within a spill range. However, that range already includes some other data. The block data can be anything, such as a text value, a merged cell, a standard space character, or insufficient space to return the results.
Spill Excel's answer is simple: either remove the range of any blocking data or select an empty set of cells. A spill error can occur when computing dynamic array formulas because active array formulas give results in many cells or an array. Let's look into why this error arises in Spill Excel and how to fix it.
How to correct Spill Error Excel?
Excel Tables do not support dynamic arrays
Dynamic array formulas are incompatible with Excel tables. When you try to add a dynamic array formula to an Excel table, it will produce a #SPILL! error in all rows. In this case, the solution is:
- To use an alternative formula or delete the Excel Table by changing it to a normal range using the
- Convert to the Range button on the Ribbon's Table Design tab. Table Design > Convert to Range.
Spill Range is Unknown
Some functions are volatile and cannot be used with dynamic array functions because the result would be "unknown" and dynamic formulae do not yet handle arrays of unknown length. For instance, the following formula will result in a #SPILL!
=SEQUENCE(RANDBETWEEN(1,100))
The issue occurs because RANDBETWEEN is volatile, resulting in a volatile length for the array returned by SEQUENCE. The only approach is to avoid dynamic array formulas, which generate arrays or ranges of undetermined lengths.
The spill range is too big
It is possible to write a formula that generates a spill range that extends beyond the edge of the worksheet. For example, the following calculation utilizes the complete column reference A:A:
=A: A+1
This formula will result in a #SPILL! error with the message "Spill range is too big" if placed in any row other than row 1. This occurs because the resulting spill range has 1,048,576 rows (the Excel limit) and will flow off the bottom of the worksheet. Similarly, the following formula attempts to use SEQUENCE to build an array with 17,000 columns:
=SEQUENCE(1,17000)
Because an Excel worksheet only has 16,384 columns, this formula also generates a #SPILL! error. The solution is to avoid using references and formulas, which may result in spill ranges that may not fit on the worksheet.
Implicit intersection (@)
Before Dynamic Arrays, Excel quietly used a practice known as "implicit intersection" to ensure that certain formulas with the potential to generate several outcomes only returned one. In non-dynamic array Excel, these formulas provide a normal-looking result with no errors. However, in some situations, the same formula entered in Dynamic Excel can result in a #SPILL error. For example, in the screenshot below, cell D5 includes this formula, copied down:
=$B$5:$B$10+3
How to fix Spill Error Excel?
To fix a spill error in Excel, you typically encounter this when using dynamic array functions like FILTER, UNIQUE, SORT, etc., and the result of the formula spills over multiple cells when it's supposed to be confined to a single cell. Here's how you can troubleshoot and fix it:
1. Check Function Arguments
Ensure that the function arguments are correct. Sometimes, an incorrect reference or range can cause a spill error.
2. Ensure Have the Latest Excel Version
Spill errors are a feature of dynamic arrays, which are available in Excel versions like Microsoft 365, Excel 2019, and Excel 2016 with certain updates. Make sure you're using a compatible version.
Use the # symbol: If you're trying to use a dynamic array function in a version of Excel that doesn't support it, you might see a spill error. In such cases, try entering the formula as an array formula. You can do this by pressing Ctrl + Shift + Enter after typing the formula instead of just Enter. Hence, this tells Excel to treat it as an array formula.
3. Check Cell Formats
Sometimes, the format of the cells where you're trying to enter the formula can cause spill errors. Ensure that the cells are formatted as General or a format compatible with the type of data you're dealing with.
4. Check Data Types
Ensure that the data types in the range you're referencing are consistent. Mixing text and numbers, for example, can cause spill errors.
5. Check for Circular References
Circular references can also cause spill errors. Ensure that your formula doesn't refer to the cell it's entered into.
6. Update Excel
If you're experiencing spill errors due to bugs or compatibility issues, updating Excel to the latest version may resolve them.
7. Consult Excel Support
If you are still having issues then consult Excel documentation or Contact our Support Technician to help you with the problem.
What does Spill Error Mean in Excel?
A spill error occurs when a formula generates several outcomes but cannot display them all on the sheet.
With the introduction of dynamic arrays in Excel 365, every formula that generates multiple calculation results automatically "spills" those results into nearby cells. Please take note of any formulas. A spill range refers to a set of cells that hold the results. If something on the sheet prevents the range from being filled, a spill error occurs.
For the most part, this behavior is understandable and consistent. For example, if your formula is supposed to produce more than one number but the neighboring cells include other data, simply delete that data, and the mistake will be resolved.
However, the causes are not always clear, which can lead to confusion. Perhaps one or more of the spilled cells contain a space or a non-printing character that is invisible to the human eye. Alternatively, a spill range can be blocked by copying the same formula across a whole column. Or you may have come across one of the few features that does not allow dynamic arrays. To correct an error, you must study each case separately and identify the source of the problem.
Also Read - XLOOKUP With Multiple Criteria
Frequently Asked Questions
How can I fix the spill Error in Excel?
Select the error checking warning and then the Select Obstructing Cells option to go directly to the obstructing cell(s). You can resolve the problem by deleting or moving the obstructing cell's entry. As soon as the obstruction is removed, the array formula will spill as expected.
How can I get rid of the spill error Excel?
Excel spill range explained To remove a specific spill range, delete a formula from the first cell. To prevent a formula from spilling into many cells, use the @ operator, which converts multiple values into a single value.
What is the spill function in Excel?
Spill indicates that a formula results in multiple values, that have been set in surrounding cells. For example, =SORT(D2:D11,1,-1), which sorts an array in descending order, would produce a similar array with 10 rows.