Absolute Cell References in Excel Calculations

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 one and I'm going to 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. 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 going to 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.

I will say, take heart. Do not give up. Take a look at what's underneath the formula so you can investigate what's going on.

You want to be like a good mechanic. A good mechanic is not just going to look at a stalled car and wonder how to fix it. They're actually going to open up the hood and look underneath to see what's going on.

Now I'm going to 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 it by the 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 F2, enter F2, enter F2.

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 going to go over here and then I'm going to go use the autofill handle.

Yay, I'm a genius. So I got the right answers because it matches the answer column. This is not going to 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 going to 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 going to 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 for watch. That's because I'm multiplying it by the text that I can't see right next to the sales tax column. All right.

So I'm going to undo all of that. We're actually going to have to do this the right way. This is what you're going to 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. So my cursor is on the cell that I need to lock.

I am not going to click on 8.875 percent because this is where 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. 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.

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

That's a very simple way to explain it. And I would say that the very highest number has a very special name. It's called the total.

So 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, use autofill. Not so promising. Now, this should occur to you as deja vu.

There's something you forgot to do. If I, as a mechanic, go and take a look at what's going on in this cell. OK.

And this one. And this one. And this one.

You sort of get the picture. So I want to lock the red cell again in this case, which is the total. So I'm going to 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.

In the class, we would have you complete this exercise. We want to get the total for all of the expenses, both by 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 Connor'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 going to do exactly what we did in the last exercise. This is going to be equal to 420 divided by the very highest number.

The 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 that are in row number and make sure that that value does not shift.

I will then press ENTER. And then I'm going to use autofill. I'm going to show you a trick.

There's an underline here. When I use autofill to copy this formula down, it's going to 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.