How to FIX SPILL Error in Excel - WHY it's NOT ALWAYS Obvious!
How to FIX SPILL Error in Excel - WHY it's NOT ALWAYS Obvious!
I’ll show you what the Excel Spill Error is and how to fix it. It’s also referred to as #spill or Hash spill error. This Excel error occurs if you reference a range that is either too big for the workbook (like referencing entire Excel columns) or when the spill range isn’t blank. You might also see this error if you use one of Excel’s new functions like the FILTER function, UNIQUE or SORT Excel functions.
Sometimes it’s easy to get rid of it but sometimes it’s not so obvious why the Excel formula error is there. I will show you different examples of why you might be getting the hash spill error and how you can correct your excel functions.
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel #excel #spill
Content
0.25 -> Today, we're going to cover
a common question I get
2.97 -> which is what is the #SPILL! error
6.55 -> and how can I get rid of it?
8.7 -> Well, sometimes it's easy
to find a reason for it
12.17 -> but sometimes it's not so obvious.
15.11 -> Let me show you here.
16.877 -> (upbeat music)
20.18 -> Here, for example, I have a
list of names and salaries.
23.54 -> And if I write this formula,
so I just say equals,
27.51 -> and I highlight the salary
column and I press Enter,
31.89 -> I get the #SPILL! error.
33.72 -> This is an error that
you're going to see only
35.98 -> in the Excel 365 version.
38.82 -> And the reason for it is
that formula's now spill.
42.36 -> So take a look at this.
44.309 -> When I go on the cell
45.33 -> we can see this light
blue border around it.
48.21 -> The formula wants to put
results onto each single cell.
52.37 -> It can't because there's a blockage.
54.67 -> To get rid of it, in this case,
56.24 -> all I have to do is remove this text.
59.77 -> And when I do everything spills correctly.
63.42 -> Okay, so you are also going to see this
65.88 -> if you, for example, go
here and you say equals
70.05 -> and you highlight the whole column
72.35 -> and then you press Enter,
it's not going to work
74.57 -> because you're putting the
entire column starting from here.
78.71 -> It will work if I pull
this all the way up here
82.35 -> and then everything spills.
84.12 -> But it's not good practice
to reference entire columns.
87.98 -> So I'm going to get rid of that.
90.07 -> Now you're also going to see this.
91.55 -> If you use functions like
unique or sort or filter.
96.16 -> So these are the new functions
97.41 -> that were introduced with 365.
99.78 -> So if I get a unique of
this and I have no blockage,
103.01 -> it's no problem, right?
104.44 -> Formula spilled.
105.8 -> Now in case there is a blockage,
107.84 -> so I have some texts
here and I press Enter,
110.39 -> I get that error again.
111.68 -> I need to get rid of it for it to spill.
114.16 -> Some other times
115.24 -> it might not be so
obvious why you have this.
117.41 -> So let's say you have a workbook
119.19 -> and then you go and do the same thing here
121.93 -> and then your press Enter.
123.22 -> And we get the #SPILL! error.
124.85 -> And look at this, there is no blockage.
127.04 -> What's happening?
128.74 -> Well, the reason for this
130.42 -> is that there is something in there,
132.27 -> but you just can't see it.
134.43 -> Either because maybe the font is white.
137.77 -> Like take a look at this.
138.61 -> This is, who's Gary?
140.18 -> The font is white.
141.69 -> If I change it to black, I can see that.
144.758 -> Now, when I take it away,
it's still not working.
149.05 -> I have something else in there.
151.16 -> And it's, who is Peter?
152.72 -> But in this case, it's not the font.
154.56 -> That's not the problem.
156.1 -> It was formatted to disappear.
158.9 -> So if I right-mouse click
and go to Format Cells
164.016 -> it was given this formatting,
166.32 -> which is actually hiding
the content of the cells.
169.77 -> If I take away the formatting,
I can see who's Peter here,
174.41 -> go with, OK,
175.63 -> and then remove the blockage.
177.12 -> Everything spills.
178.7 -> Okay, so sometimes it's obvious.
180.64 -> Sometimes it's not so obvious,
182.77 -> but the reason is that your formula
185.17 -> can't go all the way to the bottom
187.46 -> of where it wants to go,
188.97 -> because there is a blockage.
191.18 -> Okay, so that's a short video
192.69 -> on the #SPILL! error.
194.81 -> I hope you enjoyed this.
196.28 -> If you did do give it a thumbs up.
198.82 -> And if you aren't
subscribed to this channel
201.15 -> and you want to get better in Excel
203.21 -> and other office products,
consider subscribing.
