The #SPILL error is a widespread Excel problem that affects most individuals who utilize Office 365 Excel versions. The newest release of Excel under the Office 365 license contains a collection of dynamic array formulas. Unlike a standard Excel formula, complex formulations can do numerous operations and produce different attributes simultaneously. In this article, we will demonstrate seven different methods to correct a spill error in Excel.
What Is a Spill (#SPILL!) Error in Excel?
A spill range is a set of cells that contains the results. A #SPILL! error happens if something else on the worksheet stops the range from benign filled. Mainly a #SPILL! error is an error that happens when a formula generates many outcomes but is unable to display all of those on the same page.
What Generates Spill (#SPILL!) Error?
Whenever a spill range is obstructed by something like that on the spreadsheet, a #SPILL! error appears. The #SPILL error happens when the range in which the formula’s result is to be presented is blocked by other data. Whenever the cells inside the spilled range include text, space, or are combined, it can be prevented. If there’s not enough capacity for Dynamic Array Functions to spill any output, #SPILL! errors arise. Dynamic arrays are configurable arrays that enable formulas to produce varied options for a variety of spreadsheet cells relying on a specific cell computation. With the introduction of dynamic arrays in Excel 365, any formula that generates multiple computations “spills” those results into adjacent cells.
Read More: Errors in Excel and Their Meaning
How to Correct a Spill (#SPILL!) Error in Excel : 7 Easy Fixes
To fix the #SPILL! error, first we need to identify the problem message that is displayed. Let’s look at some of the scenarios where you could get the #SPILL! issue and how to correct them. To correct the error we are going to use the following dataset, which contains some items in column B, their quantity in column C, and the total sales of each item in column D. Suppose, now we want to use some formula in a different column. Now, let’s see the methods to correct a spill error in Excel.
1. Correct a Spill Error Which Shows Spill Range Isn’t Blank in Excel
When the data that is obstructing the Spill range is plainly visible. Consider the following scenario. A #SPILL! error is raised when the formula is applied to the data because there is text or some data inside the spill range. If you click on the yellow triangle you have the error message which shows that ‘Spill range isn’t blank’. This indicates that a value or formula is blocking the dynamic array formula’s spill range.
1.1. Delete Data That Is Preventing the Spill Range from Being Used
When you apply any formula to the cells in the Spill range that already have data in them, you will get a #SPILL! Error. Suppose, we want to use a simple formula in column F. For this, follow the actions to correct the problem.
- Firstly, select any cell in column F, where you want the formula.
- Secondly, type the formula there.
=D5:D9- As we can see there is data in cell F7.
- Further, if we press Enter, we will get the #SPILL! error, and when we put our cursor on the error indicator it will show a message ‘A cell we need to spill data into isn’t blank’.
- Now, to correct the error, just clear the data content in cell F7. And just removing the data from that cell will solve the problem.
- Sometimes, there is no data shown in that range where you want to put the formula but still get the error.
- If the spill range is obviously clear but the spill problem persists, select Select Obstructing Cells from the error message’s drop-down menu.
- After that, this will show you which cells are blocking the spill range. As we clearly see, in the formula bar there is data in that particular cell, and the cell’s font color is white. That’s the reason the data is not visible in our eyes.
- Furthermore, change the font color to black and remove the content.
- Finally, we will be able to see the result and the error will disappear.
1.2. Remove the Custom Number “;;;” Formatting from Cell
When a customized number format ‘;;;’ is performed on a cell, there is indeed a risk that the #SPILL! errors may appear. In such circumstances, follow the steps to correct them.
- Firstly, click on the cell that shows the error. In our case, the error is in cell F5.
- Secondly, click on the error messages drop-down menu which is an exclamation mark covered by a yellow triangle. And, select Select Obstructing Cells.
- This will highlight the cell in which we faced the blockage.
- Next, right-click on that cell and go to the Format Cells options.
- This will take you to the Format Cells dialog box.
- Choose Number and select Custom from the Category. And you can see on the right-hand side the Type of the format is ‘;;;’.
- Now, change the Type from ‘;;;’ to ‘General’.
- Then, click on the OK button.
2. Merged Cells in Spill Range to Correct a Spill (#SPILL!) Error in Excel
Suppose, we use the UNIQUE function in cell F5, to get the unique values from cell range D5:D9 and get the error because the Spill range has merged cell. We type the formula at the cell. The formula we are using is:
=UNIQUE(D5:D9)
Let’s see the procedure to fix the #SPILL! error.
- In the beginning, from the error message drop-down menu choose Select Obstructing Cells.
- This will highlight the cell that caused the error. And, we can see that cells F6 and G6 are merged that’s why the error occurs.
- To fix this, go to the Home tab from the ribbon.
- Select the cells that you want to unmerge. So, we select cells F6 and G6.
- After that, click on Unmerge Cells from the Merge % Center drop-down menu under the Alignment category.
- And finally, the error will disappear and you can see the result in column F. And the formula is in the formula bar.
3. Correct Excel Table with a Spill Range Error in Excel
Excel Tables do not allow dynamic array formulas. Assume that we want to sort the sales, for this, we are using the SORT function in Excel. If you encounter the #SPILL issue on an Excel table with the message spill range in the table as seen below, you have to follow the steps shown down.
- To begin, click on the error and the formula will show in the formula bar.
=SORT(D5:D9)- Then, if we click on the yellow triangle, we will get the error message which is ‘Spill range in table’.
- To get rid of this error, we have to select the whole table.
- After that, go to the Table Design from the ribbon.
- Next, click on Convert to Range from the Tools group.
- A pop-up window will appear and ask Do you want to convert the table to a normal range?
- Click on Yes.
- And, that’s it. The error will vanish and the function will work properly and show the result in column E.
4. Unknown Spill Range Correction in Excel
The spill error is triggered if Excel is unable to determine the size of the spilled array. When the size of the spilled array varies and Excel is unable to determine the size of the spilled range, this error occurs. The formula can sometimes cause a dynamic array to enlarge between computation rounds. The #SPILL! error will occur if the size of the dynamic array changes throughout the computation passes and does not balance out. This issue is most commonly noticed when using random functions like RANDARRAY, RAND, or RANDBETWEEN with Dynamic Array functions like SEQUENCE. For example, in cell F5 we are putting the formula below.
=SEQUENCE(RANDBETWEEN(1,100))While pressing the Enter key from the keyboard it shows the error message ‘Spill range is unknown’.
The only way to repair this formula’s inaccuracy is to use a new formula for your calculations.
Read More: How to Remove Error in Excel
5. Spill Range Is Too Big Error Correction
When Dynamic Array was not available, Excel used a feature called implicit intersection, which compelled the formula to deliver a single result even if it had the ability to provide numerous outcomes. Let’s imagine you’re trying to figure out what’s causing the problem and you find that the spill range is too big, as seen below. Assume the following example. The dataset of the example contains some items in column B, and the total sales of each item in column C and we want to find the result of a commission of 7% on each item. For this, we need to use the formula down.
=C:C*7% The formula is used with the automatic overlap in Excel 2016, 2017, 2019, or prior editions, the outcome will have resulted correctly. But in Excel 365, you will see the error as shown in the picture below.
5.1. Create an Implicit Intersection to Fix a Spill Error Using “@” Operator
The dynamic array will apply to an entire B column whenever we specify =C:C. Alternatively, we may use the @ operator to compel Excel to enforce implied overlap.
STEPS:
- First, select the cell D5 and substitute the formula
=@C:C*7%- Then, press Enter. The formula will be shown in the formula bar.
- The formula will indeed be assigned to a specific cell because the implied overlap is included. To make the formula longer.
- After that, drag the Fill Handle down to copy the formula over the range. Or, double-click on the plus ‘+’ sign. This also duplicates the formula.
- And, that’s it! You can see the result in column D which is the resulting column.
5.2. Fix a Spill Error by Referencing Range Instead of Columns
We are referring to column C in the formula =C:C*7%. Instead, use the formula below to refer to a specific range.
=(C5:C9)*7%And, that’s all.
Read More: How to Fix #REF! Error in Excel
6. Fix Out of Memory Spill (#SPILL!) Error in Excel
If the error indicates Out of Memory while you are attempting to figure out what’s causing the #SPILL problem, it is because the dynamic array formula you are using refers to a huge range, and Excel runs out of memory, resulting in the #SPILL! error. To get around the problem, try referring to a narrower range.
Read More: [Fixed!] ‘There Isn’t Enough Memory’ Error in Excel
7. Unrecognized or Fallback Spill (#SPILL!) Error Correction
Even if Excel does not detect or reconcile the origin of the problem, you might obtain a #SPILL! error. Sometimes, Excel is unable to identify or reconcile the cause of this error. In such circumstances, double-check the formula that has all the necessary arguments and ensure that all function arguments are valid.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will assist you to correct a spill error in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.
