One of the most amazing features added into Microsoft Office 365 and Excel 2021 is dynamic arrays. So, now if a formula returns multiple values then its results will span over multiple cells automatically. The results will be enclosed in a rectangle which is called spill range. #Spill! is an error which is associated with dynamic arrays. In today’s tutorial we’ll learn about the causes of #spill! error and the solutions to remove the error in Excel.
Let’s explore the errors and their solutions one by one.
Spill range isn’t blank Error
This is the most common error associated with dynamic arrays. This happens when the range, where the results are supposed to be populated after the calculations are performed, already has some data in any of the cells.
To resolve this issue follow the step above.
Step 1 – Remove the data present in Spill Range
- To resolve this error, just clear the contents of all cells in the Spill range. Go to the Editing group on the Home tab and click on the Clear drop down button. A drop down menu will appear. Click on Clear Contents. This will remove all contents from the spill range and the error will be removed.
Spill range has merged cells Error
The reason for this type of error to occur is the presence of merged cells within the spill range.
To resolve this follow the step above.
Step 1 – Locate the merged cells and unmerge all cells.
- To resolve this error, locate and select the merged cells.
- Go to the Alignment group on the Home tab and click on the Merge and Centre drop down menu. From there select Unmerge. The error will be resolved and the spill range will be populated.
Spill range is in the table
If you are using a formula which uses dynamic arrays as results inside a table then you won’t get your results right. You will see this error.
To resolve this follow this step.
Step 1 – Convert Table to range
- This error can be easily resolved by converting the table to range. To do this select any cell inside the table.
- A new tab Table Design will appear in the list of main tabs on the top row and you will see new groups. Click on it and locate the Tools group. The click on the Convert to Range. The table will be converted back to range and the error will be removed as well.
Spill range is too big
The reason for this is the inability of Excel to create a dynamic array because the array results extend beyond the edges of the current spreadsheet. This happens when you try to perform a calculation on the whole column instead of a limited range.
The solution to this is as follows.
Step 1 – Change the formula range as per requirement
- To resolve this change the range used in the formula from the whole column to only the required cells.
Spill range is unknown
This error happens when you use volatile functions inside another function which produces a dynamic array as a result. In such cases, Excel can’t determine the size of the output array therefore, you get this error. The only solution to this is to change the formula and not to use any volatile functions inside array producing functions.