One of the more important (and confusing) aspects of creating Excel formulas is relative and absolute cell references. Today, with your permission, we will dispel the confusion and bring you one step closer to becoming an Excel formulas master.
In this article, we’ll cover the basics of referring to a cell when creating a formula, when to use relative or absolute reference, and how to easily change between them.
Relatively Wrong
For the sake of demonstration, let’s assume that we have been handed the following assignment from our fictitious manager at the fictitious XYZ Widget company: To calculate the weekly wages of some of our hour-based salary workers. Let’s look at the worksheet:
Starting with John, we’ll add a formula to calculate the pay, which is: the hours worked times the hourly rate.
To do this we will:
- Select cell C6
- Enter the formula: =B6*B3
And we get John’s weekly salary:
But watch what happens in the video below when we use the fill handle to copy the formula:
Hmmm, this doesn’t look right. We have an empty cell and an error value.
Introducing Absolute References
If we select cell C7 we’ll see that it contains the formula:
=B7*B4
B7 does contain the hours worked by Adam this week, but B4 is an empty cell. Note that it’s one cell down from the hourly rate cell (B3), which is the cell we wanted to use.
Selecting cell C8 shows =B8*B5. The hourly rate is now missed by two rows. So, it seems that using the fill handle went wrong here. The fill handle replaced the referred cells with a different reference, relative to the original cell.
But we need the hourly rate cell to stay the same. The way to do that is to use an absolute reference rather than a relative reference; this way, when we use the fill handle, the reference will stay to the hourly rate cell.
So, instead of the original formula, we will now use the following one:
=B6*$B$3
A very similar result; the only difference is that cell B3 is now referred to as $B$3. Excel uses the dollar sign to mark an absolute reference; in this case, both the column and the row are absolute (meaning this is a fully absolute reference).
Now, we’ll use the fill handle just as before, and:
Ah, that’s better. Note the formula in cell C7: the hourly rate part stayed as B3 (well, $B$3, but that’s the same thing in this case).
One final tip: You can use the F4 key to change the cell references between reference types.
Summary
In this article we discussed when and how to use absolute cell referencing. We also created a formula that combined both absolute and relative cell references. And we learned how to use the $ sign to create an absolute cell reference.
Now over to you … can you think of a use for absolute references which we didn’t cover here?
About the Author: Joseph Reese is the founder and chief formulas Expert at Excel-Formulas.com . If you want to improve your performance with Excel, head over to his website.
Excel can be a difficult program to master. If you are interested in learning more see this Excel video training course. They make everything look so easy!
