Excel formulas make it easier for users to handle data efficiently, perform completed calculations, and analyze with ease.
However, while working with dynamic arrays in Excel, you might have encountered the #SPILL error, accompanied by the message – Spill range is too big.
It particularly happens when a formula generates several outcomes but is unable to output them all onto the sheet at once.
In order to ensure the accuracy and integrity of your Excel sheets, it’s important to fix this error, which we will help you with. Let’s begin.
How to Correct or Fix #SPILL! Error in MS Excel
With the Microsoft Office 365 subscription, you can access a collection of formulae that allow dynamic arrays in Excel 365 and Excel 2019 versions.
In contrast to a standard formula, these can perform numerous calculations and return various values at the same time.
If there is more than one result, they are entered into cells adjacent to the formula cell, which Excel refers to as Spilling.
However, sometimes, it shows an error and cannot present any output.
Why is My Formula Spilling in Excel?
#SPILL error occurs when:
- Excel is unable to define a spill range.
- Some data is already there in one or more of the spill range cells and therefore there is no blank range.
- There are several fused cells in the spill range.
- Excel tables do not support dynamic arrays.
- The spreadsheet’s bounds are crossed by the spill range.
- Your formula is predicted to produce many values.
- A space or a non-printing character is invisible and present in one or more of the spilled cells.
- Spill range is blocked by the same formula that is replicated throughout every column.
Do note that Excel limits the size of the spill range to prevent unintended overwriting of data.
Let’s quickly go through the solutions to get your Excel running properly:
Quick Tip: The best way to ensure the exact reason behind the Spill error is to click on the yellow exclamation sign beside the error. In the drop-down menu, Excel will show the primary cause for you to fix your issue accordingly.
Quick Answer
Clear the contents of all the cells where the formula is trying to spill its results to resolve the error in Excel.
Method 1: Empty Cells in Spill Range
It is important to clear out any entries in the spill range area or you will not get your formula results. Follow the steps to do so:
1. Open Excel and find any entries available under the spill range.
For example, here, rows 9 and 10 come under the spill range.
2. Select the cells and hit Delete.
Now the cells will become empty and you will be able to get your results without #SPILL error.
Also Read: 10 Ways to Fix Excel Opening in Protected View Stuck
Method 2: Remove Formula Spilling inside Tables
If you have inserted a dynamic array formula inside your table which is not supported in Excel, you will get the spill error. Follow the steps to remove the dynamic array:
1. Select your rows and click on Table Design from the top menu.
2. Click on Convert to Range.
3. Click on Yes to confirm and your table will be converted to the normal range.
Method 3: Clear Merged Cells
One or more merge cells have been obstructing your spill range and therefore you might be getting an error.
1. Select the merged cells under the spill range and click on Merge & Center from the menu.
If the merged cells are not visible, click on the exclamation sign under spill error, select Select Obstructing Cells, and then click on Merge & Center from the menu again.
This way your merged cells will be cleared.
Also Read: How to Unprotect Excel Workbook Without Password
Method 4: Remove Second Dynamic Formula from Spill Range
Your first formula cannot determine any result if you already have a second formula under your cells. So, to remove the second volatile formula:
1. Select the row with the second formula.
2. Remove or replace the formula from the cells.
Method 5: Remove Big Spill Range
Lookup if your spill range error is that it is too big. If so, this means your range extends beyond the tables in Excel, which usually happens when you have not entered the correct formula. To fix this:
1. Click on the row where you have entered the formula.
2. Insert an @ symbol after = to form an intersection.
Also Read: 3 Easy Ways to Remove Hyperlinks in Excel Quickly
Why Spill Range Has Merged Cells?
You may have accidentally merged two cells to create a larger cell to insert your data, which can block your spill range and cause an error.
To fix this issue, follow the steps under Method 4: Clear Merged Cells from the previous heading.
How to Remove Spill Formula in Excel
If you do not wish to use the spill formula in Excel, simply hit Delete under the selected cell and your formula will be removed.
How to Disable Spill Feature in Excel
Unfortunately, you cannot disable the Spill feature in Excel. With the new update, it is automatically available with the Dynamic Array feature.
However, you can:
- Rewrite the formula to make sure it only gives results for one row.
- To force the formula to return a single row, rewrite it using the @ command.
We hope our guide helped you fix the issue of spill range is too big in Excel.
If you have any queries or suggestions, do let us know in the comments section, and stay connected to TechCult to learn more about MS Excel and other tools.
