Pinterest Pixel

How to fix the #SPILL! error in Excel formulas

#SPILL Excel mainly occurs when the range in which the result of formula will be displayed is... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to fix the #SPILL! error in Excel formulas | MyExcelOnline How to fix the #SPILL! error in Excel formulas | 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 SPILL-error.xlsx

 

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
See also  AND Formula in Excel

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.

Top 20 Common Excel Problems Solved

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.

Top 20 Common Excel Problems Solved

Here, B12 already contains text!

Top 20 Common Excel Problems Solved

Solution:

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

Top 20 Common Excel Problems Solved

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.

How to fix the #SPILL! error in Excel formulas

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

How to fix the #SPILL! error in Excel formulas

 

Example 2: Spill range contains merged cells

See also  Excel Tricks: How to Find Unique Values in Excel

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.

How to fix the #SPILL! error in Excel formulas

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

How to fix the #SPILL! error in Excel formulas

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

How to fix the #SPILL! error in Excel formulas

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

How to fix the #SPILL! error in Excel formulas

The result is now visible and the error disappears!

How to fix the #SPILL! error in Excel formulas

 

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.

How to fix the #SPILL! error in Excel formulas

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.

See also  Best Tips & Tricks on Using Nested IF Statement in Excel

Hence, the error!

How to fix the #SPILL! error in Excel formulas

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

How to fix the #SPILL! error in Excel formulas

 

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.

How to fix the #SPILL! error in Excel formulas

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.

How to fix the #SPILL! error in Excel formulas

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.

How to fix the #SPILL! error in Excel formulas

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

How to fix the #SPILL! error in Excel formulas

 

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.

See also  SORTBY Formula in Excel

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

 

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

If you like this Excel tip, please share it
How to fix the #SPILL! error in Excel formulas | MyExcelOnline How to fix the #SPILL! error in Excel formulas | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...