What Does Spill Mean In Excel?

Have you seen the spill error message and have no idea how to fix it?

what does spill mean in excel

Last Updated on

NOW READ: Holiday Deals now live on Amazon & Best Buy. Big brands such as Lenovo and Samsung are also starting to offer Holiday discounts early, with many more to follow.

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.

You might like this

Discover different ways to convert PDF to Word on mac and other devices.
SPONSORED: Can you get Microsoft office for free? We tell you all you need to know

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Managing Editor
at
PC guide
Kevin has been a technophile since he first owned a Commodore 64. Then an Amiga….progressing to Gameboy, self-built systems, consoles, and a 15-year career in and around computing and technology. Kevin is an all-around tech and gadget enthusiast. He was previously found at such places as Micro Mart, Custom PC, Bit-tech, and PC PRO, then Which? Computing, Den of Geek, and Daily Telegraph. Also WIRED, Hardware Heaven, and KitGuru.

Independent, transparent, rigorous and authentic, our reviews are the most thorough and honest in PC gaming. Learn about our review process.

Leave a Comment