Last Updated on
If you’ve ever seen #SPILL! Pop up in your Microsoft Excel spreadsheet and if you have no idea why then you’ve come to the right place.
Whenever you see SPILL, this is about certain behavior in which formula will return multiple values, and will automatically spill these results into multiple cells. This process is part of the Dynamic Array functionality.
You will get this error message using an array of formulas including Sort, Filter, and Unique. Do note that this error message will only appear on the Office 365 version.
Here’s an example:
You have a list of movie names in column one and a list of the box office revenue that movie made in column two. In the third column next to our 5th entry is a comment that states “Only Released In North America”.
If we decide to take the third column and enter = above the comment in the first row with data while highlighting the box office revenue column and selecting enter – the term #SPILL! will pop up as opposed to the formula we were looking for.
This happens because there is an entry in the column that you selected and the software cannot enter the details.
How To Overcome This Error Message
Excel doesn’t want to just give you an error message and not give you some kind of indication as to what the problem might be, and it will usually try to give you a hint as to what the bottleneck could be.
With spill, Excel will do just that. Keeping with our example from above, you will notice that the cells underneath the #SPILL! cells are now highlighted with a soft blue box. This is an indication that something is going on within one of these cells.
To allow Excel to complete the formula you have asked it to run, simply delete or move whatever obstacle has been placed in the way and Excel will automatically complete the formula.
But what happens when you see the error message but cannot see any obvious reason why it cannot complete the formula?
Our advice is to check carefully that something isn’t hidden in one of the cells, and one of the most common reasons this occurs is because there is either a very small font used or the text has been colored in white so that it can’t be seen.
Another Reason Spill Will Occur
The incident highlighted above is probably the most common occurrence of why a spill occurs in Excel. But what happens if the above example doesn’t apply to your situation.
Here’s another reason why a spill might happen.
Let’s stick with our data points from above, but we’re now going to highlight all of the results of our items in the first column which is our movie titles.
In the third column in the 12th cell down we select = and then select the entire first column and the same error message pops up.
The reason that you do not get the result you want is that there is too much information going into one cell that sits below the data point
You can rectify this by dragging the cell up towards the top of Excel, and in our example to cell C1. As the data point is no longer too low to show everything it will show the results of our movies in full clarity.
Final Thoughts
These are the most popular reasons why a spill occurs. We hoped this helped to clear up a common but often misunderstood error message.
