Have you encountered the notorious “#SPILL!” error in Microsoft Excel while performing multiple calculations? Feeling frustration as formulas gone awry? Well, you are not alone in this journey. This article describes what spill error means, offering insights into its causes, and most prominently how to fix #SPILL error Excel to continue with your calculations.
To repair & recover corrupted Excel file data, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Try Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
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 Is Spill Error 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. The spill error in Excel generally occurs when the formula generates numerous results but can’t output them on your Excel worksheet.
Below you can see the real interface of this error.
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 Is the Shortcut for Spill in Excel?
When you put a formula that returns numerous values into a cell, MS Excel spills the results automatically into neighboring cells. This is a feature of dynamic arrays and it is known as “spilling.”
Ctrl + Shift + Down Arrow is a quick keyboard shortcut to select the first cell in the spill range.
Reasons for 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
#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.
#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 recover the entire data in the preferred location.
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 to Avoid Spill Error 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.
User Questions & Answers
How Do I Identify the Cell Causing this Error?
You can identify the specific cell causing this error by using the Evaluate Formula tool. It will help you to check for array formula errors to identify the source of the #spill error.
Why Is Excel Trying to Spill?
Excel trying to #SPILL because there aren’t enough cells for the formula to return the results.
Can Spill Error Lead to Data Loss?
No, Spill error does not lead to data loss, it only disrupts the calculations in the Excel worksheet and impacts the accuracy of results.
What Is the Symbol for Spill in Excel?
The pound sign (#) is the symbol for Spill in MS Excel.
In your MS Excel journey, experiencing the #spill bug is almost inevitable. Nevertheless, equipped with the knowledge & troubleshooting methods outlined in this blog, you can overcome this challenge and fix #spill error in Excel effortlessly.