Formulas 2: Using Relative, Absolute, and Mixed References

Overview
After reviewing this page you should be able to:


About Cell References

Cells or ranges can be referred to or referenced in formulas in three different ways: relative reference, absolute reference, and mixed reference.

Relative References

Cell references typed in as A1, B4, etc. are relative references (unless they refer to cells outside the worksheet). When you copy or fill a formula containing a relative reference, the row numbers and column letters change, reflecting the same offset or relation that was in the original cell. If the formula =B2+1 is in cell B4, when it is copied to cell D4 the formula will be =D2+1, because cell D2 is in the same position relative to D4 that B2 is relative to B4. Relative references are useful when you want to copy a formula across rows or down columns, where the formula refers to cells based on their relative positions.

For example, suppose you have the following information in a worksheet, and you have entered the formula for Profit in the column for 2006:

=B2-B3

Because in each column profit is found the same way, by subtracting the value in the row for Expenses from the value in the row for Revenues, you can easily copy the formula in the 2006 column across to the other columns, to cells C4 and D4, and the relative references will reflect the new positions of the formulas:

=C2-C3 and =D2-D3>

 

Absolute References

The dollar sign $ is used in Excel to fix or "lock in" row and/or column references. If the formula =$B$3+B4 is in cell B5, when the formula is copied to cell B25 the formula will be =$B$3+B24. The reference to cell $B$3 is fixed or absolute — it is locked into cell B3 and does not change when the formula is copied; the reference to cell B4 is relative, and so it changes to B24.

For example, suppose you have a constant multiplier in your formula:

=A2*B5

If you copy the formula in cell B6 to the other columns in cells C6 and D6 with relative referencing, the formula will refer to the empty cells B2 and C2. You do not want to have to repeat the constant multiplier in these cells. Making the reference to cell A2 an absolute reference, $A$2, insures that this same cell value is used as the multiplier in each formula as you copy it from cell B6 to C6 and D6.

=$A$2*B5, =$A$2*C5, and =$A$2*D5

Mixed References

In this case one of either the row or the column reference is absolute, and the other is relative. Careful use of mixed references can save a lot of time in creating spreadsheets. The reference =$B3 can be copied across columns and down rows to produce references to the B column cell entry in each column of every row; =B$3 can be copied across columns and down rows to produce references to the row 3 cell entry in each row in every column.

For example, suppose you have the data shown in the spreadsheet below:

=B2-C2

In the formula entered in cell D2, the reference to row 2 is relative in both $B2 and C2, so the formula can be copied down the column and the row numbers will adjust to give the correct difference in each row.

=$B2-C2

On the other hand, the column references in $B2, $B3, etc. are fixed references to column B, while the references C2, C3, etc. are relative, so the column of formulas can be copied to column F and will result in appropriate differences there, between column B and column E.

=$B2-E2


This interactive animation demonstrates the effects of using different reference styles:

Choose a Style for the Reference to A1

Click on the appropriate button

       

animated frames