Know and understand characteristics and use of absolute and relative cell referencing

What is Cell Referencing?

In a spreadsheet, a cell reference tells the program which cell you want to use in a formula. Think of it like giving a friend a specific address to find a treasure chest.

• Relative reference (e.g., A1) changes when you copy the formula to another cell.
• Absolute reference (e.g., $A$1) stays the same no matter where you copy it.

Why Use Absolute References? 📌

Imagine you have a secret recipe in cell $B$2 that you want to use in many calculations. If you copy the formula =$B$2*10 to other cells, it will always point to $B$2, keeping the recipe unchanged.

Without the dollar signs, copying =B2*10 to the next row would change it to =B3*10, which might not be what you want.

Relative References – The Moving Target ↔️

When you copy a formula with a relative reference, the address moves relative to the new location. It’s like a robot that follows a path relative to its starting point.

Mixing Absolute & Relative – The Best of Both Worlds

Sometimes you only want the column to stay fixed, but the row to move. Use $A1 (fixed column) or A$1 (fixed row).

Example: =$A$1*B1 – The multiplier stays in $A$1, but B1 changes when copied down.

Exam Tip Box 📝

Tip 1: When you see a formula like =SUM($A$1:$A$10), remember it always totals the same range, no matter where you paste it.

Tip 2: Practice converting a relative reference to absolute by adding dollar signs. This is a common question in the IGCSE exam.

Tip 3: Use the F4 key (or double‑click the reference) to toggle between relative, absolute column, absolute row, and absolute references quickly.

Quick Practice Challenge

Copy the formula =A1*B1 from cell C1 to cells C2 and C3. What will the formulas look like in each cell?

1. C1: =A1*B1
2. C2: =A2*B2
3. C3: =A3*B3

Now change the formula in C1 to =$A$1*B1 and copy again. What changes?

1. C1: =$A$1*B1
2. C2: =$A$1*B2
3. C3: =$A$1*B3