hi there welcome to teachers Tech my name is Jamie and it's great to have you here
today I want to talk about absolute cell referencing in Microsoft Excel I actually
want to talk about not using cell referencing in Microsoft Excel now don't get me wrong it's
extremely important to know and understand what this means but I want to show you a different
way that you can use naming of a cell to get very similar results and actually I
find it a little bit more efficient that's what I want to cover today on teachers Tech
if you're unfamiliar if you're if you're unfamiliar with absolute cell referencing
here is a lesson so I'm going to go ahead and I'm just going to do a little multiplication here this
is our sales total I wanted to multiply by this 12 percent okay so I'm going to start with this
and I'm just going to go multiply by this amount and I get 120 000 that's correct now the problem
is going to happen with this I don't have any absolute cell referencing on if I copy this
down these numbers below are incorrect because as it went down to do the formula notice on the next
one it's D3 times d8 it took the 20 000 and times it by the 800 000 to get this result I wanted it
to make sure it stayed at the 12 percent so if I go ahead and delete these here I can make a little
correction making with an absolute cell reference right here if I go and just add the dollar signs
I'm going to do this by function F4 so the dollar signs in front of the D in front of the two
we get this it doesn't look any different but now if I copy this down this is all correct it always
stays at D2 so if no matter which one I click on it always will stay that makes it absolute now
a different way and the way I like to do this is I'm just going to go ahead and delete this
we can actually name a cell so to do this now this 12 percent I want to call this performance bonus
so I'm going to go up to the name box up here and I'm just going to type uh the name of here so I'm
just going to call this performance performance bonus just like this and hit enter and now what I
can do if I go down here and try the same formula and this one multiply D7 multiply performance mode
bonus all I have to do is start typing it here so it makes a little easy to remember and I can
copy this down easily now what I really like about this I'm going to go to another sheet here where
it says sales bonus let me give you a couple of different examples so with the sales bonus
I'm going to go back to this here and I'm going to name this cell so rather than it just being
D3 I'm just going to type in sales bonus just like that and now I'm going to go back to the
sales bonus page so I'm going to do a formula here with an IF function so I'll just start it
here here is it right here so it's going to be if sales here is going to be greater than 500
000 what do I want to have happen well it's going to give it the sale so all I have to do is type
it in sales bonus other than that it's going to be zero so I'm just going to go ahead and end it
and there it is I can go ahead and add the dollar sign I can copy this down so you notice that it
only went to the ones that had above 500 000 on the list another one so let's do this one more
time so this one is the family bonus if they're family members they're going to get the one
billion dollar bonus so we're just going to go and rename this here so I'll call this family bonus
and now we'll go to the last sheet here so again we'll just do another one so
do the other if and what's our if this time so it's going to be if this right here equals Roy
okay that's our our logical test then they're going to get the family bonus so we're going
to get the family bonus other than that they get zero so let's see he got the one billion
dollars I'll give it some formatting and copying it down and you can see all the people that are
part of the Roy family got the one Billy dollar billion dollar bonus so rather than
using absolute cell referencing the next time try naming the cell and seeing how this works
for you I really like this approach and I really like it when I'm using different sheets it makes
it just easy to remember once you have it named let me know if you like this tip or not thanks
for watching this time of teachers Tech I'll see you next time with more Tech tips and tutorials