Relative vs Absolute Cell Reference in Microsoft Excel - What's the difference?

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 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