Using Absolute and Relative References in Excel 2007 Formulas

By Ken Bluttman, Peter G. Aitken

You can use three types of cell references in Excel 2007 formulas: relative, absolute, and mixed. Using the correct type of cell reference ensures that formulas work as expected when you copy them to another location in the worksheet. Formulas and functions are at their most useful when using references, so you need to understand them.

Excel is dynamic when it comes to cell addresses. If you have a cell with a formula that references a different cell’s address and you copy the formula from the first cell to another cell, Excel updates the cell reference inside the formula. Try an example:

  1. In cell B2, enter 100.

  2. In cell C2, enter =B2*2.

  3. Press Enter.

    Cell C2 now returns the value 200.

  4. If C2 is not the active cell, click it once.

  5. Press Ctrl+C, or click the Copy button on the Home tab.

  6. Click cell C3.

  7. Press Ctrl+V, or click the Paste button on the Home tab.

  8. If you see a moving marquee around cell C2, press the Esc key.

    Cell C3 should be the active cell, but if it is not, just click it once. Look at the Formula bar. The contents of cell C3 are =B3*2, and not the =B2*2 that you copied.

    What happened? Excel, in its wisdom, assumed that if a formula in cell C2 references the cell B2 — one cell to the left — then the same formula put into cell C3 is supposed to reference cell B3 — also one cell to the left.

    Relative references adjust when you copy a formula.
    Relative references adjust when you copy a formula.

When copying formulas in Excel, relative addressing is usually what you want. That’s why it is the default behavior. Sometimes you don’t want relative addressing but rather absolute addressing. This is making a cell reference fixed to an absolute cell address so that it does not change when the formula is copied.

In an absolute cell reference, a dollar sign ($) precedes both the column letter and the row number. You can also have a mixed reference in which the column is absolute and the row is relative or vice versa. To create a mixed reference, you use the dollar sign in front of just the column letter or row number. Here are some examples:

Cell Reference Types
Reference Type Formula What Happens After Copying the Formula
Relative =A1 Both the column letter A and the row number 1 can change.
Absolute =$A$1 The column letter A and the row number 1 do not change.
Mixed =$A1 The column letter A does not change. The row number 1 can
Mixed =A$1 The column letter A can change. The row number 1 does not