Today, we're going to cover
a common question I get which is what is the #SPILL! error and how can I get rid of it? Well, sometimes it's easy
to find a reason for it but sometimes it's not so obvious. Let me show you here. (upbeat music) Here, for example, I have a
list of names and salaries. And if I write this formula,
so I just say equals, and I highlight the salary
column and I press Enter, I get the #SPILL! error. This is an error that
you're going to see only in the Excel 365 version. And the reason for it is
that formula's now spill. So take a look at this. When I go on the cell we can see this light
blue border around it. The formula wants to put
results onto each single cell. It can't because there's a blockage. To get rid of it, in this case, all I have to do is remove this text. And when I do everything spills correctly. Okay, so you are also going to see this if you, for example, go
here and you say equals and you highlight the whole column and then you press Enter,
it's not going to work because you're putting the
entire column starting from here. It will work if I pull
this all the way up here and then everything spills. But it's not good practice
to reference entire columns. So I'm going to get rid of that. Now you're also going to see this. If you use functions like
unique or sort or filter. So these are the new functions that were introduced with 365. So if I get a unique of
this and I have no blockage, it's no problem, right? Formula spilled. Now in case there is a blockage, so I have some texts
here and I press Enter, I get that error again. I need to get rid of it for it to spill. Some other times it might not be so
obvious why you have this. So let's say you have a workbook and then you go and do the same thing here and then your press Enter. And we get the #SPILL! error. And look at this, there is no blockage. What's happening? Well, the reason for this is that there is something in there, but you just can't see it. Either because maybe the font is white. Like take a look at this. This is, who's Gary? The font is white. If I change it to black, I can see that. Now, when I take it away,
it's still not working. I have something else in there. And it's, who is Peter? But in this case, it's not the font. That's not the problem. It was formatted to disappear. So if I right-mouse click
and go to Format Cells it was given this formatting, which is actually hiding
the content of the cells. If I take away the formatting,
I can see who's Peter here, go with, OK, and then remove the blockage. Everything spills. Okay, so sometimes it's obvious. Sometimes it's not so obvious, but the reason is that your formula can't go all the way to the bottom of where it wants to go, because there is a blockage. Okay, so that's a short video on the #SPILL! error. I hope you enjoyed this. If you did do give it a thumbs up. And if you aren't
subscribed to this channel and you want to get better in Excel and other office products,
consider subscribing. (upbeat music)
