• Practice
  • About 13 min
  • 5 practice questions

Cell references: relative, absolute and mixed

Predict how a copied formula changes with relative, absolute and mixed references.

If something looks wrong, please tell us.

When you copy a formula to another cell, the references inside it may shift. Whether they shift depends on the type of reference, and this is a favourite exam topic.

Three types

Reference types
TypeExampleWhen copied
RelativeA1Changes with the new position
Absolute$A$1Never changes
Mixed$A1 or A$1The part without $ changes; the part with $ stays

The dollar sign locks what follows it. The F4 key cycles through the reference types when editing a formula.

Worked examples

Example 1. C1 holds =A1+B1. It is copied to C2. The row moves down by one, so C2 holds =A2+B2.

Example 2. D1 holds =A1*$B$1. It is copied to D3. A1 is relative and moves down two rows to A3. $B$1 is absolute and stays. So D3 holds =A3*$B$1. This is how one fixed tax rate in B1 is applied to a whole column of prices.

$ locks. Without $ the reference moves when copied.

— Rule to remember

Key takeaways: relative moves, absolute stays, and mixed locks only one part.

Check your understanding

  1. 1. Which of these is an absolute reference?

    1. $D$7
    2. D7
    3. $D7
    4. D$7
    Show answer

    Answer: A. An absolute reference has a dollar sign before both the column and the row, as in $D$7.

  2. 2. E2 holds =B2+C2 and is copied to E6. What does E6 hold?

    1. =B6+C6
    2. =B2+C2
    3. =B6+C2
    4. =C6+D6
    Show answer

    Answer: A. The copy moves 4 rows down and no columns, so both relative references move down 4 rows: =B6+C6.

  3. 3. F1 holds =$A1+B$1 and is copied to G3 (one column right, two rows down). What does G3 hold?

    1. =$A1+C$1
    2. =$A3+C$1
    3. =$A3+B$1
    4. =$B3+C$3
    Show answer

    Answer: B. $A1 keeps column A but its row moves 2 down to 3. B$1 keeps row 1 but its column moves 1 right to C. So =$A3+C$1.

  4. 4. D1 holds =A1*$B$1 and is copied to D4. What does D4 hold?

    1. =A4*$B$4
    2. =A1*$B$1
    3. =A4*$B$1
    4. =D4*$B$1
    Show answer

    Answer: C. A1 is relative and moves 3 rows down to A4; $B$1 is absolute and stays.

  5. 5. A formula containing $A1 is copied one column right and one row down. The reference becomes

    1. $B2
    2. $A2
    3. $A1
    4. B2
    Show answer

    Answer: B. The column A is locked and stays; the row is unlocked and moves down one, giving $A2.

Privacy · Terms · Refunds · Delivery