• Advanced
  • About 13 min
  • 5 practice questions

Cell references: relative, absolute and mixed

Predict what a formula becomes when copied by understanding relative, absolute and mixed cell references.

If something looks wrong, please tell us.

The most tested idea in spreadsheets is what happens to a formula when it is copied. The answer depends entirely on the type of cell reference used.

Three kinds of reference

Reference types
TypeExampleWhen copied
RelativeB2Both column and row shift with the copy
Absolute$B$2Neither changes
Mixed$B2 or B$2Only the part without the dollar sign changes

The dollar sign locks what follows it. In practice, the F4 key cycles a reference through the types while editing a formula.

Worked example 1: copy down

Cell C2 holds =A2*B2. Copy it down to C4. Each step down adds 1 to the row of every relative reference, so C3 has =A3*B3 and C4 has =A4*B4.

Worked example 2: a fixed rate

Prices are in A2:A5 and a tax rate is in cell E1. In B2 you write =A2*$E$1 and copy down to B5. The relative A2 moves (A3, A4, A5) while $E$1 stays fixed, so every price is multiplied by the same rate. Writing =A2*E1 would shift to E2, E3 and so on, giving wrong results.

Worked example 3: copy across and down

Cell B2 holds =B$1*$A2 and is copied to C3. Moving one column right and one row down: B$1 has a locked row so only the column shifts, giving C$1; $A2 has a locked column so only the row shifts, giving $A3. The formula in C3 is **=C$1*$A3**. This pattern is used to build multiplication tables.

A dollar sign locks what follows it: column, row, or both.

— Rule to remember

Key takeaways

  • Relative shifts, absolute is fixed, mixed locks one part.
  • Lock a constant such as a rate with $.
  • Shift each unlocked part by the distance moved.

Check your understanding

  1. 1. C2 holds =A2*B2 and is copied down to C6. What does C6 hold?

    1. =A6*B6
    2. =A2*B2
    3. =A6*B2
    4. =C6*D6
    Show answer

    Answer: A. Both references are relative, so each moves 4 rows down: =A6*B6.

  2. 2. Prices are in column A and a tax rate is in E1. B2 holds =A2*$E$1 and is copied down to B5. What does B5 hold?

    1. =A5*$E$4
    2. =A2*$E$1
    3. =A5*E4
    4. =A5*$E$1
    Show answer

    Answer: D. The relative A2 moves to A5; the absolute $E$1 stays fixed, so every price uses the same rate.

  3. 3. B2 holds =A2*E1 with no dollar signs and is copied to B4. What does the rate reference E1 become?

    1. E1
    2. E2
    3. E3
    4. F3
    Show answer

    Answer: C. Without dollar signs E1 is relative, so it moves 2 rows down with the copy to E3, which is why the results go wrong.

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

    1. =B$1*$A2
    2. =D$1*$A4
    3. =D$3*$C4
    4. =D$1*$A2
    Show answer

    Answer: B. The copy moves 2 columns right and 2 rows down. B$1: row locked, column moves to D, giving D$1. $A2: column locked, row moves to 4, giving $A4.

  5. 5. In the reference $B2, what stays fixed when the formula is copied?

    1. The column B only
    2. The row 2 only
    3. Both the column and the row
    4. Neither the column nor the row
    Show answer

    Answer: A. The dollar sign locks what follows it; here it stands before B, so the column is fixed and the row can change.

Privacy · Terms · Refunds · Delivery