How to Write Relative and Absolute Cell References

In this blog post, I’m going to explain what relative and absolute cell references are, some of the advantages and disadvantages of each type of reference, the situations when each can be used, and, of course, some examples.

If you’d like, you can jump to the bottom of the page and watch the YouTube that goes with blog post.

What is a Cell Reference?

Before I explain what the relative and absolute cell references are, it worth briefly describing what a cell reference is. To borrow from one of my previous posts, every cell in the Worksheet Grid is identified by a cell reference.

Excel Worksheet Grid

A cell can be referenced in one of two methods: “A1” cell reference style, and “R1C1” reference style. The A1 style is the most commonly used style of cell reference. For most users, this style is all you need to know. The cell reference is a combination of the column and row identifiers: for example, A1, B4, and Z30 all can be cell references.

A1 Reference Style

The second kind of cell reference, the “R1C1” cell reference style, describes both the row and column position using numbers. The row and column numbers are preceded by the letters R and C; for example, R1C1, R4C2, and R30C26 all can be references.

R1C1 Reference Style

When using R1C1 style to reference another cell, it is described interms of its relative position to the current cell. What does this mean, when referncing another cell 1 row up and 2 rows to left, it would be written as R[-1]C[-2].

R1C1 Relative Reference

This post will be written using only A1 style cell reference. I would recommend sticking with this style in most instances. If you want to switch between cell reference styles, this post provides more information.

Why Is It Important to Know the Difference?

It is important to know the difference between relative and absolute references when you want to move, copy, drag, or AutoFill formulas. One method will keep a static reference to a cell when moved, copied, dragged, or AutoFilled in a formula, whereas, the other will change the cell reference relative to how you move, copy, drag, or AutoFill. Understanding the difference can save you a lot of time.

Relative Cell References

Excel, by default, uses relative references. Relative cell references adjust and change when you move, copy, drag, or AutoFill a formula relative to how you have moved, copied, dragged, or AutoFilled for the formula.

For instance, suppose you have the formula =A1*2 in cell B1.

Formula in Cell B1

If you then copy cell B1 and paste it in B2, you end up with the formula =A1*2 in cell B2 like the image below.

Formula in Cell B2

The same will happen if you drag or AutoFill cell B1 to B2.

Examples

So how can this be useful? The following examples are just a couple of ways that relative cell references can save you time.

Example 1

In this first example, we will list goods for sale in column A; in column B we have the sales price and in column C we want to use a formula to work out the sales tax for each product in column A based on the price in column B. The original data would look something like the following image.

Product and Sales Price

Assuming the sales tax is 10%, we would use the following formula =B2*0.1 in cell C2 to work out the sales tax for the product in the first row.

Formula in Cell C2

If you drag the formula in cell C2 down to row 10, then the formula in cell C2 will be pasted into each of the rows and the reference to the row will be updated in the formula. The column remains unchanged because you only move the formula relative to the rows.

Formula in Row C2 to C10

When you’ve finished the worksheet should look like the image below.

Example 1 Sales Tax

To toggle between show a cell value and the formula, go to the Formulas tab on the ribbon and click the “Show Formulas” button.

Example 2

In this second example, we start with two groups of data. We have the same as before in column A, a list of goods for sale and in column B, we have the sales price. This pattern is now repeated in columns D and E; goods for sale in column D and in column E, we have the sales price. This time, we want to calculate sales tax in columns C and F.

Columns A, B, D and E

Like before, assuming the sales tax is 10%, we would use the formula =B2*0.1 in cell C2 to work out the sales tax for the product in the first row.

Column A, B, D and E formula in Cell C1

When we drag the formula in cell C2 down to row 10. The formula in cell C2 is pasted into each of the rows, and the reference to the row will be updated in the formula.

Column A, B, D and E formula in Cell C2 to C10.png

Now, highlight cells C2 to C10 and copy the cell contents (Ctrl+C on Windows or Cmd+C on Mac) and paste the copied cells into column F, cells F2 to F10. When you do this, the column references in the formulas will be updated to reference column E. For example, cell F2 has the following formula, =E2*0.1.

Column A, B, D and E formula in Cell F2 to F10.png

Advantage of Relative Cell References

The main advantage of a relative reference is that it can save a lot of time when you drag or AutoFill cells using the same formula in adjacent rows or columns.

Disadvantage of Relative Cell Reference

The main disadvantage of a relative reference is that you do not always want to update the row and column reference when you move, copy, drag, or AutoFill a formula. This is where absolute cell references come in handy.

Absolute Cell Reference

An absolute cell reference doesn’t change when you move, copy, drag, or AutoFill a formula. Therefore, if you want to constantly reference a specific cell, you should use an absolute cell reference.

To write an absolute cell reference, we need to introduce a new bit of notation in the cell reference, the dollar “$” sign. For example, an absolute cell reference could be $A$1. If you used this cell reference in a formula, no matter where you pasted the formula, the reference would not change. The cell reference remains locked on cell A1.

You can make the column, row, or both absolute. For example,

  • $A1 makes column “A” an absolute reference, but row 1 is a relative reference.
  • A$1 makes column “A” a relative reference, but row 1 is an absolute reference.
  • $A$1 makes both column “A” and row “1” absolute references.

To switch between the above combinations of absolute, relative, and mixed references, press the F4 key on Windows, or Fn+F4 on your Mac.

Examples

Example 3

In this first example, we are going to return to the list of goods for sale in column A; in column B, we list the sales price; and, in column C, we want to use a formula to work out the sales tax for each product in column A based on the price in column B. The original data would look something like the following image.

Product and Sales Price

However, this time, we are going have a separate cell for the sales tax rate. So in cell C1, we are going to type 10% and press enter. Now, in cell C2, enter the formula =B2*$C$1.

Formula in Cell B2 Absolute Reference.png

Drag the formula in cell C2, down to row 10. The formula in cell C1 is pasted into each of the rows, and the cell reference this time doesn’t change, it remains locked to cell C1.

Formula in Cell C2 C10 Absolute Reference

When finished the worksheet looks like the image below.

Formula in Cell C2-C10 Absolute Reference Values

Example 4

In this second absolute example, we start with two groups of data. Like before, we have the list of goods in column A, and, in column B, we have the sales price. This pattern is now repeated in columns D and E; list of goods in column D, and, in column E, we have the sales price. This time, we want to calculate sales tax in columns C and F.

Column A, B, D and E Absolute Reference

Like before, we assume the sales tax is 10%. This time, we are going use the same cell reference for the sales tax calculations in Columns C and F, using the same formula in example 3. In cell C1, we are going type 10% and press enter. Now, in cell C2, enter the formula =B2*$C$1 in cell C2.

Column A, B, D and E Absolute Reference C1.png

Drag the formula in cell C2 down to row 10. The formula in cell C2 is pasted into each of the rows. The cell reference doesn’t change; it remains locked to cell C1.

Column A, B, D and E Absolute Reference C2 to C10.png

Now, highlight cells C2 to C10, and copy the cell contents (Ctrl+C on Windows or, Cmd+C on the Mac) and paste the copied cells in column F, cells F2 to F10. When you do this, the cell references in the formulas remain locked on cell C1.

Column A, B, D and E Absolute Reference F2 to F10

When finished the worksheet looks like the image below.

Example 4

Example 5

For this example, we are going to apply different rates of sales tax to the same products in different columns by using a mix of absolute and relative references.

Using the previous examples, where column A is a list of goods and column B is the sales price, this time, C and D will have formulas to work different rates of sales tax. In cell C1, we will type 10%, and in D1, we will type 15%.

Example 5 10 and 15 Percent

Now, in cell C2, enter the formula =$B2*C$1. Notice that we have only placed a dollar sign before the row reference.

Example 5 cell C2

This means the reference to column C is relative (no dollar sign), but the reference to row 1 is absolute.

When we drag the formula in cell C2 down to row 10. The formula in cell C2 is pasted into each of the rows. The formula in cell C2 is pasted into each of the rows, notice that the cell reference does not change; it remains locked to cell C1.

Example 5 Formula C2-C10

Now, we highlight cells C2 to C10, and copy the cell contents (Ctrl+C on Windows or Cmd+C on a Mac) and paste the copied cells in column F, cells F2 to F10. When you do this, the cell references in the formulas remains locked on row 1, but the column reference has been updated to column D.

Example 5 Formula D2-D10

When finished the worksheet looks like the image below.

Example 5 Complete

There is an exception to an absolute reference not changing in a formula. If you add or delete rows and columns, and this changes the cell reference, then the absolute cell reference will be updated.

Advantages of Absolute Cell Reference

The advantage of an absolute reference is the fact it is absolute; it doesn’t change no matter where you copy, paste, drag, or AutoFill a formula. This is a time-saver for a cell or a range of cells that contain values that are used in different locations throughout a spreadsheet.

Conclusion

As you have seen, both relative and absolute cell references can be used, and they can be mixed together in the same formula. Both are time savers, and it is worth getting used to using both.

Absolute cell references are fantastic when you have a global value or values that a user might enter that are used throughout a spreadsheet.

Using absolute cell references makes spreadsheet maintenance easier but can often require a little more thinking about when you design the layout of your spreadsheet. Relative cell references are quick but can be time-consuming when maintaining a spreadsheet.

Here’s one final word on cell references. You can reference an entire column as a range of cells by typing the column without the row, for example, A:A; this can also be an absolute reference by typing A$:A$. I’ll leave you to think about how this might be useful.

That’s it for this blog post. I hope you found it useful and have learned something about absolute and relative cell references. If you liked this post, please share it with your friends on Facebook or Twitter.

To get a copy of the example spreadsheet used in this blog post, sign-up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit