Switch between relative, absolute, and mixed references

By default, a cell reference is relative (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.). For example, when you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A), and in the same row (2). A formula that contains a relative cell reference changes as you copy it from one cell to another. As an example, if you copy the formula =A2+B2 from cell C2 to D2, the formula in D2 adjusts downward by one row and becomes =A3+B3. If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) by preceding the columns (A and B) and row (2) with a dollar sign ($). Then, when you copy the formula (=$A$2+$B$2) from C2 to D2, the formula stays exactly the same.

In less frequent cases, you may want to make a cell reference "mixed" by preceding either the column or the row value with a dollar sign to "lock" either the column or the row (for example, $A2 or B$3). To change the type of cell reference:

  1. Select the cell that contains the formula.
  2. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) Formula bar, select the reference that you want to change.
  3. Press F4 to switch between the reference types.

The following table summarizes how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right.

For a formula being copied: If the reference is: It changes to:
Formula being copied $A$1 (absolute column and absolute row) $A$1 (the reference is absolute)
A$1 (relative column and absolute row) C$1 (the reference is mixed)
$A1 (absolute column and relative row) $A3 (the reference is mixed)
A1 (relative column and relative row) C3 (the reference is relative)
 
 
Applies to:
Excel 2010