Learning Objectives

  • Distinguish relative (A1), absolute ($A$1), and mixed ($A1, A$1) references
  • Use F4 to cycle reference locks while editing formulas
  • Choose the right reference type for VAT, tax, commission, and matrix tables
  • Audit formulas with Show Formulas (Ctrl+`)

Sample file: sales_data_large.csv

Why References Matter

When you copy a formula down a column, Excel shifts cell addresses unless you lock them. Wrong locks are the #1 cause of incorrect totals in student workbooks.

1) Relative References (A1)

Relative references change when copied.

A (Units) B (Price) C (Revenue)
10 250 =A2*B2 → 2500
8 180 copy down → =A3*B3

When to use: line totals, row-by-row calculations.

2) Absolute References ($A$1)

Both column and row stay fixed.

Setup: put VAT rate 0.13 in H1. Label G1 as VAT Rate.

=D2*$H$1

Copy down: D3*$H$1, D4*$H$1 — the rate never drifts.

When to use: tax rate, bonus constant, exchange rate, single parameter reused in many rows.

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

Lock only one axis.

Commission matrix example

  • Product bases in A5:A8
  • Rates in B4:E4 (5%, 8%, 10%, 12%)
  • Formula in B5:
=$A5*B$4
  • $A5 keeps the product base when you copy across
  • B$4 keeps the rate row when you copy down

Fill B5:E8 to build the full matrix.

4) Choosing the Right Type

Goal Use
Same pattern each row Relative
One constant for all rows Absolute $H$1
2D table (row item × column rate) Mixed
Entire column when inserting rows Prefer Tables (Ctrl+T) with structured refs

5) Workflow Tips

  1. Type the formula for the first row only; verify the result.
  2. Click the reference in the formula bar and press F4 until the $ signs match your intent.
  3. Drag the fill handle; spot-check two more rows.
  4. Toggle Formulas → Show Formulas to audit locks quickly.
  5. Watch for #REF! after deleting cells that formulas still point to.

Common Mistakes

  • Leaving tax rate as relative → each row points to an empty cell
  • Locking too much ($D$2*$H$1 copied down always uses row 2 revenue)
  • Merged cells inside formula ranges

Practice Task

Using Sales data (or a 6-row product table):

  1. Relative formula for Revenue = Units × Price
  2. Absolute VAT using $H$1
  3. Mixed-reference commission matrix (4 products × 4 rates)
  4. One-sentence note under the matrix explaining each lock

📚 Continue to: Essential Functions