#Spill error in Excel occurs when Excel cannot populate multiple cells with the formula’s calculated results to the sheet. This error is usually reported on Office 365, Office Online, or Office 2021 versions as these support dynamic arrays. A formula working perfectly in earlier Office versions (Office 2019 or below, can be called pre-dynamic Excel) may also show #spill error in Office 365 or Office 2021 versions due to the support of dynamic arrays.
Array Formula, Excel Arrays, and Dynamic Arrays
Array formula in Excel is a type of formula that performs its calculations on multiple values (not a single value). The final calculated result of an array formula could be an array of values or a single item (depending on the formula).
An Excel array is a multi-value output of an Array Formula. It can be said to be structured data that holds a collection of values. Excel arrays can be perfectly mapped to ranges on the spreadsheet.
Dynamic Arrays are resizable arrays that are calculated automatically and return the result values into multiple cells based on a formula entered in a single cell. Dynamics arrays broke the 30-year-old shackles of one formula one cell for Excel.
Now, any formula that returns an array of calculated result values automatically spills the results into (horizontal or vertical, depending on the formula type) neighboring cells, without any user action. In simpler words, operating dynamic arrays has become as easy as working with a single Excel cell. Moreover, any change in the source data will change the output of the array, hence the name dynamic array.
Spill in Excel
The term spilling or spill in Excel is the behavior when an array formula that has multiple values as a result, outputs or “spills” these results into neighboring (horizontal or vertical) cells, just not limited to the cell where the formula is present. This spill behavior is automatic and native in newer versions of Excel and even a simple formula (without any functions) can spill results to the neighboring cells. Some users may want to disable the spill functionality but the bad news is it is not possible, but a user can stop multiple results that cause the spill (discussed later).
Spill Range in Excel
The term spill range in Excel refers to the range of the result values returned by the formula that spills onto an Excel sheet. The concept can be made clear by the picture below:
In this picture, there are two columns with data. Column B is the source data with different color names, whereas, cell D5 only holds the unique formula i.e., UNIQUE(B5:B10) but the formula’s result is shown from cell D5 to D8. Here, D5 to D8 is the Spill Range. If a user selects a single cell in the range, then a blue border will be shown around the whole spill range.
#Spill Error in Excel
A user may encounter the #spill error in Excel if the dynamic arrays functionality fails to paste the calculated results in the Spill Range. To clear the concept:
- Launch Excel and create a new workbook.
- Now, fill up column B with different color names like Red, Blue, Green, Green, Blue, and Purple.
- Then, in cell D7, enter X, and afterward, enter the following formula in cell D5:
=UNIQUE(B5:B10)
#Spill Error in Excel - Now a user will notice a #spill error in the formula cell (here, D5) because the cell D7 of the spill range is already filled with the value X.
- Then delete X from the D7 and the formula cell will be clear of the #spill error.
Check and Identify the Reason for the #Spill Error in Excel
In the above example, things look quite simple, but that is not always the case. But thanks to the nature of dynamic range functionality, every #spill error shows the reason causing the error. To better understand the concept:
- Repeat steps 1 to 3 of the above example and when the user is shown, a #spill error, click in the formula cell (here, D5).
- Now click on the warning triangle sign (shown on the side of the formula cell) and the user will notice the reason for the #spill error. Here, it will be shown as the Spill Range isn’t Blank.
Identify the Cause of the Spill Error
Once the user identifies the cause of the reason, he may follow the section about the cause of the Spill error to clear the error.
Spill Range isn’t Blank
If a user is shown that the #spill error is occurring due to Spill Range isn’t blank, then this is the most basic reason to cause a #spill error in Excel.
The #spill error due to Spill Range isn’t Blank can be cleared by the following two methods:
Delete the Non-empty Cells in the Spill Range
- Click in the formula cell (that is showing #spill error) to show the blue border around the Spill Range.
- Then delete all the non-empty cells from the range or move them to other cells.
Click in the Formula Cell to Highlight the Spill Range and Delete the non-empty Cells in the Spill Range
Use Select Obstructing Cells
If a range covers a lot of cells and it is a problem to find each non-empty cell in it, then
- Click in the formula cell and click on the warning triangle sign.
- Now click on Select Obstructing Cells and then delete the contents of the problematic cells or move the contents to other cells. Keep in mind if multiple cells are blocking the Spill Range, then all the obstructing cells will be selected when Select Obstructing Cells is selected.
Click on Select Obstructing Cells
Keep in mind that there can be some cases, where a cell may contain an invisible character (like a space in a cell) and due to this Excel might mark it as a non-empty cell but the user may find it difficult to find the problematic character, so, look for those cells as well. To clear a cell with an invisible character (use Select Obstructing Cells to find out the problematic cells), a user may select the cell, click on Clear (in Editing section) on the Home tab of Excel, and then click on Clear All.
Spill Range has a Merged Cells
A user may encounter the #spill error in Excel if the Spill Range contains merged cells. Here, unmerging the merged cells in the Spill Range may clear the #spill error.
- Click on the formula cell to highlight the Spill Range.
- Now try to find merged cells and once found, unmerge the cells to clear the #spill error.
#Spill Error due to Spill Range Has Merged Cell - If merged cells cannot be found, click on the formula cell and then click on the warning triangle sign (as discussed earlier).
Unmerge Cells in the Spill Range - Now click on Select Obstructing Cells and then, unmerge the problematic cell and that may clear the #spill Excel error.
Spill Range in Table
The spill Range does not support the table and if the Spill Range is occurring in a table, then it may throw # a spill error. Here, a user has three options, first, use another formula (discussed at the end of the article), and second move the formula outside the table, or convert the Table to a range. To convert the table to a range:
- Except for the first cell that holds the formula, delete all other repetitions of the #spill error in the column or row (if multiple #spill errors are shown).
Delete Repeated #Spill Error in an Excel Table - Now right-click on the formula cell (or any other cell in the table) and hover over Table.
Convert the Excel Table to Range - Then click on Convert to Range and afterward, confirm to convert the table to a range.
Confirm to Convert the Excel Table to Range - Now Excel will convert the table to a range and that may clear the #spill error.
Some users may not be convenient to convert a table to a range, for those users, they may look into the TEXTJOIN or INDEX functions to clear the #spill error in a table.
Spill Range is Unknown
Excel may show #spill error with the cause of Spill Range is Unknown if the formula contains a volatile function. Volatile functions are those functions whose output is recalculated with every change of the sheet. If an array formula is using a volatile function, then with every output into the spill range, the sheet will be recalculated and this will trigger a non-ending cycle of recalculations, thus resulting in Spill Range is unknown. One such example is the formula below:
=SEQUENCE(RANDBETWEEN(1,100))
The following is the list of the volatile functions of Excel:
- CELL() (depends on arguments)
- INDIRECT()
- INFO() (depends on arguments)
- NOW()
- OFFSET()
- RAND()
- RANDBETWEEN()
- TODAY()
In the case any of the volatile functions are used in the formula, then the user may change the formula to clear the #spill error or use implicit intersection (discussed later).
Spill Error Out of Memory
If a user is using a complex and nested formula that is referring to large data, then that may cause a #spill error as Excel runs out of memory before populating the results in the form of a Spill Range. Here, the user may amend the formula to make it less complex and refer to a smaller chunk of data.
Spill Error due to Unrecognized/Fall Back
Computing is a complex process and due to this complexity, there may arise cases where Excel fails to determine the reason for # the spill error. In this case, Excel may show the spill error due to unrecognized/fall back and a user may amend the formula to remove the #spill error.
Spill Range Too Big
A user may see a spill error in Excel due to Spill Range Too Big if the Spill Range is going beyond the worksheet’s columns or rows, for example, if the following formula is entered in Excel (except the first row), it will trigger #spill error due to Spill Range Too Big.
=A:A+1
If the above formula is entered in any row (except the first one), then it will go beyond the last row of the sheet as the sheet holds 1,048,576 rows (Excel rows limit), whereas, the formula will try to paste to 1,048,576 rows but as it is starting from row 2 (or below), so, the sheet will require at least 1,048,577 rows (which is not there) to past the results, so, Spill Range Too Big error.
Another example could be the following formula:
=SEQUENCE(1,17000)
This will also throw a #spill error as Excel has a total of 16,384 columns, so, it is not possible to post the result to 17000 columns, and hence the #spill error due to Spill Range being too big.
To solve #spill error due to Range too big, the user may reference a range (not the whole column or row), copy the formula to other cells after applying it to a single cell, or perform implicit intersection by using @ operator.
Use Ranges to Clear the Spill Error Due to Spill Range Too Big
In place of using whole Excel columns or rows, a user may prefer the required ranges to clear the spill error due to the spill range being too big. For example, a user may encounter a #spill error for the following formula:
=B:B*10%
As the above formula uses a whole column, so, it may trigger the #spill error. Here, using a range like the following formula may clear the #spill error:
=B1:B1000*10%
Copy The Formula to Other Cells After Applying It to A Single Cell
Despite applying the Spill formula to a single cell that is triggering #spill error due to the spill range being too big, a user may copy the formula to other cells after applying it to the single cell may solve the problem.
For example, the following formula will trigger a #spill error on Excel:
=B:B*10%
But to clear the #spill error, the user may enter the following formula in the formula cell:
=B1*10%
And copy (or drag) the formula to all the relevant cells.
Use the Implicit Intersection
With the user implicit intersection operation, a user may stop the spilling of results to the neighboring cells and limit the current result only to the formula cell. It is the same functionality where the formula did not spill the results in the pre-dynamic Excel (Office 2019 or earlier). Another advantage of using implicit intersection is that it can be used in a table as well but with a structured reference. A user should only use this implicit intersection (with @ sign) only when there is no other way possible. To better understand the concept:
- Create a new sheet in Excel and enter the following formula:
=B:B*10%
- This will trigger a #spill error. Now, add @ just before B in the formula, as under:
=@B:B*10%
- Now the current result will be only shown in the formula cell without any #spill error.
Use Implicit Intersection to Stop Spilling of Results to Other Cells
To use the implicit intersection in an Excel table, it will be better to use the formula with a structured reference like the below:
=[@Sales]*10%
The post How to Fix “#Spill Error” in Microsoft Excel? appeared first on Appuals.com.
0 Commentaires