Learning Objectives

  • Understand relative cell references (A1)
  • Use absolute cell references ($A$1)
  • Apply mixed cell references ($A1, A$1)
  • Choose appropriate reference type for formulas
  • Copy formulas with correct references

Step-by-Step Guide

1) Relative References (A1)

  • Change automatically when copied.
  • Best for repeated row/column calculations.

Example: =B2*C2 copied down becomes =B3*C3.

2) Absolute References ($A$1)

  • Locked row and column.
  • Use when one fixed value (tax rate, discount, exchange rate) is reused.

Example: =B2*$F$1 where F1 stores VAT.

3) Mixed References ($A1, A$1)

  • Lock only column or only row.
  • Useful for matrix multiplication and cross-tab formulas.

4) Choosing Correct Reference Type

  • Repeating formula by rows → mostly relative
  • Constant multiplier → absolute
  • 2D table fill → mixed

5) Copying Formulas Correctly

  • Fill handle for fast propagation
  • Use F4 to cycle reference types in formula bar
  • Audit with Show Formulas (Ctrl+)

Error Prevention

  • #REF! from deleted cells
  • Wrong totals from accidental relative references
  • Hidden errors after drag-fill

Practice Task

Create a sales sheet with:

  • Relative formulas for line totals
  • Absolute reference for VAT
  • Mixed references in a commission table
  • Formula audit notes explaining why each reference type was used

📚 Continue to: Excel Functions