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.

**Read More:** [Fixed!] ‘There Isn’t Enough Memory’ Error in Excel

**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.

**Download Practice Workbook**

You can download the workbook and practice with them.

## Related Articles

**<< Go Back To Excel Formula Errors | Errors in Excel | Learn Excel**