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:

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

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.

ReplyDeletecondominiums edgewater

while facing the secrets, crimes and mysteries hidden behind the doors of their at the surface beautiful and seemingly perfect suburban neighborhood. The show is inspired, to an extent, by the 1999 film American Beauty. sky sports abroad

ReplyDeleteI liked the example for relative references. Very nice, easy to understand.

ReplyDeletegood one here as well:

Absolute references in Excel