Alternative to Absolute Cell Referencing in Excel: Using Named Cells

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 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 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 logical test then they're  going to get the family bonus so we're going   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