This article will assist you to identify the root of #SPILL errors and solutions for fixing these errors within Excel 365.
#SPILL! is a brand new type of Excel error that is most often encountered when a formula produces multiple calculations is trying to display the outputs of its calculations in the spill range, but the range already contains other information.
The data that is blocked could include anything, such as text value or merged cells, simple line, and even in the event that there isn’t enough space to display the results. The solution is easy or remove the range of any blocking data or choose the empty cells which doesn’t contain any kind of information within it.
Spill error is usually encountered when you calculate dynamic array formulas since it is the formula that generates the results to several cells, or in the form of an array. Let’s take a closer look and discover what causes the error Excel and the best way to fix it.
Table of Contents
What is the cause of a spill error?
Since the introduction of Dynamic arrays in the year 2018, Excel formulas can handle multiple values simultaneously and provide results to multiple cells. Dynamic arrays can be resizable which allow formulas to return different results in a set of worksheet cells using a formula that is that is entered into a single cell.
If a dynamic array formula produces multiple results, the results will automatically spill over into adjacent cells. This is known as Spill in Excel. The range of cells in which the data spills is known as the ‘Spill range’. The spill range can expand or shrink automatically, based on the values of the source.
If the formula attempts to fill in a spill range that has many results, but is hindered by something in the range the #SPILL error is triggered.
Excel currently has nine functions that make use of Dynamic Array technology to address issues, which include:
- SEQUENCE
- FILTER
- TRANSPOSE
- SORT
- SORTBY
- RANDARRAY
- UNIQUE
- XLOOKUP
- XMATCH
Dynamic array formulas are available only in Excel 365 and is currently not available in any offline Excel software (i.e. Microsoft Excel 2016, 2019).
Spill errors aren’t just caused by data obstruction There are a variety of causes that could cause a the #Spill error. Let’s look at the various scenarios that could trigger the #SPILL! error, and the best way to correct the issue.
Spill Range isn’t Blank
One of the main reasons for spill errors is that the spill area is not empty. For example, if you want to show 10 results, however, if there is any information in one of the cells within this spill zone, then the formula will return an #SPILL! error.
Example 1:
In the following example we’ve entered the TRANSPOSE function into cell C2 to transform cell’s vertical dimension (B2:B5) to horizontal one (C2:F2). Instead of converting the column into one row, Excel displays the “#SPILL! error.
When you click that formula cells, you’ll be able to see a dashed blue border that indicates the area of spill (C2:F2) which is required to show the results as illustrated below. Additionally, you will see an orange warning symbol with an exclamation point across it.
To find out the cause of the error, click on the warning icon beside the error, and you will look at the message on the first line, highlighted in gray. It states “Spill range doesn’t have to be blank” in this case.
The issue lies in the fact that both cells within the spill range E2 and D2 contain texts (not empty) which is the reason for the error.
Solution:
The solution is easy to either delete all information (either remove or move) within the spill area or transfer the formula to a different area where there is no obstruction.
When you remove and move the obstruction, Excel will fill the cells with the result from the calculation. When we remove the text in E2 and D2 the formula converts the column into rows as it was intended.
Example 2:
In the following example even though the spill area seems empty, but the equation still displays the Spill! error. The reason for this is that the spill isn’t empty. It has an invisibility space character within one cell.
It is difficult to find characters in space or any other characters that are invisible hidden in what appear to be empty cells. To locate cells that contain unneeded data, simply click the Error floating icon (warning symbol) and then select the option ‘Select Obstructing Cells from the menu, and it will direct your to the cells with the data that is blocking it.
You can observe, in the screenshot below Cell E2 contains two spaces. If you delete the data, you’ll receive the correct output.
Sometimes, the invisibility character may be a text that is formatted using the same color of the font as the color of the cell’s fill or a cell value that is custom that is formatted using the number code ;;;. If you customize the value of a cell with ;;;, it, it will obscure any content in the cell regardless of the font color or color of the cell.
Spill Range Contains Merged Cells
Sometimes, the #SPILL! error happens in the event that the range of spills has the combined cells. Dynamic array formula isn’t working when there are merged cells. To correct this issue the only thing you need to do is remove the cells within the spill range , or transfer the formula to a different range with no merging cells.
In the following example although the spill area is not empty (C2:CC8) The formula will return an error code of Spill error. This is because the cells C4 and C5 have been joined.
To ensure that the merged cells are not the reason for the error, click on the warning symbol and check the reason”Spill range has been merged’.
Solution:
To remove the cells, click on the cells that have been merged, then, on the ‘Home’ tab, select the “Merge & Center button, and then select Unmerge Cells.
If you are having a difficult finding the merged cells within your large spreadsheet, select the ‘Select Obstructing cells option in the warning signs menu to navigate to the cells that have been merged.
Spill Range in Table
Spilled array formulas aren’t accepted within Excel tables. Formulas for dynamic arrays should be limited to be entered in one individual cell. If you type an array formula that is spilled in the table, or when the spill area is inserted into the table, you will receive the Spill error. If this occurs, attempt to convert the table into normal range, or moving the formula out of the table.
For instance, if we type in the spilled range formula into the Excel table, we will receive a spill error in every cell in this table and not only that formula cells. This is due to the fact that Excel automatically copies any formula that is entered into the table to each cell within the column of the table.
Additionally, you’ll encounter an error relating to spills when a formula attempts to spill the results of the table. In the screenshot below the spill area is within the table that is already in use and we receive an error for spill.
To determine the reason for this error, click on the warning symbol and look up the error’s cause”Spill range in table’
Solution:
To correct the issue it is necessary to convert the Excel table back into the range. To do this you need to right-click any place within the table, select “Table” then choose the Convert to Range option. Alternately, you can click left any table area and then click the tab ‘Table Design’ and choose the ‘Convert to Range’ option.
Spill Range is Unknown
If Excel could not determine the dimensions of the array spilled and it is unable to determine the size, it triggers an error called the Spill error. In some cases, this formula allows an array dynamically to change size during each calculation pass. When the dimension of the array is constantly changing throughout the calculation process and does not balance out it can result in the #SPILL! Error.
This kind of error usually occurs by volatile functions like RAND, RANDARRAY, RANDBETWEEN Offset, INDIRECT, and RAND functions.
For instance, if we apply the formula below within cell B3, we will get the Spill error
=SEQUENCE(RANDBETWEEN(1, 500))In this example in the example, the RANDBETWEEN function produces an undetermined number between 1 to 500 The output of the function changes continuously. The SEQUENCE function does not know the number of values it can create in an array of spills. This is why this error message.
You can also determine the root of the error by clicking on the warning sign that says ‘The range of the spill is not known’.
Solution:
To correct the error in this formula, the only option is to choose another formula to calculate.
Spill Range is Too Big
Sometimes, you’ll need to run a formula that produces an unintentionally spilled area that is too large for the worksheet to manage and may extend beyond the boundaries that the sheet. If this happens, you could receive a #SPILL! error. To resolve this issue it is possible to reference an individual range or cell instead of columns, or use the character ‘@’ to allow an implicit intersection
In the following example we’re trying to figure out 20 percent of the sales figures in column A and return the results to column B. However, instead we receive an error in the form of a spill.
In B3, the formula calculates 20 percent of the value from A3, and then 20 percent of the value from A4, and the process continues. It generates more than one million numbers ( 1,048,576) and then spills all of them into the column B beginning in B3 cell, but it will end at the top in the workbook. It isn’t sufficient space to display all the results, and as a result we will get an error code #SPILL.
You can clearly see, the reason for this error is the “Spill range is too large’.
Solutions:
To resolve this issue Try replacing the entire column using an appropriate range or single-cell reference, or adding an @ operator for an implicit intersection.
Solution 1 Try using ranges as a reference instead of entire columns. In this case, we replace the entire range A:A by substituting A3:A11 as the formula, then the formula would then fill the range with results.
Fix 2. Change the whole column by replacing the reference to the cell on that same row (A3) Then, duplicate the formula down the column by using an fill tool.
3. It is also possible to add the @ operator prior to the reference to perform an implicit intersection. The output will be displayed within the formula cell, but only.
Copy the formula from cell B3 and paste it into the remainder part of the spectrum.
NOTE: When you are editing a spilled formula you are only able to modify the cell within the spill range or area. The formula can be seen in the other cells within the spill area, however they will be grayed out and will not be updated.
Out of memory
If you use an array spilled formula that results in Excel to exhaust its memory, it could cause the error #SPILL to occur. In that case you can try using an array that is smaller or a range.
Unrecognized / Fallback
It is also possible to get an Spill error, even if Excel doesn’t detect or can’t determine the reason for the error. In these cases make sure you double-check the formula and ensure that all parameters of the formula are in order.
Now, you are aware of all the reasons and solutions to #SPILL! mistakes in Excel all 365 days of the year.