Be able to replicate formulae using absolute and relative cell references where appropriate

Resources → Subject Notes → Information Communication Technology ICT → Lesson Plan

Topic 20 – Spreadsheets

Learning Objective

Be able to replicate formulae using absolute and relative cell references where appropriate.

1. Cell References

In a spreadsheet a cell reference tells a formula where to find the data it needs. There are three types of references:

  • Relative reference – changes when the formula is copied to another cell (e.g., A1).
  • Absolute reference – remains constant when the formula is copied (e.g., $A$1).
  • Mixed reference – either the column or the row is fixed (e.g., $A1 or A$1).

2. Why Use Absolute References?

Absolute references are required when a formula must always refer to the same cell, such as a tax rate, a fixed price, or a constant multiplier.

Example: If the tax rate is stored in cell B1 and you want to calculate tax for many items, the formula in column C would be =A2*$B$1. When the formula is copied down, A2 changes to A3, A4, … but $B$1 always stays the same.

3. Replicating Formulae

  1. Select the cell that contains the original formula.
  2. Drag the fill handle (small square at the bottom‑right corner) across the range where the formula is required.
  3. Excel automatically adjusts relative references and keeps absolute references unchanged.

4. Example Scenario

A small shop records the quantity sold and the unit price of three products. The shop wants to calculate:

  • Total price for each product (Quantity × Unit Price).
  • VAT (Value Added Tax) at a fixed rate of 20 % stored in cell E1.
  • Grand total (Total price + VAT) for each product.

5. Sample Spreadsheet Layout

Cell Content / Formula Description
A1 Product Header
B1 Quantity Header
C1 Unit Price Header
D1 =B2*C2 Total price (relative references)
E1 0.20 VAT rate (fixed value)
F1 =D2*$E$1 VAT amount (absolute reference to rate)
G1 =D2+F2 Grand total (relative references)
A2 Notebook First product
B2 15 Quantity sold
C2 2.50 Unit price (£)
D2 =B2*C2 Calculated total price
F2 =D2*$E$1 VAT for this row
G2 =D2+F2 Grand total for this row
A3 Pen Second product
B3 30 Quantity sold
C3 1.20 Unit price (£)
D3 =B3*C3 Calculated total price
F3 =D3*$E$1 VAT for this row (absolute reference unchanged)
G3 =D3+F3 Grand total for this row

6. Demonstrating the Copy Process

Assume the formula =B2*C2 is entered in D2. When the fill handle is dragged down to D3 and D4, the formulas become:

  • D3 = B3*C3
  • D4 = B4*C4

Because the references are relative, the row numbers adjust automatically.

For the \cdot AT formula =D2*$E$1 in F2, copying down yields:

  • F3 = D3*$E$1
  • F4 = D4*$E$1

Here $E$1 stays fixed, ensuring the same tax rate is applied to every row.

7. Common Pitfalls

  • Forgetting the $ signs results in the tax rate changing when the formula is copied.
  • Using absolute references for the quantity or price cells (e.g., $B$2) prevents the formula from adapting to each row.
  • Mixed references are useful when copying across columns but keeping the same row (e.g., A$1).

8. Quick Reference Cheat‑Sheet

Reference Type Notation Behaviour when copied
Relative A1 Both column and row adjust.
Absolute $A$1 Neither column nor row changes.
Mixed (column fixed) $A1 Column stays the same; row adjusts.
Mixed (row fixed) A$1 Row stays the same; column adjusts.

9. Practice Exercise

Complete the following steps in a spreadsheet:

  1. Enter the data shown in the table above (excluding the formula column headings).
  2. In cell D2 type =B2*C2 and copy the formula down to D5.
  3. Enter the \cdot AT rate 0.15 in cell E1.
  4. In cell F2 type =D2*$E$1 and copy down to F5.
  5. In cell G2 type =D2+F2 and copy down to G5.
  6. Check that the \cdot AT column always uses the value from E1 even after copying.

10. Suggested Diagram

Suggested diagram: Flow of copying a formula with relative and absolute references – show original cell, fill handle, and resulting cells with adjusted references.