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:
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: 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
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 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.
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: 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: The following is the list of the volatile functions of Excel: 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. 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: 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: 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:
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: But to clear the #spill error, the user may enter the following formula in the formula cell: 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: To use the implicit intersection in an Excel table, it will be better to use the formula with a structured reference like the below:
Fix: Microsoft Excel 2016 Opens Blank on Windows 10Fix: Microsoft Excel is Trying to Recover Your InformationHow to Fix a Damaged or Incomplete Installation of Microsoft Excel?Fix: Microsoft Excel is Waiting for Another Application to Complete an OLE…
