Today, we're going to talk about
the hash reference in Excel. A common question I get
is what is that hash sign I keep using in my videos
when I'm referencing cells? Well, if you have Microsoft 365, you already have that hash sign and you should be using it because it's going to make
your life a lot simpler. Let me show you how. Here I have some sample data which is not formatted as an Excel table. First of all, let me show
you where you can't use the hash sign. So you can't just go typing in equals, reference A3, put in hash
and expect your results to spill and pick up the
range from A3 to D18, that's not going to happen. And the reason it doesn't happen is that this is not a spilled range. You can only use the hash
sign when you reference a range that's spilling. Now, how do you recognize spilled range? Well, let me show you. First of all, let's
create a spilled range. How do you do that? Well split ranges are generally created by using formulas that spill. So for example, if you
use the sort function or the unique function, these
are functions that spill. If you're curious to find out
more about these functions, I have a separate video about this and I'm going to add the link to it to the description of this video. I also have a complete
course that covers all the new functions in Excel 365. I'll add the link to that as well. The only thing we need for the
unique function is our range. So let's say I want to
get the unique combination for division and region, I'm
going to reference this range, close bracket press Enter and
this now is a spilled range. I wrote the formula once I press Enter and the result is spilled
to the next cells. Now, here's how you can
recognize spilled range. Number one, it has this
automatic blue border so every time I click away, it's gone. Every time I click inside, it's there. And number two is that the formula is only living in the first cell. So that's the top left-hand corner. Take a look at the formula bar, the formula is dark here. When I go to the next
cells, it's grayed out. This is how you can
recognize the spilled range. Check this out. If I try to remove any part of this range so I'm just going to press
Backspace here and press Enter, it doesn't let me do it. If I press Delete, it
doesn't let me do it. The formula can only be removed here, here if I press Backspace and press Enter, my formula is gone. I'm going to press
controls that to go back. Now instead of just removing it, if you type in something over this so let's say I type in subscribe which is a good time to let you know that if you're not
subscribed to this channel, consider subscribing, especially if you're finding yourself learning new topics here. If I type this in and I press Enter, I get a spill error here, why? Because my formula wants to
spill and there is a blockage then if I remove this blockage,
I get back my results. Now that we know what a spilled range is, how can we use the hash sign? Well, if you're referencing a range that has this property that is spilling that's when you can use the hash sign. So here, if I go in and type in =G3 and now I put in hash and I press Enter, my range automatically spills
and it picks up everything. Now the advantage of using the hash sign is that the moment this
original range here expands, this range is going to
expand automatically. So for example, if I go
back to my sample data here and create a new combination
so productivity Europe, let's change this to
productivity Australia and I press Enter I get that combination
pop up here automatically. And this part, my hash reference
automatically expanded as well. Okay, so I'm going to press
Control + Z to go back. Now, if you take a closer
look at our first formula, notice I'm referencing A3 till B18. So I'm using direct cell referencing. This is not an Excel table. This means that every time
I add something new to this if I add in health and I put in Europe obviously this range
is not going to expand. But if I go in and manually
expand it and then press Enter the second range will automatically expand because I'm using the hash sign here. Now best practice is to use Excel tables because you're not going
to have this problem, your range will automatically expand if the source is a table and this is not because I'm
using a special function like unique, this works
with any Excel function that's why it's great to
use tables as your source. So if we transform this
into an Excel table, we don't have to worry
about this part anymore because our range will
automatically expand. Let me just press Control + Z to go back. Now, before we do that and I show you how spilled ranges behave with tables, let me show you one other thing. Remember when I typed in this formula, I actually went in and I
did G3 and then I put hash. You don't necessarily
have to type in the hash. Here, if you just referenced
this spilled range it automatically puts it for you. So take a look at this and
just going to remove this. Now, when I reference this,
take a look at the formula bar, it's still G3 to H8. Now, if I expand this
and go one more row down, take a look at what happens, it puts in the high sign for you. Okay, so referencing the
whole range gives you that hash sign as well. Let's pause and take a look
at a practical application of the hash sign. Now you generally use that
inside other formulas. So for example here, let's say
I want to get a unique list for either region, app or division and I want to make that
selection from disruptor. So if I switched to app
here, I get a unique list of the apps and I get an
automatic numbering on the sites. If I switched to division, I just have two different divisions. Where does hash come into play? Inside the sequence and
the COUNTA functions. If you're curious to see
what formula I used here, it's a combination of
unique index and x match which is the new version
of the match function. Now let's get back to
our original example. Let's go ahead and transform this into an official Excel
table and see what happens. So I'm going to press Control + T, table has headers and let's go with Ok. So now this is a table, the moment I start to
add something to this, take a look at my range here. I'll put in health and Europe, it automatically expands because
my source is now a table. And how about using hash
references inside a table? Well, tables have an inbuilt
spill behavior already. So take a look at this. If for example, I was just
adding a new column to this table and I want to take this number
here and deduct one from it. So I'm going to do minus one. The moment I press
Enter, my results spill, everything is relative referencing and it's using structured
relative referencing inside the table. Now if instead I go ahead
and use the hash sign or write a formula that spills. So for example, let's
go and do a unique here and I'm going to reference
this column here. When I close the bracket
and I press Enter, it's going to give me an error, why? Because my formula is spilling. When it's in this cell, it's trying to write game,
productivity and health. And then when it's in this cell is trying to write the same thing game,
productivity and health, it's blocking one another. So you get the high spill error. This means that you cannot
use any spilled ranges inside a table. And the reason for that is that you end up with something that's like
a table inside a table which is something that
Excel can't display in this two dimensional environment, we just have rows and columns. It would be nice to maybe
get a little table icon here and we could click it
and go to a second table that just shows us these three. Kind of like empower query
where you can have tables inside the cells. You can click on them
and get to the details. Unfortunately, that
behavior can't be displayed on the grid here. I guess, to keep that in mind
when you work with tables. Now here I have a bonus tip for you. What if you want to
reference the spilled range but you don't want the whole range back. You just want a specific
part of that range. So let's say here, I just
want to get column I back of that spilled range. While there are different
ways of doing this, one way you can do this is
to use the index function, the array is your spilled range so I'm going to go with I3#, I want to include all rows so I'm just going to skip this argument and for the column number, I
can give back the index number of the column I want returned. So if I just want the first column, I'm going to put one, close
the bracket press Enter and I just get the first
column back if I just want it. The second one back
changes to two press Enter and I get the second one. Okay, so I hope it's clear now
what that hash reference is and how we can make your
reports more dynamic without you having to worry about updating your source ranges whenever they change. I hope you found this video useful so give it a thumbs up and
subscribe to this channel to stay up to date with
office productivity topics. Just click on the subscribe
button below the video. Thank you for your attention and I'll see you in the next video. (mellow music)
