How to Fix #SPILL Error Excel (5 Easy Ways)

If you are experiencing #SPILL error Excel while performing multiple calculations on Microsoft Excel and wondering how to get rid of it, then check out this comprehensive guide about this error.

This article describes complete info regarding what does spill mean, why this error occurs & how to correct spill error in Excel to continue with your calculations.

Quick Solutions	Step-By-Step Solutions Guide
#1: When the SPILL Range is Covered	Choose any cell around the spill range till you see the borders…Complete Steps
#2: Spill Range is Not Known	Using a function such as =SEQUENCE (RANDBETWEEN(1, 100))…Complete Steps
#3: Excel Tables are not Dynamic Arrays Compatible	Excel tables are prone to return #SPILL! Errors in all those…Complete Steps
#4: Spill Range is way too Large	It is practically viable to quote a formula that creates a spill range off. For instance, the formula =A: A+1…Complete Steps
#5: Securing Implicit Intersection	Long before Dynamic Arrays were a thing, it was the implicit intersection method…Complete Steps

What Does #SPILL Error Mean in Excel?

With the arrival of Dynamic Array in MS Excel, there has been quite a revelation of newer changes and errors appearing. This has bought #SPILL! Error.

Below you can see the real interface of this error.

The spill error in Excel generally occurs when the formula generates numerous results but can’t output them on your Excel worksheet.

When this error takes place, it simply means that you can’t continue with your calculations in Excel. Hence, you need to correct this issue as soon as possible.

What Causes Excel #SPILL Error?

A spill error occurs when a Spill range is covered by some object on the worksheet. This can range from entering a formula on a specific range, but this is blocked by existing data already present in the worksheet.  Therefore, the solutions in regards to the problem see clearing any blockages.

There is one more reason why one would see the #SPILL error. On clicking on the Error indicator, you can check the main cause of the error.

How to Fix #Spill Error Excel?

#SPILL error comes with its fair share of fixes. Check each one of these with respect to the different cases involved. With that said and done, here are some productive ways how to fix spill errors in Excel.

#1 – When the SPILL Range is Covered

 Considered the most manageable problem to resolve, the formula entered should return more values. However, in this case, these returns #SPILL! since there is a possible blockage in the spill range.  Clearing the #SPILL! The range is the easiest way out. The steps include,

• Choose any cell around the spill range till you see the borders.
• Make sure to have all cells in the spill range remains empty. The box should not even have space as they are assigned as a Character as per ASCII.
• This removes the character/ obstruction causing the problem
• The UNIQUE function automatically fills in the result

Also Read: How To Fix #VALUE! Error in Excel [EASY TRICKS]

#2 – Spill Range is Not Known to Fix Excel Spill Error

Certain functions are unstable and can’t be accommodated even with a dynamic array function. This is because the dynamic formula does not function with an unknown array length. Thus, using a function such as,

• =SEQUENCE (RANDBETWEEN(1, 100))

Since the RANDBETWEEN function is not stable, thus making SEQUENCE segment has an unknown length. In short, avoiding dynamic array formulas for unknown ranges would cut short #SPILL!

#3 – Excel Tables are not Dynamic Arrays Compatible

Excel tables are prone to return #SPILL! Errors in all those roles with Dynamic arrays. Therefore, the simplest and the most productive way would be to remove the Excel table by changing it to a normal range. This can be done using the Convert to Range button on the Table Design Tab. The other way would be to use an alternative formula.

#4 – Spill Range is way too Large

It is practically viable to quote a formula that creates a spill range off the edge of a worksheet. For instance, the formula,

• =A: A+1

In this case, if the entered row is anything other than 1, the result would return a #SPILL! And technically with a message showing “Spill range is too big”.

This is because the spill range includes the maximum limit of rows available in MS Excel and would run straight to the bottom of the sheet. On the other hand, using the SEQUENCE formula and creating an inclusive array of 17,000 columns would ensure keeping in the range.

Since the Excel sheet comprises only 16,384 columns, the formula of =SEQUENCE(1,17000) would return #SPILL! Error. Thus, make sure to not include formulas that may overshoot the range limit of Excel sheets.

Also Read: VLOOKUP Not Working In Excel

#5 – Securing Implicit Intersection to Fix #Spill Error Excel 

Long before Dynamic Arrays were a thing, it was the implicit intersection method that Excel used to ensure the returned results are within the limit. While considering a non-dynamic spreadsheet, this would come up with no errors. In case the same formula is entered in a Dynamic Excel sheet, this may leave a #SPILL error.

Spill error Excel message wouldn’t have appeared, had this been on the editions before 2016. This is because the implicit intersection would have restricted the formula for showing more results. But with the Dynamic arrays in place, the formula result would surely be showcasing #SPILL!, since this would be crashing with the other results. To fix the problem, using @ before entering the formula would curtail the error from appearing.

Best Way to Repair Corrupted Excel Workbook

If meanwhile performing the task or fixing Excel errors unfortunately you lose your Excel data then you can use Excel Repair & Recovery Tool.

This professional tool is best for easy repair and recovery of corrupted or damaged Excel workbooks. This is a unique tool to repair multiple excel files in one repair cycle and recovers the entire data in the preferred location.

Download       Buy Now

* Free version of the product only previews recoverable data.

It is well capable of fixing Excel file corruption issues and various errors frequently encountered by users. It allows us to easily restore entire data including the charts, worksheet properties cell comments, and other data. You can try this tool’s free demo version.

Steps to Utilize Excel File Recovery Tool:

How Do You Avoid Spill in Excel?

After getting stuck with an Excel spill error, you might have come to know how annoying is this. So, if you want to avoid this Excel error in the near future then you should insert the symbol ‘@’ each time before you type each formula.

Doing this will simply enable the feature known as “implicit intersection” and it will reduce the multiple values to a single value automatically.

Final Thoughts

Excel spill error has been a long-standing one, and even more since the advent of Dynamic Arrays. it is estimated the solutions given work for you to solve the SPILL error in Excel.

Follow the given tricks one by one till you find the one that works for you.

I hope you have found this tutorial helpful. If you are facing any other issue related to this specific one then do let us know by sharing your experience on our social media FB and Twitter Pages.