Excel #SPILL! error

Just imagine this: you advanced your Excel version to Microsoft Office 365 with the latest Excel updates, and the attempted and tested Excel formulas you used in previous versions have unexpectedly stopped working. Whatever you do, Excel is throwing a #SPILL error as an output. Sounds scary, right! Don't worry; in this tutorial, you will get the solution, causes, and fixes of the #SPILL error.

What is a #SPILL! Error?

"A #SPILL! error is a type of Excel formula error that occurs in your Excel worksheet when a formula generates several outputs but cannot display them all on the spreadsheet."

With Excel 365, Microsoft Excel introduced the concept of dynamic arrays. But this concept raised another problem in Excel, i.e., whenever any formula produces numerous calculations, the resulting outputs automatically "spills" those output values into adjacent cells. The range of cells that carry the resulting output is known as the spill range. And because of any reason, Excel prevents filling this range on the Excel worksheet, a #SPILL! error occurs.

In many situations, this error occurs because of some obvious mistake that can be predicted and corrected. For example, if you have created an Excel formula that will return more than one value, but the neighbouring cells are already occupied with some other data, in such case the formula will throw a #Spill error. To rectify the error, simply delete the data from the nearby cells, and the formula will successfully return the result.

But sometimes, the causes for this error are not predictable and, therefore, confusing. Some instances of facing this error are given below:

• Excel cells often contain hidden data such as a space or a non-printing character. When you try to generate multiple outputs in those cells, Excel throws the #Spill error. Since the data is not visible to the user, it becomes frustrating for them to correct the error.
• if the user tries to copy the same formula across the entire column, in such cases, the formula blocks the spill range and throws the #Spill error.
• Sometimes, you may experience this error if you run into one of the very rare features of Excel that do not support dynamic arrays.

To fix the #Spill error, you'll have to examine each issue separately and figure out the source of the problem.

How to fix #SPILL! error in Excel

As discussed above, the Excel #SPILL! error might occur due to various reasons. To determine root cause of this error, click on the warning icon (a yellow diamond with an exclamation point), and read the message in the first line highlighted in grey:

Once you have figured out the exact root of the problem, you can refer to one of the below given cases to quickly resolve the issue using the details steps:

1. Spill range blocked

This cause is the simplest one and it gets fixed easily. It occurs when your formula has to return multiple values, but instead it throws the #SPILL! Error because the spill range are pre-occupied with some other data. In the below example you will notice that the array numbers are blocking the spill range:

Solution: Select the spill range cell to fix the problem, and Excel will immediately show the spill boundaries. Check the problem and ensure all cells in the spill range are empty. Once you delete the data the numbers from the spill range cell, the SEQUENCE function will spill the output normally:

2. Excel Tables do not support dynamic arrays

Microsoft Excel version do not support Dynamic arrays in tables. In the below example as you can see, we are trying to add a dynamic array formula to an Excel Table, the SEQUENCE formula will return a #SPILL! error in all rows.

Solution: The solution to the above problem is to convert the table to a normal range or place the formula outside the table to allow it to spill. The reason why dynamic array formulas do not operate from within Excel Tables is not quite clear (possibly because of the explicit syntax of structured references). Still, anyway these two very useful things do not get along.

To confirm the cause, click on the warning icon (a yellow diamond with an exclamation point), and Excel will throw the text in the first line - Spill range in table.

In such situation, the best you could do is to convert the Excel table to a range. To do so, right-click anywhere within the table, and then click Table > Convert to Range.

Or else you can try to move your formula beyond the table boundaries.

3. Spill range is unknown

Microsoft Excel is unable to determine the size of the spilt array.

Some Excel functions are volatile and are not advised to be used with the dynamic functions because the returned output would be "unknown" to the end user. And the dynamic arrays do not support arrays of unspecified length in the present version of Excel.

Solution: Try and implement different method or formulas for your task.

When using unpredictable Excel functions unlike RAND, RANDARRAY, RANDBETWEEN combined with dynamic formulas, in most of the cases it returns a #SPILL error as a result because the array returned by a volatile function change between the worksheet's calculations and the function cannot determine its size.

In such conditions, an error message says - Spill range is unknown.

4. Spill range contains merged cells

The concept of Spilling goes wrong with the merged cells.

Solution: If Excel is throwing the #Spill range error because of merge cells, quickly unmerge the cells in/around the spilled area. Alternatively, you can move the respective formula to different cells containing no merged cells.

If spilled array contains one or more merged cells, error immediately throws an error window displaying the following message - 'Spill range has merged cell'.

In case you are finding it challenging to detect the merged cells or it taking more time than usual, you can Select the Obstructing Cells option to skip the problematic cells and it will automatically jump to the unmerged cell.

5. Spill range is big enough

In many cases, Microsoft Excel cannot return the output of a spilt array as it expands beyond the worksheet borders.

In the following formula, we are using the SEQUENCE function to create an array with 17,000 columns.

As you can see, the above formula returns a #SPILL error. This is because an Excel spreadsheet comprises of only 16,384 columns, and we are sequencing column 17000, that doesn't exist.

Solution: The solution to the above problem is to avoid references and formulas that are used to create spill ranges that do not fit the space of the Excel spreadsheet.

6. Implicit intersection (@)

Before the concept of Dynamic Arrays, Microsoft Excel applied a method known as "implicit intersection" to make sure that specified formulas that ideally returned multiple results should only return a single output. In the case of non-dynamic arrays, the same formulas return an output without any error.

But if you create the formula with dynamic arrays, there are chances that it may throw a #SPILL error. For example, in the below image, we have used the formula =$B$2:$B$5+3 and dragged the same formula down the cells. As soon as you will drag the formula you will notice, Excel has returned the #SPILL error.

If you are using the version of Excel 2016, this error would not be thrown because the implicit intersection would prevent the formula from returning multiple outputs. However, with dynamic arrays, the above concept won't work. Since the formula automatically returns multiple results that eventually crash with each other, the formula is copied down in B2:B5.

Solution: Instead of calculating the multiple results together, add the @ operator to enable implicit intersection.

Note: This solution also relates to the formulas you might find in older versions, unlike the "@" character that appears in formulas created in older versions of Excel. The '@' character is inserted to maintain compatibility between the formula so the multiple outputs don't crash into each other. Since most older formulas can't spill into multiple cells, the developers used @ to secure the same behaviour when the formula is created in an Excel version that supports dynamic arrays.

In your formula, =$B$2:$B$5+3 add the '@' character where you want to covert the array with multiple results into a single value.

Enter the formula in the first cell. Next, drag the formula down across the rows using your mouse cursor. This will copy the formula to the respective rows.

In this example, the formula will become as follows:

=@$B$2:$B$5+3

Because the above output returns a single value, not a dynamic array, you can use this formula for both ranges and tables. If you are incorporating the formula in a table, however, a structured reference would be a wise and effective option:

=[@Sales]*10%

Eureka! Now since we have covered all the various possibilities of this error. Therefore, you can confidentially troubleshoot and fix a #SPILL error in Excel.