What does #Spill Mean in Excel – How to Fix it?

  • Home
  • / What does #Spill Mean in Excel – How to Fix it?

#SPILL! Error is one of the errors you may encounter while adding an Excel formula. Did you ever face this error?

It often occurs when you apply an improper concept of spilling an array in an Excel formula.

What Does #SPILL Mean in Excel?

Whenever your formula returns more than one outcome but is unable to operate all those outcomes on the sheet, you will most likely face a #SPILL! error. Let’s have a brief overview of the #SPILL error.

In dynamic arrays, when a formula continues producing more than one result, by default, it will give you spills in adjacent cells. Any formula that handles arrays or not will by default produce spills. A spill range often appears with a range of cells that carry results. And when you find something that stops filling that range is known as a #SPILL error.

Suppose, your formula is producing more than one value and the adjacent cells contain some data then simply delete it and you will find that error gone. It might be possible that in the spilled area one or more cells have a space or a character that is not visible. To fix this issue, you must have to understand the real reason behind it.

what spill ,ean

How to Fix the #SPILL Error in Excel?

First, you need to identify why this error is occurring before finding a fix to this. You can have a look at this notification by clicking on the yellow triangle in the error cell. Below are some fixes to this error:

Spill Range in Not Empty

This heading shows that some value or formula is stopping the spill range of the dynamic array formula. Follow the steps given below to fix this error:

spill range not blank

Once the dynamic array formula, you need to clear the entire spill range. Or else you can move the dynamic array formula to some other site.

spill range not blank2

When the spill error appears even though the spill range is visible, click on the Select Obstructing option given under the error notification. It will simply select the cells stopping the spill range. You can now delete them or even remove the error.

spill range not blank3

You will see the Spill Range clearly when the spill error appears.

spill range not blank4

Under the error notification, choose the Select Obstructing Cells option.

spill range not blank5

Note that dynamic array formulas must not be dragged in the spill rage.

spill range not blank6

Spill In Tables

spill inside tables

Whenever you will try spilling in the table, a spill error will occur. Spill ranges and Excel tables are not compatible with each other because tables have different syntaxes.

spill inside tables2

For ease, you can convert the table into a range or else move the formula out of the table. Choose the table and open the Table Design option.

Click on the Convert to Range button and you will see a table without deleting the data.

Spill Range in Unknown

When Excel does not identify how small or big a range would be, you will find some formulas appear with a spill error.

spill range unknown

=SEQUENCE(RANDBETWEEN(1,10))

Using the above formula will let you make a series of columns. In this formula, the SEQUENCE argument is a dynamic array function as it returns a spill range. You will find its spilling gets disturbed when the consistent computation of the RANDBETWEEN function is done. You will encounter an error as the spill range size of SEQUENCE does not determine.

spill range unknown2

Here, you may need to find RANDBETWEEN one time in another cell B2 and paste the value in the SEQUENCE function.

The spill range contains merged cells:

When you have merged cells, spilling does not allow it. Therefore, unmerged cells are used in the spilled area or you can move the formula somewhere else.

In a spilled array, when there are one or more merged cells, you may encounter this error notification – Spill range has merged cell. When the merged cells are unidentifiable, you need to click on the Select Obstructing Cells option to move on to other cells:

Summary:

You now have a clear-cut idea of what does spill mean in Excel. Therefore, you need to be careful about things that let you face this error. Before fixing an error, finding a reason behind it is necessary.

Write your comment Here