Overview

After reviewing this page you should be able to:

- Understand and use relative, absolute, and mixed references

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

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

Profitin the column for2006:Because in each column profit is found the same way, by subtracting the value in the row for

Expensesfrom the value in the row forRevenues, you can easily copy the formula in the2006column across to the other columns, to cellsC4andD4, and the relative references will reflect the new positions of the formulas:>

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:

If you copy the formula in cell

B6to the other columns in cellsC6andD6with relative referencing, the formula will refer to the empty cellsB2andC2. You do not want to have to repeat the constant multiplier in these cells. Making the reference to cellA2an absolute reference,$A$2, insures that this same cell value is used as the multiplier in each formula as you copy it from cellB6toC6andD6.

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:

In the formula entered in cell

D2, the reference to row2is relative in both$B2andC2, so the formula can be copied down the column and the row numbers will adjust to give the correct difference in each row.On the other hand, the column references in

$B2,$B3, etc. are fixed references to columnB, while the referencesC2,C3, etc. are relative, so the column of formulas can be copied to columnFand will result in appropriate differences there, between columnBand columnE.

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

*Click on the appropriate button*