Absolute and relative cell references

One of the magical parts of working with formulas in Excel is that hard-coding values into your formulas is not necessary (in fact, it is not advisable). Whenever you enter a value into a formula, like SUM, you can feed Excel a "cell reference" rather than a number. A cell reference comes in the form A1, where A represents the column letter of the given cell and 1 represents the row number. When Excel sees a cell reference, it will visit the cell in question, pull out its value, and use that value in whatever formula you are writing.

You can also include cell ranges as cell references in the form A1:A5. In this case, Excel will include all cells between A1 and A5. For example, if you take the SUM of A1:A5, Excel will add up the values of cells A1, A2, A3, A4, and A5.

When you reference cells in this manner, you can do so with one or two "reference types": relative and absolute. The difference between these two reference types is that they have different behavior as your drag or copy and paste them to other cells. Relative references change and adapt as you copy and paste them; absolute references, on the other hand, do not. Using relative and absolute references in the right way is critical to success in Excel. Let's take a deeper look at how it works.

Relative cell references

Take a look at the following worksheet, which lists SnackWorld sales by month for various product categories.

Let's say that we want to sum up sales for each of these categories across all three of the months in our spreadsheet. Starting in cell C7, let's use a SUM formula to add up the range C4:C6.

=SUM(C4:C6)
Output: $16,000,000

We've got our sum for Cookies — $16,000,000 — and now it's time to copy our formula to the Brownies and Gummy worms columns. Let's copy the formula across to cells D7 and E7 by using the copy and paste functions. What happens?

=SUM(D4:D6)
Output: $24,000,000

Notice that in cell D7, our formula has changed. Rather than referencing the range C4:C6, it now references D4:D6. Why did it switch?

The answer is that we are using a relative cell reference in this formula. As we copy and paste it from cell to cell, the cells referenced will change relative to the cell that we started in and the cell that we copy and paste into. In this case, relative cell references are working to our advantage — we want our SUM function to act on different columns, and Excel has saved us work by doing that automatically!

Absolute cell references

Relative references work great in certain situations, but there are some times during which you may not want your formulas to change. Let's say that we want to insert another section in row 8 that calculates the taxes SnackWorld will owe on the revenue it collected over the three months of data:

First, we'll make a new cell, H4, to hold the tax rate that we'll use for our calculation. Then, starting in cell C8, we'll write a formula to multiply the total sales by the tax rate we've built into cell H4

=C7*H4
Output: $4,800,000

This formula works well for cell C8, but what happens when we copy and paste it to the right?

=D7*I4
Output: 0

It looks like the formula has broken, because our relative reference to cell H4 shifted to cell I4 when we copied and pasted. To fix this problem, we'll need to use an absolute cell reference.

We indicate absolute cell references with the $ symbol before a row number or column letter. The $ symbol tells Excel that it should not change the given row or column when copying and pasting.

Take a look at a new formula we've written for cell C8 using an absolute reference on the column letter:

=C7*$H4
Output: $4,800,000

When we paste this formula into the adjacent cells, the column letter does not change, because it is an absolute reference. Our problem is solved!

=D7*$H4
Output: $4,800,000

Absolute references can appear on rows or columns

An important thing to remember here is that absolute cell references can appear on rows, columns, or both. Here's a handy table that will show you what the $ sign means depending on where you see it in a cell reference:

Note that you can also lock rows and columns in a range. For example, the range $A1:$B8 will have its columns locked as it is copied and pasted, and the range A$1:B$8 will have its rows locked.

Hotkeys

The hotkey to cycle through locked rows, columns, and both is one of the most-used shortcuts in Excel. When typing a formula in the formula bar, use F4 to cycle through lock states on Windows and ⌘+T on a Mac. You'll need your cursor to be on a reference to a cell for this to work, so that Excel knows which cell to toggle.

Additional practice

For practice, take a look at the four images below, in which the formula in cell D7 has been copied to the range D7:E8. Can you tell why each output section shows what it does?

Absolute and relative cell references are one of the most useful — and confusing — features of Excel. So be sure to play around with these at length until you are comfortable with them. It'll pay off down the road!

Comments