Seeing the Excel SPILL error while doing multiple calculations on the MS Excel in the latest office 365 subscription and wondering what this error is and how to fix Spill Excel error.
Then this article will include the entire potential fixes which help you to get past the SPILL error and continue with your calculations.
To 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:
- Download 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.
With the advent of Dynamic Array in MS Excel, there has been quite a revelation of newer changes and errors appearing. This has bought #SPILL! Error, when the formula returns out-of-range results. This calls for expansion of the range of arrays and this is caused by multiple reasons.
If you are wondering what spill error means in Excel, check out the next segment and the possible solutions available with us.
Why 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 MS Excel SPILL Error?
#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.
But before starting with the solutions it is suggested to first recognize the error message by clicking the yellow little triangle inside the cell next to the error. And then start with the solution mentioned related to the error.
#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
Certain functions are unstable and cant 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
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.
Excel spill error 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 File Recovery Tool.
This professional tool is best for easy repair and recovery of corrupted, damaged, or inaccessible Excel files. This is a unique tool to repair multiple excel files in one repair cycle and recovers 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:
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.