Microsoft Excel 2013 Tutorial - 19 - Referencing Cells Properly Video Lecture | Microsoft Excel 2013: Be the Excel Master - Software Development

alright guys in the last video we
learned about one of the coolest things
that we could do in Excel up to this
point basically we learned about the
ability to create a dynamic formula
something like this for the total value
we could take the quantity of whatever
item and multiply it by the price and
hit enter and then when we grab the fill
handle and drag it down check it out it
automatically was smart enough to detect
exactly what we were trying to do and it
went through row by row and it use that
formula to give us the perfect answer
alright so that is pretty sweet so let
me go ahead and use this for something
else I know that the text of I don't
know like the tax rate is 7% so 0.07 so
now what I'm going to do is find the
text of each item so make a new column
tax and okay so what did I do here I'm
going to make equal sign for formula and
I know the tax of each item is the price
of that item and we multiply it by the
text 0.07 everything was good to go
Paul rice wheat so the text if you
bought some bread gloves you would have
to pay a dollar in 61 cents all right
man this is nice so let me just go ahead
and fill this in and what the heck is
this crap I thought that uh wait a
minute okay it's working right here but
why the heck is it not working there
okay I see what's going on now see what
happened in this case is in this example
right here Excel detected that this x
this was equal to this so on the next
row whenever we filled it in
it assumed this x this was equal to this
in other words it shifted down one row
each time so in this case what it's
doing is it's taking this multiply or
excuse me this multiplied by this and
gave us our answer however when we shift
down not only does it shift down the
price
but also shifts down this cell as well
so instead of just using this 0.07 like
before it actually is going down one row
and looking for some number right here
now of course we don't have any number
right there so that's why we're getting
this little error right here and you can
see if you actually click on it it's
trying to reference be 18 which is empty
now that's obviously not what we want so
let's go ahead and hit ctrl Z ctrl Z
ctrl Z cancel out of there and I'll show
you guys how to set up a different type
of cell in other words how to reference
cells in a different kind of way so
basically what we need to tell excel in
everyday human language is whenever we
use our formula this piece of data is
going to be changing the price on each
row but the cell we're referencing in
other words the text is going to stay
the same so you don't need to shift down
right here so what you can do is start
typing in the formula just like you did
before so the text is equal to the price
of each row multiplied by the text okay
simple enough
however hold on let me shift around in
my chair right here however instead of
just referencing this row right here
we're going to add some cool symbols to
it the first thing we're going to do is
we're going to add a dollar sign before
the B and a dollar sign before the
number right there before the row number
now hit enter and okay we got the same
result as before
however hover your cursor over to the
fill handle and drag down and check it
out it now completes our equation and it
did it perfectly so what the heck were
those dollar symbol symbols and why did
it cause our formula to work well those
dollar symbols right here are basically
telling cell not to change the cell or
the reference in that formula the dollar
symbols mean absolute value in other
words what we're basically telling it in
this formula is okay
to be working with two parts the first
part is going to be dynamic it's going
to change the part with the dollar cells
or excuse me the dollar signs it's going
to stay the same so whenever we fill it
in don't move this cell down keep the
value or the reference to be 17 for
every single equation alright pretty
sweet now the dollar sign actually means
don't move this part of the equation so
we actually can have something like this
dollar sign B and then nothing before
the 17 and what this would mean is okay
B is going to stay the same so the
column is going to be absolute reference
but the row is going to be dynamic and
actually you don't see that that often
but I just want to point it out arm so
you guys know what's going on so again
that is the difference between how you
reference cells either absolute or
dynamic and another thing that's really
cool go ahead and delete all those is
this if you ever want to arm reference a
cell really easy and you don't feel like
typing in all those dollar signs a cool
little shortcut that you can do is you
can type let's create the same equation
we just created so we're going to use c3
which is going to be dynamic and we're
going to multiply it by the tax value of
7% now after we click a cell that we
want a reference whenever we want to
reference it in a different kind of way
in other words the absolute value if you
hit f4 on your keyboard it cycles
through the different types of ways you
can reference it so of course the
default one means totally dynamic as
well it's going to try to shift that one
down if you hit it one more time then
it's completely absolute and of course
the other ones are mixed so this means
that the rows can be absolute but the
columns going to be dynamic and this one
means the columns absolute in the rows
dynamic but of course that's the one
we're looking for right there and now we
can just drag it like before boom
roasted pretty stinking sweet so I mean
I think that's all
I have to cover for this tutorial and
actually let me show you guys one other
thing since I have some time this is
actually pretty cool now we know that we
can reference cells in a bunch of ways
we can either use their nickname we can
use the address such as c3 we can use
the address such as b-17 and tell them
we want to treat it as an absolute
reference in our formulas but did you
know that you can actually reference
cells in other worksheets so let's go
ahead and move to a new worksheet and of
course we now have sheet 2 and sheet 1
so let's say that we wanted to create an
equation in sheet 2 and we wanted to
reference one of the cells in I don't
know I'll just reference bread gloves
because it's the easiest so the address
of bread gloves is a3 so remember that
so if we go to sheet 2 and it already
started trying to do it for me
because I don't know it's a little bit
smarter than we are right now but we'll
fix that we're catching up to it so set
it equal to and it's actually a two part
thing we need to remember sheet 1 the
first part is the sheet name and now
separate it with an explanation mark or
explanation point if you want to call it
and after that put the cell address so
a3 so this means is I'm going to
reference a cell a3 and it's in sheet 1
so now hit enter and of course it gives
you the value of bread gloves so that's
when if you ever want a reference a cell
another sheet just like the name of that
sheet this simple effort and the address
of that cell so pretty cool and of
course if this was a number which is not
you can like take the price of it plus
10 or whatever but of course since we're
trying to add the word bread gloves and
the value 10 that's where we get the
error so for now hope you guys learn
some cool things in other words how to
reference cells if you don't quite
understand and I know it's kind of a
hard concept to understand at first then
ask me when I form
I'll write a little explanation of what
dynamic mixed and absolute means and it
may actually be easier to see if you're
looking at it like on the table or
something but for now as always thank
guys for watching don't forget subscribe
and I'll see you guys next video