If you are a frequent user of Microsoft Excel, then you might have faced an error called #SPILL!. This is a new kind of error that Microsoft has introduced `and it usually occurs when there is an error in the formula or a formula that produces multiple calculation results.

What exactly happens is that Excel tries to display its outputs in a spill range, but that range already contains some other data.

The data through which it is blocked can be anything from text value, merged cells, a plain space character, or even when there isn’t enough space to return the results.

The basic solution to the problem is to either clear the range of any blocking data or select an empty array of cells that don’t contain any type of data in it and can be used to enter the value of the result.

The Spill error usually occurs when calculating dynamic array formulas. The dynamic array formula is the one that outputs results into multiple cells or an array. Let’s dive in deeper to understand the cause of the spill error and how we can avoid and solve this error.

What Causes a Spill Error?

In 2018, Microsoft Excel launched dynamic arrays. Using the dynamic array Excel can handle multiple values at a time and return the results to more than one cell. Usually, dynamic arrays are resizable arrays that allow formulas to return multiple results to a range of cells on the worksheet which is based on a formula entered in a single cell in Microsoft Excel.

So, when a user enters a formula with a dynamic array, then it will return multiple results. These results automatically spill into the neighbouring cells. This behaviour of Excel is called Spill in Excel. The range of cells where the results spill into the cells is called the Spill range.

According to the source values, the Spill range will expand or contract automatically.

In case the formula is trying to fill up a spill range that has multiple results but is blocked by some other data on that spill range, then users see a #SPILL error.

As of now, Microsoft Excel has a total of 9 functions that use Dynamic Array functionality for solving problems and calculations, these include:

• SEQUENCE
• FILTER
• TRANSPOSE
• SORT
• SORT BY
• RANDARRAY
• UNIQUE
• XLOOKUP
• XMATCH

Do note that the Dynamic array formulas are currently only available on Microsoft Excel 365 online. it is not supported by any of the offline versions of Excel as of now.

The Spill errors are not only caused by any constructing data in the spill range but can also be caused by several other reasons. Below we have certain scenarios in which the spill error can occur, and how you can fix them.

Spill Range isn’t Blank

There are several different reasons which cause spill range errors. But one of the primary causes of spill error is that the spill range is not empty. To explain it better, if you try to display 10 different results in different cells, but there’s some data already in the range of cells which you have selected to display the result, then the formula will return a #SPILL! error.

Example 1

Below we have an example explaining the #SPILL error occurrence. So, we have entered the TRANSPOSE function in cell C@. This will convert the vertical range of cells (B2:B5) into a horizontal range (C2:F2). But, instead of switching the column to a row, Microsoft Excel shows us the #SPILL! error.

On clicking the formula cell, you will see a blue border indicating the spill area/range (C2:F2) that is required for the formula to display the results as shown below in the image. Notice the yellow warning sign exclamation mark, you can click on it to understand and see the reason behind the #SPILL! error.

On clicking the warning sign, you will see that the first highlighted line in grey is written as Spill range isn’t blank.

So, in the above example the problem is that the cells in the spill range D2 and E2 have text characters and arent empty, hence the error.

Solution:

The solution for #SPILL! error is pretty simple and easy. You can either clear the data entered in the spill range or you can move the formula to another location where the cells are empty and there is no obstruction.

The moment you delete or move the data, Excel will automatically show you the results of the formula. Below we have the image showing that when we clear the text in D2 and E2, the formula transposes the column to a row as intended.

Example 2:

Here is another example, where we can see the spill range appears empty, but the formula still shows the SPILL! error. Well, this is because the spill is not empty, it has an invisible space character in one of the cells.

It is almost impossible to find or locate an invisible space character in a large number of cells. As the space character looks empty only. To find such cells with unwanted or hidden data entered in the cells, you can click on the error floating (the warning sign) and then select the obstructing cells option from the menu. It should point out to the cell which isn’t empty but is appearing to be empty.

Often, the invisible character could also be in the form of text, but formatted with the same font colour as the cell’s fill colour is, or you might have mistakenly selected the same custom colour code for the cell and the text as well.

Spill Range Contains Merged Cells

#SPILL! the error can also occur when the spill range contains the merged cells. The dynamic array formula doesn’t work with the merged cells. So, you need to first fix it by unmerging the cells in the spill range or moving the formula to a different range of cells where the cells aren’t merged. Below we have an example of the same.

As you can see in the below image, even though the spill range is empty between (C2:CC8), the formula returns the Spill error. This is because cells C4 and C5 are merged.

You can confirm that the #SPILL! error is because of the merged cells by clicking on the warning sign and then verifying the cause – ‘ Spill Range has merged cell’.

Solution:

You need to unmerge the cells. So, to unmerge the cells you need to go to the Home tab, then click on the Merge and Center button and then select Unmerge Cells.

If you are not able to find the merged cells in your extra-large spreadsheet, then you can directly click on the Select Obstructing Cells option from the warning sign menu and it will jump to the merged cells.

Spill Range in Table

Spilled array formulas are not yet supported in Microsoft Excel tables. So, you need to enter the Dynamic array formula in a single individual cell. If you enter a spilled array formula in a table or in a range of cells where the spill area falls into a table, then you would simply get the spill error. If you are facing anything like this, then you need to convert the table to a normal range or move the formula outside the table.

Here we have an example, where we will enter the following spilled range formula in an Excel table, we would get a Spill error in every cell of the table and not just the formula cell. This happens because Microsoft Excel automatically copies any formula that is entered in a table to all of the cells present in the table’s column.

Also, you will get the spill error if your formula tries to spill results in a table. below we have an image explaining the same. The spill area falls within an existing table, so the formula will return the spill error.

You can confirm the reason behind the error, by clicking on the yellow colour warning sign and then seeing the error reason. It should say – ‘Spill range in table’.

Solution:

To fix the error, you will need to revert the Excel table to the range. You can do so, by right-clicking anywhere within the table, clicking on the Table and then selecting the Convert to Range option. Another way of doing it is by left-clicking anywhere within the table and then going to the Table Design tab and selecting the Convert to Range option.

Spill Range is Unknown

If Microsoft Excel fails to determine the size of the spilled array then it will trigger the spill error. Sometimes, the formula also enables a dynamic array which can resize between each calculation pass. If the size of the dynamic array is changing after each calculation passes and it doesn’t balance out, then it will also cause the #SPILL! error.

usually, this type of Spill error is seen or triggered when we are using a volatile function such as RAND, RANDARRAY, RANDBETWEEN, OFFSET, and INDIRECT functions.

For example, if we use the below formula in cell B3, we can get the Spill error.

=SEQUENCE(RANDBETWEEN(1, 500))

In the above example, the RANDBETWEEN function returns a random integer between numbers 1 and 500, and its output is continuously changing. Also, the SEQUENCE function doesn’t know how many values to produce in a spill array. hence, we get the #SPILL! Error.

As usual, you can confirm the cause of the error by clicking the yellow coloured warning sign beside the SPILL Error. It will show as the Spill range is unknown.

Solution:

You can fix the above-shown error for this formula by using a different formula for calculation.

Spill Range is Too Big

Often you might see errors like spilled range is too big. This happens when you execute a formula whose output in a spilled range is too big for the worksheet to handle and it may extend beyond the edges of the worksheet. To fix this issue, you can try referencing a specific range or one cell instead of taking up the entire columns or using the @ character so that you enable the implicit intersection.

Below we have an example, where we will be calculating the 20% of the sales numbers in column A and then return the results in column B, but instead, we get a Spill error.

The formula entered in B3 calculates the 20% of the value which is entered in A3, and then 20% of the value in A4 and it goes on. This can produce over a million results and will also spill all of them in column B starting from cell B3. However, it will reach the end of the worksheet before it reaches the end of the loop. Since there isn’t enough space to display all of the outputs, so the formula will end up showing #SPILL! error.

Below we have the screenshot where you can see the cause of the error is that the – Spill Range is too Big.

Solution:

To solve the issue of the above error, you can try changing the entire column with a relevant range or a single cell reference, or simply add the @ operator to perform the implicit intersection.

Fix1: You can simply try referring to ranges instead of referring to the entire columns. So, here we will change the entire range A: A with A3:A11 in the formula, and then the formula will automatically populate the range with the results.

Fix 2: You can replace the entire column with just the cell reference on the same row (A3), and then copy the formula down the range using the fill handle.

Fix 3: Alternatively, you can also try adding the @ operator before each of the references to perform the implicit intersection. This will display the output in the formula cell only.

Next, you need to copy the formula from cell B3 to the rest of the range.

Note: If you are editing a spilled formula, you can only edit the first cell in the spill area/range. You can see the formula in other cells of the spill range. However, they will be greyed out and cannot be updated.

Out of Memory

If by any chance you execute a spilled array formula that causes Excel to run out of memory. It will trigger the #SPILL error. So, under those circumstances, you can try referencing a smaller array or range.

Unrecognized / Fallback

A wrong formula can also give you the #SPILL! error. Microsoft Excel is designed to give an error if it does not recognize or cannot reconcile the cause of the error. So, in such scenarios, you need to double-check the formula and make sure all of the parameters of the functions are correct.

Now, you know all the causes and solutions for #SPILL! errors in Excel 365.