I normally don’t think absolute and relative cell references are difficult, until I try and mix them in one formula with two cell references. Come along and see what I mean.
Relative Cell Reference
The first example is a worksheet with data for Quantity, Price, and Total. The formula in cell C2 is Price times Quantity, or =B2*A2.
There are two cell references in this formula, B2 and A2. Both have a column reference (B and A) and a row reference (2).
You’ll notice that B2 changes to B3, B4, B5, and A2 changes to A3, A4, A5 when copied down.
As we copy the formula in cell C2 all the way down to cell C5, both of these cell references change automatically. They are relative references. Copying down changes the row reference.
We can look at this same data, transposed to columns. Now the formula in cell B3, Quantity times Price, is =B1*B2.
When I copy this formula across to C3, D3,and E3 you’ll notice the row stays the same, but the column reference changes.
Still a relative reference, but copying across changes the column reference.
Absolute Cell Reference
I’ve changed my spreadsheet example to calculate the Tax for Quantity times Price. The Tax Rate is located in cell B7.
The formula for Tax in cell C2 is Quantity times Price times Tax Rate, or =A2*B2*$B$7.
The cell reference for B7 is an absolute reference, which is needed because the Tax Rate is fixed in one place.
The reference to cell B7 is modified by using the dollar sign ($) before the column and row reference.
By doing so, B7 will remain constant as I copy the formula down to C3, C4, C5.
The other two cell references are still relative references and change as the formula is copied down. Although, if you’ll notice, neither of the column references change, they’re still A and B.
What this means is that the reference to cell B7 needs only an absolute row reference for this formula to work. As you see below, B$7 is now the cell reference and row 7 will not change when you copy the formula down.
If we transpose this data to a column setup and use the formula =B1*B2*$B$5 the first two cell references are relative, and $B$5 is absolute.
Copying the formula across changes the first two cell references, but not the cell reference for Tax Rate.
Notice the row reference doesn’t change, but the column reference does, so we could have used $B5, freezing the column reference with the dollar sign ($) and the formula would’ve worked perfectly.
Changing Absolute and Relative Cell References
Instead of manually typing in the dollar sign ($) there’s a shortcut to changing the cell reference. You have to be in Edit mode for this to work. Select a cell to modify, then enter Edit mode by pressing the F2 button or use the mouse to click inside the formula bar.
Select the cell reference you want change and press the F4 button to toggle through the different states. If your formula is =A1 hitting the F4 button repeatedly will give you =$A$1, then =A$1, then =$A1, then back to =A1.
Note: Don’t try this in Excel 2008 for Mac.
Absolute verses Relative Cell Reference — The True Test
The spreadsheet below is a square footage reference table for a particular room size. The formula in cell C3
can be modified with mixture of absolute and relative references so that it can be copied down and across to fill the entire table. I usually funk this test, hence the reason I wrote this post.
Side 1 data is fixed in row 2 and not going to change. As we learned above, copying down will change the row reference so I’ll put a dollar sign ($) in front of the reference to row 2 to make it absolute.
Side 2 data is fixed in column B and since copying across will change the column reference, I’ll put a dollar sign ($) in front of the column B reference to make it absolute.
Now my formula is
which will work for all the cells in this table. Here’s a look at how some of these formulas appear when copied.
To keep the formatting from being copied and utilize a new feature in Excel 2010, Copy the formula in cell C3 and Paste as Formulas by doing the following:
- Select cell C3
- Copy, then select cells C3:J12
- Click the Paste drop-down from the Home menu and hold the mouse over the Paste as Formulas icon to see a preview of what the paste operation will look like (new in Excel 2010) then click to complete.
I’ve dealt strictly with cell references here, but ranges can also be relative, absolute, or mixed references.