In this video tutorial, we'll walk through why cell referencing is important and how to use it properly with various step-by-step examples.
Absolute Cell References
Absolute cell references are very important in Excel when you're working with calculations where you have multiple values and a single individual value that is used in that calculation. I usually start this exercise by pretending that I'm in the class and I'm a student who has watched everything that we've covered already in level 1 and I'm gonna volunteer and say I know how to do this exercise. given everything that you just showed us how to do with calculations, I can figure out the answer to this. so now I will pretend to be that student and I will say okay you have products and you have prices. what you're trying to do is you're trying to figure out the sales tax for all of these products. I know how to do this because I eat out a lot and I know that when the waiter brings my dinner bill they take my individual amount and multiply it by a sales tax. so I'll type equal here and then I'll select this cell and then I'm gonna multiply it by the sales tax. now when I press enter I'll get an answer. I'll be able to see if it's right because we have a list of the answers here. I'll press ENTER. all right I'm on the right track and I have the right answer. now I want to be able to copy this formula for down for all the other values. now I might say okay I want to show you this really cool trick that I just learned if you go to the black plus sign and you double click it'll automatically copy all the values down. I'll double click and then I'm disappointed. At this point, I might start to reevaluate whether or not a career in excel is right for me because I thought I did everything I needed to do and I still didn't get the right answer.
Diagnosing the Problem
I will say take heart, do not give up, take a look at what's underneath a formula so you can investigate what's going on. You want to be like a good mechanic. A good mechanic is not just gonna look at a stalled car and wonder how to fix it, they're actually gonna open up the hood and look underneath to see what's going on. now I'm gonna go to the first value and press f2. Now I can see that what's happening here is I'm taking the value in blue and I'm multiplying about a value in red and I got the sales tax that's appropriate. so let me press ENTER. let me check this cell I'll press f2. oh that's interesting it looked like I moved down one row for blue and now I'm no longer selecting the sales tax, I'm actually selecting nothing so it looks like I'm multiplying 800 by a blank cell. Let me check the next cell. I'll press Enter F to enter F to enter f2.
Okay, now I think I recognize a pattern here. Every time I move down one row for blue I also move down one row for red I need to make sure that red doesn't move. Now let's say I needed to come up with the answer really quickly on a test and I didn't know how to use absolute cell references. Here's a quick potential solution. I'm gonna go over here and then I'm gonna go use the autofill handle yay I'm a genius so I got the right answers cuz it matches the answer column. This is not gonna impress the examiner because I am duplicating the sales tax 11 extra times and they'll say why did you do that.
Now, if I try to explain that if you move down one row for blue you move down one row for red they're gonna look at me funny so I need to come up with another solution because I don't want them to see my answer and my results look like that. so here's one potential solution there you go - problem solved. Everything looks great except it's not gonna work in the long run because if I start to type in the title for total because I want to add the price plus the sales tax. As soon as I press ENTER I experience a case of disappearing ink and then all of a sudden I lose my sales tax. Watch, that's because I'm multiplying it by the text that I can't see right next to the sales tax column. I'm gonna undo all of that we're actually gonna have to do this the right way. this is what you're gonna need to do. In the formula, you want to be able to lock one of the cell references I usually ask the class is it red or blue and the class usually says red. My cursor is on the cell that I need to lock. I am NOT going to click on eight point eight seven five percent because this is in it we're in the formula. I need to lock the cell the keyboard circuit you can use to place dollar signs in front of the column letter and row number is f4. you can manually write it but if you press f4 that automatically adds the values the dollar signs. there that locks the cell. it will keep it from moving when I use autofill. so now I'll press Enter everything looks the same. now if I go to that cell formula and use the autofill handle I will experience a different result and in this case, it's called the right answer.
Exercise 2
Now that I've done that I might want to check to see where else this might be useful well if we go over to exercise two. we have the same products but we're looking for a percentage of the total. when I was first learning about percentages the elementary way I described it to myself is I need to take the smaller number and divide it by the very highest number. it's a very simple way to explain it. I would say that the very highest number has a very special name; it's called the total. In order to come up with the percentage of a Mac computer, I will type the equal sign, I will select 1200, and I will divide it by the total. when I press ENTER things are very promising. now I go here to use autofill. not so promising. now, this should occur to you as deja vu. there's something you forgot to do. I, as a mechanic, go and take a look at what's going on in this cell, you sort of get the picture. I want to lock the red cell again in this case which is the total so I'm gonna press f4. f4 puts mathematical handcuffs that I call the dollar sign around i-22 and when I press ENTER I still get the same result. the difference will occur when I start to use autofill and now I have the results I need right there. So that's how you would use absolute cell referencing to lock a portion of the formula when you don't have equal pairs of numbers. this is usually the case when you're working with percentages and let's say sales tax because you only have one sales tax but you might have multiple products.
Exercise 3
In the class, we would have you complete this exercise. We want to get the total for all of the expenses both by the salesperson and for expenses. Here's a quick way to do that. select all the values and the blank cells and then click on autosum. that will sum all the values both horizontally and vertically. now that's done. I want to come up with the sales tax for Connor. That's going to be equal to Connors's pre-tax amount multiplied by the sales tax. Now, this is the value that I need to lock because I only have one sales tax but I have multiple pre-tax amounts. I'll press f4 press ENTER and then now I can take this and move it over to the right and get the sales tax for both fuller and Pulaski. Now I want to get a total for this. I can select both cells and just to be quick about this I'll click autosum here. we're gonna do exactly what we did in the last exercise. this is gonna be equal to four twenty divided by the very highest number a special name we have for that highest number is the total and then I'll press f4. that adds the dollar signs in front of the column letter and row number and make sure that that value does not shift. I will then press ENTER and then I'm gonna use autofill.
Formatting
Now I'm gonna show you a trick. There's an underline here when I use autofill to copy this formula down. It's gonna erase that underline because it's copying the formatting of the original cell. Now there's this little submenu here that I could use to say please fill the formula without changing the formatting. In other words please leave that underline there and with one click it's there and so now it's both aesthetically pleasing and it's accurate in terms of its calculations. we have some further reference information you could look at here and that's how you would use absolute cell referencing with your formulas.