How to Correct a Spill (#SPILL!) Error in Excel (7 Easy Fixes)

Let’s look at some of the scenarios where you could get the #SPILL! issue and how to correct them. We are going to use the following dataset, which contains some items in column B, their quantity in column C, and the total sales of each item in column D.

Case 1 – Correct a Spill Error Which Shows Spill Range Isn’t Blank in Excel

A #SPILL! error is raised when the formula is applied to the data because there is text or some data inside the spill range. If you click on the yellow triangle, you have the error message which shows that ‘Spill range isn’t blank’. This indicates that a value or formula is blocking the dynamic array formula’s spill range.

Fix 1.1. Delete Data That Is Preventing the Spill Range from Being Used

When you apply any formula to the cells in the Spill range that already have data in them, you will get a #SPILL! Error. Consider the following scenario:

Select any cell in column F, where you want the formula.
Type the formula there.

=D5:D9

As we can see there is already existing data in cell F7.

If we press Enter, we will get the #SPILL! error, and when we put our cursor on the error indicator it will show a message ‘A cell we need to spill data into isn’t blank’.

Clear the data content in cell F7.
Removing the data from that cell will solve the problem.

Sometimes, there is no data shown in that range where you want to put the formula but still get the error.

Choose Select Obstructing Cells from the error message’s drop-down menu.

After that, this will show you which cells are blocking the spill range. As we clearly see, in the formula bar there is data in that particular cell, and the cell’s font color is white, making it invisible against the background.

Change the font color to black and remove the content.
We will be able to see the result and the error will disappear.

Fix 1.2. Remove the Custom Number “;;;” Formatting from Cell

Click on the cell that shows the error. In our case, the error is in cell F5.

Click on the error message’s drop-down menu (an exclamation mark covered by a yellow triangle).
Go to Select Obstructing Cells.

This will highlight the cell in which we faced the blockage.
Right-click on that cell and go to the Format Cells options.

This will take you to the Format Cells dialog box.
Choose Number and select Custom from the Category. And you can see on the right-hand side the Type of the format is ‘;;;’.

Change the Type from ‘;;;’ to ‘General’.
Click on the OK button.

Case 2 – Merged Cells in Spill Range

Suppose, we use the UNIQUE function in cell F5, to get the unique values from cell range D5:D9 and get the error because the Spill range has merged cell. We type the formula at the cell. The formula we are using is:

=UNIQUE(D5:D9)

Let’s see the procedure to fix the #SPILL! error:

From the error message drop-down menu, choose Select Obstructing Cells.

This will highlight the cell that caused the error. Since cells F6 and G6 are merged, the error occurs.
Go to the Home tab from the ribbon.
Select the cells that you want to unmerge.
Click on Unmerge Cells from the Merge & Center drop-down menu under the Alignment category.

The error will disappear and you can see the result in column F.

Case 3 – Correct Excel Table with a Spill Range Error in Excel

Assume that we want to sort the sales with the SORT function in Excel. If you encounter the #SPILL issue on an Excel table with the message spill range in the table as seen below, you have to follow these steps:

Click on the error and the formula will show in the formula bar.

=SORT(D5:D9)

Click on the yellow triangle to get the error message which is ‘Spill range in table’.

Select the whole table.
Go to the Table Design from the ribbon.
Click on Convert to Range from the Tools group.

A pop-up window will appear and ask “Do you want to convert the table to a normal range?”
Click on Yes.

The error will vanish, and the function will work properly and show the result in column E.

Case 4 – Unknown Spill Range Correction in Excel

The spill error is triggered if Excel is unable to determine the size of the spilled array. A formula can sometimes cause a dynamic array to enlarge between computation rounds. The #SPILL! error will occur if the size of the dynamic array changes throughout the computation. This issue is most commonly noticed when using random functions like RANDARRAY, RAND, or RANDBETWEEN with Dynamic Array functions like SEQUENCE. For example, in cell F5 we are putting the formula below:

=SEQUENCE(RANDBETWEEN(1,100))

While pressing the Enter key from the keyboard it shows the error message ‘Spill range is unknown’.

The only way to repair this formula’s inaccuracy is to use a new formula for your calculations.

Read More: How to Remove Error in Excel

Case 5 – Spill Range Is Too Big Error Correction

When Dynamic Array is not available, Excel used a feature called implicit intersection, which forces the formula to deliver a single result. Let’s imagine you’re trying to figure out what’s causing the problem and you find that the spill range is too big, as seen below. Assume the following example. The dataset of the example contains some items in column B, and the total sales of each item in column C and we want to find the result of a commission of 7% on each item. For this, we need to use the formula below.

=C:C*7%

The formula is used with the automatic overlap in Excel 2016, 2017, 2019, or prior editions, the outcome will have resulted correctly. But in Excel 365, you will see the error as shown in the picture below.

Fix 5.1. Create an Implicit Intersection to Fix a Spill Error Using “@” Operator

The dynamic array will apply to an entire B column whenever we specify =C:C. Alternatively, we may use the @ operator to force Excel to use implied overlap.

STEPS:

Select the cell D5 and substitute the formula.

=@C:C*7%

Press Enter. The formula will be shown in the formula bar.
The formula will be assigned to a specific cell because the implied overlap is included. To make the formula longer.

Drag the Fill Handle down to copy the formula over the range.

You can see the result in column D which is the resulting column.

Fix 5.2. Referencing Ranges Instead of Columns

We are referring to column C in the formula =C:C*7%. Instead, use the formula below to refer to a specific range:

=(C5:C9)*7%

This fixes the error.

Read More: How to Fix #REF! Error in Excel

Case 6 – Fix Out of Memory Spill (#SPILL!) Error in Excel

If the error indicates Out of Memory while you are attempting to figure out what’s causing the #SPILL problem, it is because the dynamic array formula you are using refers to a huge range, and Excel runs out of memory, resulting in the #SPILL! error. To get around the problem, try referring to a narrower range.

Case 7 – Unrecognized or Fallback Spill (#SPILL!) Error Correction

Sometimes, Excel is unable to identify or reconcile the cause of this error. In such circumstances, double-check the formula that has all the necessary arguments and ensure that all function arguments are valid.