#SPILL error in Excel | MyExcelOnline

#SPILL Excel mainly occurs when the range in which the result of formula will be displayed is being obstructed by some other data. It can be blocked by when the cells in the spilled range contain some text, space, or are merged.

Once you know the reason why Excel is producing SPILL errors, you can simply fix the problem and get rid of this error.

 

What does SPILL mean?

Dynamic arrays come with a package full of benefits but it also includes the user to a new error – SPILL! These formulas produce multiple results and display them to the neighboring cells.

SPILL error is caused when a formula with multiple results cannot display its output array as those cells already contain some data.

A simple solution to this problem is to clear contents of the cells in the spill range.

Follow this detailed tutorial on #SPILL Excel and download this Excel workbook to practice along and understand better:

DOWNLOAD EXCEL WORKBOOK

 

How to fix #SPILL Excel?

The different scenarios when you can encounter this error are:

  • Spill range isn’t blank
  • Spill range contains merged cell
  • Spill range is too big
  • Spill range in a table

Let’s look into more detail and understand the causes and solutions to the #SPILL Excel!

 

Example 1: Spill range isn’t blank

The spill range where the result will be displayed is non-empty.

In the example, we have used the UNIQUE function to extract a unique customer list in column B. Instead of displaying the result, Excel returns #SPILL! error.

#SPILL error in Excel | MyExcelOnline

To understand the reason behind this error, click on the small yellow! warning sign next to the error. Upon clicking this sign, Excel tells you the reason – Spill range isn’t blank.

#SPILL error in Excel | MyExcelOnline

Here, B12 already contains text!

#spill error

Solution:

Simply, clear any contents from the output range and you are good to go!

#SPILL error in Excel | MyExcelOnline

There can be times when you get the SPILL error but you are unable to spot the cell that contains unwanted data.

Click on Select Obstructing Cells and it will take you to the cell that is creating the problem.

#SPILL error in Excel | MyExcelOnline

This is useful when you have hidden spaces and you are unable to find them.

#SPILL error in Excel | MyExcelOnline

 

Example 2: Spill range contains merged cells

SPILL error can also occur when the result range contains the merged cells.

In this example, you can see the spill range i.e. B7:B15 is empty but still, Excel returns #SPILL error.

#SPILL error in Excel | MyExcelOnline

To be sure of the reason, let’s click on the yellow warning sign and see what it says – Spill range has merged cell.

#SPILL error in Excel | MyExcelOnline

Now, click on Select Obstructing Cells and it will take you there.

#SPILL error in Excel | MyExcelOnline

Go to Home > Merge & Center to unmerge the cells.

#SPILL error in Excel | MyExcelOnline

The result is now visible and the error disappears!

#SPILL error in Excel | MyExcelOnline

 

Example 3: Spill range is too big

This error occurs when the output range is too big and the result goes beyond Excel’s boundary. There isn’t enough space to display the result.

In this example, we are trying to calculate the discount given to the customers based on the sales amount. Column C is used to get 5% of the sales amount.

#SPILL error in Excel | MyExcelOnline

As you can see, Excel is again and producing a SPILL error and this time it is because the range is too big.

Here, Excel is trying to multiply each cell in Column B by 5% and produce a million results. It will spill the result in Column C starting from cell C7 but it will reach the end of Excel.

Hence, the error!

#SPILL error in Excel | MyExcelOnline

To solve this, simply replace the column with the relevant range and copy the formula to the output range.

#SPILL error in Excel | MyExcelOnline

 

Example 4: Spill range in a table

Excel tables do not support spilled array formulas. If you try to insert an array formula in an Excel table, it will return a #SPILL error.

In this example, we are trying to sort the amount mentioned in Column A in ascending order using the SORT function. It will return a dynamic array as a result in column B.

#spill excel

Unfortunately, we are getting a #SPILL Excel error.  This is because range A7:B15 is in fact saved as a table in Excel and dynamic functions do not work within Excel tables.

#SPILL error in Excel | MyExcelOnline

We can easily convert it into a range for our SORT function to work properly.

To do so: Right Click on any cell in the Table range > Select Table > Select Convert to Range.

#SPILL error in Excel | MyExcelOnline

This will convert the table to range and now all array functions will work perfectly as shown below!

#SPILL error in Excel | MyExcelOnline

 

Conclusion

Next time when you face #SPILL error in Excel, the best way to deal with it would be to examine the cause of the problem by clicking on the exclamation mark icon.

After that, you can easily troubleshoot the problem and fix the error effortlessly!

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn