Cause:If the spill range is inside an Excel Table, it can lead to the #SPILL! error. Example:You have an Excel table (A1:B6), and you want to sort the data in column A and spill the result in column B, you'll encounter the error. ...
If you get the error message “Spill range in table” with the #SPILL! error, the reason behind it is that the Excel tables do not support dynamic or spilled array formulas. Now, if you want to fix the error in this case, you need to convert the table into a normal range. Or, yo...
The other solution is to relocate the target cell of your formula. Since the spill range ahead of D1 has a non-blank cell, there is no issue in this scenario to move the formula a few cells down. Therefore, we have shifted our target cell from D1 to D5 which allows the result to...
whereas, cellD5only holds theunique formulai.e., UNIQUE(B5:B10) but the formula’s result is shown from cellD5 to D8. Here, D5 to D8 is theSpill Range. If a user selects a single cell in the range, then ablue borderwill be shownaroundthewhole spill range. ...
Spill range in a table Let’s look into more detail andunderstand the causes and solutionsto the #SPILL Excel! Example 1: Spill range isn’t blank The spill range where theresult will be displayed is non-empty. In the example, we haveused the UNIQUE functionto extract a unique customer ...
Spill range in table Reason: Dynamic arrays are not supported in Excel tables. Solution: Convert the table to a normal range or place the formula outside the table to allow it to spill. It's not quite clear why dynamic array formulas do not work from withinExcel tables(maybe because of ...
For the new item to be included in the formula results, you need to change the referred range to A2:A11. If you want such changes to be reflected on the fly, then put your source data into anExcel tableand usestructured referencesin your formulas. Unlike ranges, Excel tables expand autom...
The causes for the #SPILL error in Excel include the following:The #SPILL range is not blank The #SPILL range has a merged cell The #SPILL range is too extensive The #SPILL is in a table in Excel The #SPILL range is unknown (often due to a volatile function)...
Once you have converted the Excel table into a normal range: Reapply the UNIQUE function, and it’s all sorted now. Examples of formulas that return the #spill error Most of the time, you’d see the #SPILL error for array functions. These functions result in an array of cells. ...
Spill Range Error in an Excel Table If you are using Excel tables, in that case, there’s a chance that you will get the #SPILL! error. The reason for this is thattables don’t support dynamic formulas yet. In the above example, we have Table1 where we are trying to get the rando...