Total Pageviews

Thursday 17 March 2011

Relative and Absolute Cell References in Excel Formulas


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:
Absolute Cell References in Excel Formulas
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:
Cell References in Excel

References in Excel
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 anabsolute 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
Cell References
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:
Excel tutorial
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.

2 comments:

  1. We appreciate you this type of fantastic blog. Where else could anyone have that kind of info designed in this type of perfect way? I own a presentation that we are presently working on, and I have been getting the appearance out for such information.
    condominiums edgewater

    ReplyDelete
  2. I liked the example for relative references. Very nice, easy to understand.

    good one here as well:

    Absolute references in Excel

    ReplyDelete