Download Excel Workbook
#SPILL Excel mainly occurs when the range in which the result of formula will be displayed is being obstructed by some other data. It can be blocked by when the cells in the spilled range contain some text, space, or are merged.
Once you know the reason why Excel is producing SPILL errors, you can simply fix the problem and get rid of this error.
What does SPILL mean?
Dynamic arrays come with a package full of benefits but it also includes the user to a new error – SPILL! These formulas produce multiple results and display them to the neighboring cells.
SPILL error is caused when a formula with multiple results cannot display its output array as those cells already contain some data.
A simple solution to this problem is to clear contents of the cells in the spill range.
Follow this detailed tutorial on #SPILL Excel and download this Excel workbook to practice along and understand better:
How to fix #SPILL Excel?
The different scenarios when you can encounter this error are:
- Spill range isn’t blank
- Spill range contains merged cell
- Spill range is too big
- Spill range in a table
Let’s look into more detail and understand the causes and solutions to the #SPILL Excel!
Example 1: Spill range isn’t blank
The spill range where the result will be displayed is non-empty.
In the example, we have used the UNIQUE function to extract a unique customer list in column B. Instead of displaying the result, Excel returns #SPILL! error.
To understand the reason behind this error, click on the small yellow! warning sign next to the error. Upon clicking this sign, Excel tells you the reason – Spill range isn’t blank.
Here, B12 already contains text!
Solution:
Simply, clear any contents from the output range and you are good to go!
There can be times when you get the SPILL error but you are unable to spot the cell that contains unwanted data.
Click on Select Obstructing Cells and it will take you to the cell that is creating the problem.
This is useful when you have hidden spaces and you are unable to find them.
Example 2: Spill range contains merged cells
SPILL error can also occur when the result range contains the merged cells.
In this example, you can see the spill range i.e. B7:B15 is empty but still, Excel returns #SPILL error.
To be sure of the reason, let’s click on the yellow warning sign and see what it says – Spill range has merged cell.
Now, click on Select Obstructing Cells and it will take you there.
Go to Home > Merge & Center to unmerge the cells.
The result is now visible and the error disappears!
Example 3: Spill range is too big
This error occurs when the output range is too big and the result goes beyond Excel’s boundary. There isn’t enough space to display the result.
In this example, we are trying to calculate the discount given to the customers based on the sales amount. Column C is used to get 5% of the sales amount.
As you can see, Excel is again and producing a SPILL error and this time it is because the range is too big.
Here, Excel is trying to multiply each cell in Column B by 5% and produce a million results. It will spill the result in Column C starting from cell C7 but it will reach the end of Excel.
Hence, the error!
To solve this, simply replace the column with the relevant range and copy the formula to the output range.
Example 4: Spill range in a table
Excel tables do not support spilled array formulas. If you try to insert an array formula in an Excel table, it will return a #SPILL error.
In this example, we are trying to sort the amount mentioned in Column A in ascending order using the SORT function. It will return a dynamic array as a result in column B.
Unfortunately, we are getting a #SPILL Excel error. This is because range A7:B15 is in fact saved as a table in Excel and dynamic functions do not work within Excel tables.
We can easily convert it into a range for our SORT function to work properly.
To do so: Right Click on any cell in the Table range > Select Table > Select Convert to Range.
This will convert the table to range and now all array functions will work perfectly as shown below!
Conclusion
Next time when you face #SPILL error in Excel, the best way to deal with it would be to examine the cause of the problem by clicking on the exclamation mark icon.
After that, you can easily troubleshoot the problem and fix the error effortlessly!
Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
