Absolute and Relative Cell References in Excel

by Gregory on October 26, 2010

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

Relative Rows FormulaThe 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).

Relative RowsYou’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.

Relative Columns Formula

When I copy this formula across to C3, D3,and E3 you’ll notice the row stays the same, but the column reference changes.

Relative Columns

Still a relative reference, but copying across changes the column reference.

Absolute Cell Reference

Absolute Row FormulaI’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.

Absolute Rows FormulasThe 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.

Absolute Row Only Formula

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.

Absolute Columns Formula

Copying the formula across changes the first two cell references, but not the cell reference for Tax Rate.

Absolute Columns

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

=B3*C2

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.

Absolute and Relative Example

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

=$B3*C$2

which will work for all the cells in this table. Here’s a look at how some of these formulas appear when copied.

Absolute Relative Formulas

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.

Paste As Formulas Excel 2010

I’ve dealt strictly with cell references here, but ranges can also be relative, absolute, or mixed references.

Related Posts Plugin for WordPress, Blogger...
olegko November 2, 2010 at 4:53 am

How to make absolute reference on Table column?
I have Table1 and use such formula:
=SUMIFS(Table1[Pay],Table1[Month],G9)

I want to “freeze” columns “Table1[Pay]” and “Table1[Pay]” in formula (like $A:$A).
How to do it?

Gregory November 2, 2010 at 10:28 am

There’s no need to try and put in a reference like $A:$A, because Table1[Pay] and Table1[Month] are already absolute references for those columns inside the table. If you add more data to the bottom of the table, the absolute reference changes to include all rows of the table. So unless you want to refer to data that is below the table, there’s no change needed to your formula.

Daniel February 2, 2011 at 7:24 am

I think you misunderstood his question.
The reference will expand to the bottom, thats right.
But what he was asking was the following:
When you have a formula that references Table1[Pay] and you select the cell in which that formula is entered and expand that cell, so that the formula will be in more cells to the right of the original cell, the column in the formula will change!
In the cell right to the original cell, the column is changed to the column to the right of the column “Pay”. In the next cell to the right it is the next column to the right etc.

Gregory February 2, 2011 at 10:59 am

Yeah, I noticed that if you just keep copying the formula to the right, it will repeat each column in the entire table over and over again. Weird. So the only way to ensure an absolute formula reference inside a Table is to manually type in something like =$A$3 which will defeat the automatic formula =Table1[Pay].

Comments on this entry are closed.

Previous post:

Next post: