# What is ‘#SPILL!’ error and how to correct it? – Microsoft Excel

#SPILL! error in Excel occurs when Excel cannot populate multiple cells with the formula’s calculated results to the sheet. This error is usually reported on Office 365, Office Online, or Office 2021 versions as these support dynamic arrays. A formula working perfectly in earlier Office versions (Office 2019 or below, can be called pre-dynamic Excel) may also show #spill error in Office 365 or Office 2021 versions due to the support of dynamic arrays.

## Spill in Excel

The term spilling or spill in Excel is the behavior when an **array formula** that has **multiple values as a result**, outputs or “**spills**” these results into neighboring (horizontal or vertical) cells, just not limited to the cell where the formula is present.

This spill behavior is **automatic** and **native** in newer versions of Excel and even a simple formula (without any functions) can spill results to the neighboring cells. Some users may want to disable the spill functionality but the bad news is it is not possible, but a user can **stop multiple results** that cause the spill (discussed later).

## Spill Range in Excel

The term spill range in Excel refers to the **range of the result values** returned by the formula that spills onto an Excel sheet. The concept can be made clear by the picture below:

In this picture, there are **two columns** with data. **Column B** is the **source data** with different color names, whereas, cell **D5** only holds the **unique formula** i.e., UNIQUE(B5:B10) but the formula’s result is shown from cell **D5 to D8**. Here, D5 to D8 is the **Spill Range**. If a user selects a single cell in the range, then a **blue border** will be shown **around** the **whole spill range**.

## #SPILL! Error in Excel

A user may encounter the #spill error in Excel if the dynamic arrays functionality **fails to paste** the **calculated results** in the Spill Range. To clear the concept:

- Launch
**Excel**and**create a new workbook**. - Now, fill up
**column B**with**different color**names like Red, Blue, Green, Green, Blue, and Purple. - Then, in cell
**D7**, enter**X**, and afterward, enter the**following formula**in cell**D5**:=UNIQUE(B5:B10)

- Now a user will notice a
**#spill error**in the formula cell (here, D5) because the cell D7 of the spill range is already filled with the value X. - Then delete
**X**from the**D7**and the formula cell will be clear of the #spill error.

## Check and Identify the Reason for the #Spill Error in Excel

In the above example, things look quite simple, but that is not always the case. But thanks to the nature of dynamic range functionality, every #spill error shows the **reason causing the error**. To better understand the concept:

- Repeat
**steps 1 to 3**of the above example and when the user is shown, a #spill error, click in the**formula cell**(here, D5). - Now click on the
**warning triangle**sign (shown on the side of the formula cell) and the user will notice the reason for the #spill error. Here, it will be shown as the**Spill Range isn’t Blank**.

Once the user identifies the cause of the reason, he may follow the section about the cause of the Spill error to clear the error.

### 1. Spill Range isn’t Blank

If a user is shown that the #spill error is occurring due to Spill Range isn’t blank, then this is the most basic reason to cause a #spill error in Excel.

The #spill error due to Spill Range isn’t Blank can be cleared by the following two methods:

#### 1.1 Delete the Non-empty Cells in the Spill Range

- Click in the
**formula cell**(that is showing #spill error) to show the blue border around the Spill Range. - Then
**delete**all the**non-empty**cells from the range or move them to other cells.

#### 1.2 Use Select Obstructing Cells

If a range covers a lot of cells and it is a problem to find each non-empty cell in it, then

- Click in the
**formula cell**and click on the**warning triangle sign**. - Now click on
**Select Obstructing Cells**and then**delete**the**contents**of the**problematic cells**or**move**the contents to other cells. Keep in mind if multiple cells are blocking the Spill Range, then all the obstructing cells will be selected when Select Obstructing Cells is selected.

Keep in mind that there can be some cases, where a cell may contain an **invisible character** (like a space in a cell) and due to this Excel might mark it as a non-empty cell but the user may find it difficult to find the problematic character, so, look for those cells as well. To clear a cell with an invisible character (use Select Obstructing Cells to find out the problematic cells), a user may select the cell, click on Clear (in Editing section) on the Home tab of Excel, and then click on Clear All.

### 2. Spill Range has a Merged Cells

A user may encounter the #spill error in Excel if the Spill Range contains merged cells. Here, unmerging the merged cells in the Spill Range may clear the #spill error.

- Click on the
**formula cell**to highlight the**Spill Range**. - Now try to find
**merged cells**and once found,**unmerge the cells**to clear the #spill error. - If merged cells cannot be found, click on the
**formula cell**and then click on the**warning triangle sign**(as discussed earlier). - Now click on
**Select Obstructing Cells**and then,**unmerge**the problematic cell and that may clear the #spill Excel error.

### 3. Spill Range in Table

The spill Range does not support the table and if the Spill Range is occurring in a table, then it may throw # a spill error. Here, a user has three options, first, **use another formula** (discussed at the end of the article), and second move the **formula outside **the **table**, or **convert** the **Table** to a **range**. To convert the table to a range:

- Except for the
**first cell**that holds the formula,**delete**all other**repetitions**of the**#spill error**in the column or row (if multiple #spill errors are shown). - Now
**right-click**on the**formula cell**(or any other cell in the table) and hover over**Table**. - Then click on
**Convert to Range**and afterward,**confirm**to convert the table to a range. - Now Excel will convert the table to a range and that may clear the #spill error.

Some users may not be convenient to convert a table to a range, for those users, they may look into the **TEXTJOIN** or **INDEX** functions to clear the #spill error in a table.

### 4. Spill Range is Unknown

Excel may show #spill error with the cause of Spill Range is Unknown if the formula contains a **volatile function**. Volatile functions are those functions whose **output** is **recalculated** with **every change** of the sheet. If an array formula is using a volatile function, then with every output into the spill range, the sheet will be recalculated and this will trigger a **non-ending cycle** of **recalculations**, thus resulting in Spill Range is unknown. One such example is the formula below:

=SEQUENCE(RANDBETWEEN(1,100))

The following is the **list** of the **volatile functions** of Excel:

- CELL() (depends on arguments)
- INDIRECT()
- INFO() (depends on arguments)
- NOW()
- OFFSET()
- RAND()
- RANDBETWEEN()
- TODAY()

In the case any of the volatile functions are used in the formula, then the user may **change the formula** to clear the #spill error or use implicit intersection (discussed later).

## 5. Spill Error Out of Memory

If a user is using a **complex** and **nested formula** that is referring to **large data**, then that may cause a #spill error as Excel **runs out of memory** before populating the results in the form of a Spill Range. Here, the user may **amend** the **formula** to make it less complex and refer to a smaller chunk of data.

## 6. Spill Error due to Unrecognized/Fall Back

Computing is a complex process and due to this complexity, there may arise cases where Excel **fails to determine the reason** for # the spill error. In this case, Excel may show the spill error due to unrecognized/fall back and a user may amend the formula to remove the #spill error.

## 7. Spill Range Too Big

A user may see a spill error in Excel due to **Spill Range Too Big** if the Spill Range is **going beyond the worksheet’s columns** or rows, for example, if the **following formula** is entered in Excel (except the first row), it will trigger #spill error due to Spill Range Too Big.

=A:A+1

If the above formula is entered in any row (except the first one), then it will go beyond the last row of the sheet as the sheet holds **1,048,576 rows** (Excel rows limit), whereas, the formula will try to paste to 1,048,576 rows but as it is starting from row 2 (or below), so, the sheet will require at least **1,048,577 rows** (which is not there) to past the results, so, Spill Range Too Big error.

**Another example** could be the following formula:

=SEQUENCE(1,17000)

This will also throw a #spill error as Excel has a total of 16,384 columns, so, it is not possible to post the result to 17000 columns, and hence the #spill error due to Spill Range being too big.

To solve #spill error due to Range too big, the user may **reference a range** (not the whole column or row), **copy the formula** to other cells after **applying** it to a **single cell**, or **perform implicit intersection** by using @ operator.

#### 7.1 Use Ranges to Clear the Spill Error Due to Spill Range Too Big

In place of using whole Excel columns or rows, a user may prefer the required ranges to clear the spill error due to the spill range being too big. For example, a user may encounter a #spill error for the **following formula**:

=B:B*10%

As the above formula uses a whole column, so, it may trigger the #spill error. Here, using a **range** like the following formula may clear the #spill error:

=B1:B1000*10%

#### 7.2 Copy The Formula to Other Cells After Applying It to A Single Cell

Despite applying the Spill formula to a single cell that is triggering #spill error due to the spill range being too big, a user may copy the formula to other cells after applying it to the single cell may solve the problem.

For example, the **following formula** will trigger a #spill error on Excel:

=B:B*10%

But to **clear the #spill error**, the user may enter the **following formula** in the formula cell:

=B1*10%

And **copy** (or drag) the formula to all the relevant cells.

#### 7.3 Use the Implicit Intersection

With the user implicit intersection operation, a user may **stop the spilling** of **results** to the neighboring cells and limit the current **result only to the formula cell**. It is the same functionality where the formula did not spill the results in the pre-dynamic Excel (Office 2019 or earlier). Another advantage of using implicit intersection is that it can be **used in a table** as well but with a structured reference. A user should only use this implicit intersection (with @ sign) only when there is **no other way possible**. To better understand the concept:

**Create a new sheet**in Excel and**enter**the following formula:=B:B*10%

- This will trigger a
**#spill error**. Now,**add @ just before B**in the formula, as under:=@B:B*10%

- Now the
**current result**will be only shown in the formula cell without any #spill error.

To use the implicit intersection in an Excel table, it will be better to **use** the **formula** with a **structured reference** like the below:

=[@Sales]*10%