hi there Jamie here today at teachers Tech hope you're having a great day today the other day I
had someone ask me about what was the difference between a relative versus absolute cell reference
in Microsoft Excel actually they didn't ask it like that they asked why is there dollar signs
around some cell references and not around others so what I mean here's a formula if
I look at this cell reference right here when it has a dollar signs around it like this this
is absolute so I have another formula over here and it doesn't have any dollar sign so
this is going to be uh the cell is going to be relative in that case and I want to show
you the difference of what what each one does here today on teacher's Tech foreign
so if you want to follow along with today's tutorial I'll put a link to this sheet Down
Below in the description you can click on it make a copy or download it to your computer
but let's get started here with relative versus absolute cell referencing in Microsoft Excel and
we're going to create a simple formula here uh to show totals here and I'm going to start actually
with a relative one and then I'll kind of show the difference between what the absolute will do here
so simple formula here I'm just going to start with an equal sign here and the total is going to
be this right here and it's going to be plus this spot and we don't have anything in the bonus yet
so I know it's going to be the exact same number but when we do the bonus then this number over
here this total will adapt to that so now what I'm going to do is just copy this down and I'm
just going to double click on that little green square there and it copies it all the way down
so that is relative so what that means is if I look in the next cell here so the first cell was
B2 plus C2 that's what I created as I copied it down it went move down with it so it's relative B3
plus C3 and then I can look at the next one here if I click on the next one it's B4 plus C4 so it's
moving down across and this works great now if I wanted to the bonus uh to this cell right here
I can't do the same thing unless 3.8 percent was all the way down so what I mean by this is let's
create we're going to create a relative one here so if I uh go ahead and hit equals it's going to
be this here and I'm going to multiply it by the Bonus amount which is 3.8 percent so if I
hit enter well that looks fine so B2 plus E2 this is still relative if I copy this down so I'm just
going to double click on this notice it works well for first one but then nothing was working for the
rest of them because what's happened is it's moving down if I look at this cell right here
now it's saying B3 and E3 so this is correct it moved down to do B3 but E3 is this blank and if
I look at the next one down here it says B4 and E4 so it's moving down I need to lock this one in
place so this is in E2 and it's always going to be E2 so I need to make that absolute and
that's where the dollar signs come into it now we can do this a few different ways I'm not going to
delete this I'm just going to modify this up here in the formula bar and I'm just going to put some
dollar signs around it you can see so if I put the dollar sign in here like so and I'm going to put
one after the 2 here and just hit return I need to recopy this down and I'm just going to do that
by double click and look how it all fills in here because now if I look at the next one and you can
see that it's B3 plus E2 up here and this one is staying in place so this is absolute when I put
the dollar signs around it locks it in place there so that would be kind of the difference we don't
always need to put the dollar signs around all of them so here's an example if I was just getting
rid of the uh two here the dollar sign in front of the two and I'm going to actually hit return here
I'm going to go back up to this one and change this one so I'm going to just get rid of the 2
here the dollar sign in from the two and then I'm going to copy this one down just like I did before
and it doesn't work so if I look at this and think well what's wrong with this one so it's the E is
locked in here but as it's moving down it's going to the tube wasn't locked in so it's going three
four you can see how it keeps moving E6 E7 but if I go back up to it and I'm going to change
the dollar signs around here I'm going to delete this one and then put the dollar sign in this spot
hit return and copy this one down everything looks correct now if I look now at each one notice it's
not moving because I'm not moving if I was moving across this way then it would start to change but
since I'm just moving uh down like this it's locking it in place so a different way you can
do this and I tend to use F4 instead so what I mean I'm going to delete this here this dollar
sign and if I go up to this and I'm just going to click in this right up here and if I use F4 and
remember depending on your keyboard if you have to hold down function F4 but if it's just F4 I go
ahead and select F4 and you can see that it adds dollar signs around then from the letter and the
number if I press it again it goes to the number in front of the number so it will lock that in and
if I press it one more time it goes to the letter and one more time just eliminates both of them so
F4 is a handy way to Simply put those dollar signs to make it an app loot value right away and I tend
to use that more than just putting the dollar signs in but either way it doesn't take too long
so I hope you like this tip here today about uh absolute versus relative and hopefully this makes
uh sense to you when to use either of them let me know in the comments if this makes sense thanks
for watching this time on teacher stack I'll see you next time with more Tech tips and tutorials