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

📊 20 Spreadsheets – Replicating Formulae with Absolute & Relative References

1. Understanding Cell References

Think of a spreadsheet like a city map. Relative references are like walking directions that change depending on where you start. Absolute references are like a fixed landmark (e.g., the city hall) that stays the same no matter where you stand. 📐 Example: If you write =A1+B1 in cell C1 and copy it to C2, the formula becomes =A2+B2 – it moves with you. 📊 If you write =A$1+B$1 and copy it to C2, the row stays 1 but the column changes: =A$1+B$1 → =B$1+C$1.

2. Relative References – Move with the Formula

1. Enter a simple sum in cell D1: =A1+B1.
2. Drag the fill handle down to D5. Each row now adds the corresponding A and B cells.
3. Copy the whole column to the right (E1:E5). The formula automatically updates to =B1+C1, =B2+C2, etc.

3. Absolute References – Lock the Cell

Use the dollar sign ($) to lock a row, a column, or both. 📌 Locking a column: =A$1+B$1 – the row stays 1, the column changes when copied horizontally. 📌 Locking a row: =A1+$B1 – the column stays A, the row changes when copied vertically. 📌 Locking both: =A$1+$B$1 – the reference never changes, great for constants like a tax rate or a fixed discount.

4. Mixed References – Combine Both

Example: In a sales sheet, you might want the product price (column A) to stay the same while the quantity (column B) changes. Formula in C1: =A1*$B$1 – the price moves with the row, the tax rate stays fixed. Copying C1 down keeps the tax rate constant while updating the product price.

5. Practical Example – Budget Sheet

6. Key Points (Colourful Box)

🔑 Absolute Reference – Use $ to lock a cell. 🔑 Relative Reference – No $; moves with the formula. 🔑 Mixed Reference – Lock one part only. 🔑 Practice – Copy formulas across and down to see how references change. 🔑 Exam Tip – When asked to “replicate a formula,” first decide which part should stay constant and add $ accordingly.

7. Exam Tips (Colourful Box)

• Read the question carefully. Identify the cell that must remain unchanged. • Use the $ symbol. Remember: $A$1 locks both row and column. • Check your work. After copying, verify that the formula still refers to the correct cells. • Practice with sample sheets. The more you copy and paste, the quicker you’ll spot which references need locking. • Time management. Allocate a few minutes to double‑check references before submitting.