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

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

SPONSORED: Can you get Microsoft office for free? We tell you all you need to know

Share this article

PC guide
Summary Kevin became editor of PC Guide in April 2022, having previously worked in national new publishers, as a freelance journalist and having written for technology publications for more than 15 years. He graduated from the University of Wales, Aberystwyth – now Aberystwyth University – and holds a bachelor’s degree in Creative Writing and English Literature. He has also been awarded a commendation for poetry by the Royal Literary Fund and took part in, and completed, the 2012 London Marathon. Experience Kevin started his career after university, having worked for the Kent Messenger Group and then Ferrari Press Agency, before moving to Dennis Publishing to work as a Staff Writer, Reviews Editor, and then Deputy Editor on the now defunct Micro Mart magazine. In his time at Dennis, he additionally provided articles for Custom PC, PC Pro, Computer Shopper, IT Pro, and Bit-tech. After Dennis, Kevin worked as a freelance journalist for titles including Which? Computing, and moved to the Telegraph Media Group working with the Technology desk. He then joined Wired UK as a commercial editorial assistant, also contributing to Hardware Heaven and Kitguru in a freelance capacity. Kevin left technology journalism in 2017 to join News UK as an e-commerce editor, overseeing the creation of Sun Selects, and content for Times Tickets. After leaving News UK in 2021 he took a break from journalism, returning to join PC Guide in 2022. Education Kevin holds a bachelor’s degree in Creative Writing and English Literature from the University of Aberystwyth.

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

Leave a Comment