Switch between relative and absolute references in Excel 2016 for Mac

By default, a cell reference is relative. 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. For example, if you copy the formula =A2+B2 from cell C2 to C3, the formula references in C3 adjust downward by one row and become =A3+B3.

If you want to maintain the original cell reference when you copy it, you "lock" it by putting a dollar sign ($) before the cell and column references. For example, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same. This is an absolute reference.

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:

To change the type of cell reference

  1. Select the cell that contains the formula.

  2. In the formula bar Formula bar , select the reference that you want to change.

  3. Press COMMAND + T to switch between the reference types.

The following table summarizes what happens if a formula in cell A1, which contains a reference, is copied. Specifically, the formula is copied two cells down and two cells to the right, to cell C3.

If the reference is:

It changes to:

A1 (relative column and relative row)

C3 (the reference is relative)

$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)

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!