#SPILL! error in Excel - what it means and how to fix

#SPILL Error in Excel: Diagnostics and Solutions

Have you ever encountered this situation: After upgrading to Microsoft Office 365 with the latest Excel update, the reliable formula you've been using for years suddenly stopped working, replaced by a #SPILL error? Don't worry, this article will answer you.

SPILL error meaning

Simply put, when a formula produces multiple results but cannot be displayed all on the worksheet, a #SPILL! error occurs.

Dynamic arrays are introduced in Excel 365, and any formula that produces multiple calculations automatically "overflows" its results into adjacent cells, even if the formula was not originally designed to process arrays. The range of cells containing the results is called the overflow range. A #SPILL! error occurs if something on the worksheet prevents populating the range.

In most cases, this behavior is understandable and predictable. For example, if your formula expects to return multiple values, but nearby cells are filled with other data, just delete that data and the error will go away.

But sometimes the reason is not that obvious, so it is confusing. Perhaps one or more cells in the overflow area contain spaces or unprintable characters that cannot be seen by the human eye. Alternatively, copying the same formula to the entire column will prevent overflow ranges. Or, you may have encountered some very few features that do not support dynamic arrays. To fix the error, you must investigate each case separately and determine the root cause of the problem.

Solve #SPILL! error in Excel

SPILL! Errors can be caused by a variety of reasons. To understand the root cause of the problem, click the warning icon (yellow diamond with exclamation mark) and read the message in the first line highlighted in gray:

After determining the cause, find the corresponding example below, which contains detailed instructions on how to resolve a specific case.

Overflow range is not empty

Cause : The area to be filled with the formula result contains non-empty cells.

Solution : Clear the expected overflow range.

In the simplest scenario, just click on the formula cell and you will see a dotted border indicating the boundary of the overflow range – any data in it is a barrier. So either delete existing data from the overflow area or move the formula to another location that is not blocked.

However, in some cases, the overflow range may appear blank, but it is not. The problem may be invisible characters lurking in some cells (such as spaces), or the formula returns an empty string.

To detect such cells, click the warning flag and you will see this description - the overflow range is not empty . Below it, there are multiple options. Click Select Blocking Cells and Excel will show you which cells prevent formulas from overflowing.

In the following figure, the blocking cell is A6, which contains the empty string ("") returned by the formula.

To clear blocking cells, go to the Start tab > Edit group and click Clear > Clear All.

Overflow range contains merged cells

Cause : Overflow does not apply to merge cells.

Solution : Unmerge cells in the overflow area or move the formula to another location where there is no merged cell.

If one or more merged cells are expected to exist in the overflow array, the following error message is displayed: The overflow range contains merged cells .

If you have trouble detecting merged cells intuitively, jump to the problematic cell using the Select Blocking Cell option:

Overflow range in table

Cause : Excel tables do not support dynamic arrays.

Solution : Convert a table to a normal range or place a formula outside the table to allow it to overflow. It's not clear why dynamic array formulas don't work within Excel tables (probably due to the specific syntax of structured references), but anyway, these two very useful things are not compatible:(

To confirm the root cause of the problem, click the icon with an exclamation mark and you will see this text in the first line: Overflow range in the table .

The best thing you can do in this case is to convert the table to a range. To do this, right-click anywhere within the table and click Table > Convert to Range . Alternatively, you can move the formula outside the table boundary.

Overflow range unknown

Cause : Excel cannot determine the size of the overflow array.

Solution : Try to formulate different formulas for your task.

When using volatile functions such as RANDARRAY, RAND, or RANDBETWEEN with dynamic array functions, a #SPILL error may occur because the array returned by the volatile function changes between calculations in the spreadsheet and the "wrapper" function cannot determine its size.

In this case, the error message appears as: Overflow range is unknown .

For example, the following formula throws a #SPILL! error because the RANDBETWEEN output is constantly changing, and SEQUENCE does not know how many values ​​to generate:

=SEQUENCE(RANDBETWEEN(1,100))

Excessive overflow range

Cause : Excel cannot output an overflow array because it goes beyond the edge of the spreadsheet.

Solution : Instead of computing the entire column, refer to used ranges, individual cells, or add @ operators to perform implicit intersections.

This is the most complex case, depending on your goals and the structure of the worksheet, you may need to adjust the formula in different ways. Here we will demonstrate a common approach with a very simple example.

Suppose you have a column of numbers in column B and you want to return 10% of those numbers in column C.

In traditional Excel 2019 and earlier, the following formula works fine (in the left part of the figure below, it is located in cells C2 to C7):

=B:B*10%

In Dynamic Excel 365, the same formula triggers a #SPILL error.

The reason for the error is that the overflow range is too large .

Why do formulas in new Excel get broken? Because implicit intersection is no longer silently executed in the background. The term sounds mysterious, but the logic behind it is simple - reducing multiple values ​​to a single value.

In versions prior to 365, this was the default behavior of Excel—because the cell can only contain one value, the formula is forced to return a single result. In our example, even if we provide a whole column (B:B), Excel handles only a single value that is on the same row as the formula. Therefore, the formula in C2 calculates 10% of the median value in B2, the formula in C3 calculates 10% of the median value in B3, and so on.

In Dynamic Array Excel, the default behavior differs - any formula that may return multiple results will automatically overflow it onto the worksheet. In our case, Excel multiplied each cell in column B by 10%, resulting in over a million results (more precisely, 1,048,576), trying to output all of these results into column C starting from C2, but reached the end of the sheet grid - hence a #SPILL error. Guess what would happen if we put the formula in C1? Yes, it will work, because this time there are enough cells to fill - the overflow range will occupy exactly 1,048,576 cells!

Solution 1. Reference scope instead of columns

This is a very reasonable approach - don't reference the entire column, but only the relevant data. In addition to fixing #SPILL errors, it also helps save Excel resources and improve performance.

For our example dataset, the formula is:

=B2:B10*10%

This formula is entered into only one cell (C2). The result is a dynamic array that will automatically overflow into multiple cells:

Notice. Since Excel tables do not support dynamic arrays, this solution is only available for normal ranges.

Solution 2. Calculate a single cell and copy the formula downward

This is the traditional method - refer to a cell on the same row and copy the formula downwards.

In our case, the following formula is in C2, which you can then drag down to C10:

=B2*10%

Unlike dynamic array formulas, this traditional formula style is equally effective in ranges and tables:

Solution 3. Apply implicit intersection

This approach is a little more complex—using the implicit intersection operator (@ symbol), which was introduced in Excel 365 as part of dynamic array updates.

In your formula, insert the @ character before the range/column/row reference, function, or expression where you want to reduce the array or range to a single value. Enter the formula into the topmost cell and drag it down to multiple rows as needed.

For our example dataset, the formula takes the following form:

=@B:B*10%

Since the result is a single value, not a dynamic array, this formula can be used both in ranges and in tables. However, in the latter case, structured references would be a more elegant and efficient solution:

=[@Sales]*10%

To some extent, this explains why the "@" character appears occasionally in formulas created in older versions - in order to maintain compatibility. Because formulas cannot overflow to multiple cells in Excel before dynamics, the implicit intersection operator ensures the same behavior when the formula is imported into the dynamic array Excel.

My personal advice is to use this approach with caution, as it disables a new feature of Excel that is very efficient and useful in many scenarios.

This is how to troubleshoot and fix #SPILL errors in Excel. In the following articles, we will investigate and resolve this error in some popular formulas such as VLOOKUP, INDEX MATCH, SUMIF, etc. Thank you for reading, please stay tuned!

The above is the detailed content of #SPILL! error in Excel - what it means and how to fix. For more information, please follow other related articles on the PHP Chinese website!