Working with relative, absolute, and mixed references

- We're looking at a worksheet called Absolute in our workbook 03 - Creating Formulas and Functions. In cell F2, we're about to calculate a new salary not only for Lisa but for all the others in our list here. Now, if everybody's going to get $2,000 more, we could write a simple formula here, equal E2, type it or click it, plus 2,000. And we can copy this down the column. Even before pressing Enter, we can point to the lower right-hand corner.

Click and drag downward, and that formula will have been copied downward, and our original formula, E2 plus 2,000, has been copied here automatically to say E3 plus 2,000, and this is what we call a relative reference. As we copy a formula into a different row, the row reference in the formula, the row 2, becomes row 3, and becomes row 4, and down at the bottom, row 13. That happens automatically.

It's referred to as a relative reference. But what if our salary calculation is going to be based on a percent increase 2.1%? Instead of this formula, we're going to make a change to it. So in cell F2, and I'll just start all over again, equal E2, asterisk, meaning multiplication, H2. Now if I press Enter, I will only get the amount of the increase. It'll look like this. That's the amount of the increase.

So we want that incremental value to be added onto the existing salary. Plus E2. Now, there are different ways to write this formula. Some people will put this in parentheses but change the E2 there to be a one. We can certainly do it that way, and there are a few other ways as well too, but the point is we will get a correct answer here. The new salary for Lisa will be $55,695. If we copied this downward all the way to the bottom, as we look back in forth, we realized only Lisa is going to be happy with this pay increase because what's happened here in every other cell below.

The numbers have not changed. So what's the formula in F3? Correctly the references to cell E2 have been changed, but the reference to H1 got changed as well. It becomes H2. And as you might guess, down here, for example, this refers to cell H7, and down here it refers to H10 and so on. Now, a poor solution here would be to fill in all these cells with 2.1. You could imagine lists like this may be having hundreds, thousands, or tens of thousands of names. That would not be the best way to do this.

So here's a situation where we do not want that reference to H1 to be a relative reference. We want it to be absolute. We want this location to be more precise, not to change. So reference is made absolute by adding dollar signs, and that is not intuitive or at all logical. I wouldn't call it illogical, but if we put a dollar sign in front of the H and in front of the 1 here, that means it's an absolute reference. Now, you could mistakenly be saying, "Oh, I get it.

"Dollar signs, salary. I see what's going on here." That has nothing to do with it. The dollar signs as we use them here mean if we copy this formula, this reference will not change. This is referred to as an absolute address. Now, instead of typing the dollar signs, what you could do, and if you saw this coming before writing the formula, you would know ahead of time, right after the H1 you can simply press F4. Now, when you're editing, you could click in front of it, you can click between two, any of those.

Make no difference, but when you press the function key F4, Excel does the typing for you, and, again, I stress this is not intuitive in any way. It's the action we take to make sure that this formula, if it's copied, this portion of it will not change. So we want to copy this from the lower right-hand corner. And if I press Control + Enter, the active cell will not move down. It'll stay in place, and I'll drag from the lower right-hand corner down the bottom. The salaries have all gone up.

Let's click any one of these. Double-click here. We see what's happening. They all refer to cell H1. Now, if we were just writing the formula for the first time, and we know that we need that, equal E2 times H1. At that point, we'll simply press F4 and then plus E2. So more and more you'll see the need for this. Anybody who uses Excel with any kind of depth will need this feature at one time another.

It's called an absolute reference. In certain situations, and it's beyond the scope of this course, you will have the need to make sure that just the row reference is absolute, so we might have a dollar sign just in front of the row reference or in other cases, just in front of the column reference, and it could be the case, actually, here. We really only strictly need a dollar sign in front of the row reference because we're not moving or copying our formula into a different column, so we could get away with not having the dollar sign in front of the H, but it's so simple to apply these with the F4 key.

Doesn't hurt in this case. Works just fine. It's an absolute reference, and it's absolutely indispensable as you work with Excel.