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

Step-by-Step Lab Exercise: Invoice with VAT

  1. Enter this data in A1:C5, and put VAT Rate in G1 and 0.13 in H1:

    Product Units Price (Rs.)
    Notebook 10 250
    Pen 50 20
    Calculator 4 1,200
    Stapler 6 350
  2. D1 = Revenue; in D2: =B2*C2 (relative). Fill down to D5.
  3. E1 = VAT; in E2: =D2*$H$1 (absolute rate). Fill down.
  4. F1 = Total; in F2: =D2+E2. Fill down.
  5. In row 6, total each column with =SUM(D2:D5) and so on.

Expected results:

Product Revenue VAT Total
Notebook 2,500 325 2,825
Pen 1,000 130 1,130
Calculator 4,800 624 5,424
Stapler 2,100 273 2,373
Total 10,400 1,352 11,752

Test the lock: change H1 to 0.15. Every VAT cell should update (total VAT becomes 1,560). If only the first row changes, the reference was not absolute.

Mixed-reference check: with bases 10,000 and 20,000 in A10:A11 and rates 5% and 8% in B9:C9, the formula =$A10*B$9 filled across and down should give 500, 800 and 1,000, 1,600.

Key Takeaways

  • Relative references move when copied; absolute references ($A$1) stay fixed.
  • Mixed references lock only the row or only the column, ideal for two-way tables.
  • Press F4 to cycle through reference types while editing.
  • Test a formula by changing an input and checking every dependent cell.

Exam-Style Questions

Viva (short answer)

  1. Distinguish between relative, absolute, and mixed cell references.
  2. What does the $ sign do in a cell reference?
  3. What is the function of the F4 key when editing a formula?
  4. When would you use a mixed reference? Give an example.
  5. What does the #REF! error mean?

Lab test tasks

  1. Create a sales sheet that calculates revenue, VAT at 13% using an absolute reference, and total for six products.
  2. Build a 4 × 4 commission table using a single mixed-reference formula filled across and down.
  3. Turn on Formulas → Show Formulas and explain which references are relative, absolute, and mixed.

📚 Continue to: Essential Functions