1 - Introduction ~5 mins
Welcome
You already write formulas that add, multiply, and look up project numbers. Today is about what happens when you copy those formulas. One missing dollar sign can quietly break every row under it, and one well-placed dollar sign can lock a VAT rate, a fixed cost, or an hourly rate so the whole sheet stays honest.
By the end of this lesson, you will be able to:
- Use relative and absolute references correctly when formulas are filled
- Fix fill-handle formula breakage with $ locks
- Recognise mixed references and try a simple mixed fill as extension
- Refactor at least one portfolio formula so it copies correctly
Warm-up
Think about a formula you already use in your Something Real spreadsheet (VAT, a fixed fee, a rate, a target). If you dragged that formula down ten rows, would every row still point at the right cells, or would something slip?
2 - Key Concepts ~6 mins
Cell references tell a formula where to look. When you copy or autofill a formula, the sheet either moves those looks with the formula, or keeps them locked. The dollar sign $ is the lock.
| Concept | Why it matters | Example |
|---|---|---|
Relative reference — a plain address such as B4 that shifts when you copy the formula |
Row-by-row totals need to move with each item, not stay stuck on one row | Gross =B4+C4 filled down becomes =B5+C5 for the next merch line |
Absolute reference — a locked address such as $B$1 that never moves when you copy |
One shared rate, fee, or target must stay fixed for every row or the whole sheet lies | Irish standard VAT held in $B$1 so every net price multiplies by the same cell |
| Fill handle — the small square at the bottom-right corner of a selected cell | Fastest way to extend a formula, and the fastest way to spread a broken reference | — |
Quick read: $B$1 locks column and row. Plain B1 locks nothing. You will type the $ signs yourself in the demo (that always works on every device).
Quick match (class)
Your teacher will call out each job. Decide: relative or absolute?
- Net + VAT on the same row, copied down a price list
- One VAT rate cell used by every product row
Optional: if your teacher hands out the matching sheet, use it as a 2–3 minute pair check or keep it for early finishers later.
3 - Step-by-step Task ~15 mins
Build a small GAA club stall price sheet. First you will watch a formula break when the VAT rate is relative, then lock it with $, then add a relative gross column. Follow the tab for your spreadsheet app.
Core path (everyone): VAT block, item table, broken fill, absolute fix, gross column.
Optional stretch: mixed-reference price-tier strip at the end, whole-class with your teacher or for early finishers only.
4 - Common Issues ~4 mins
Common Issues
| Issue | Solution |
|---|---|
| Fill-down gives 0, huge numbers, or #VALUE! | Click a wrong cell and read the formula. If a shared rate moved (for example B1 became B2 or B3), edit it to {{code:$B$1}} and fill again. #VALUE! often means the formula walked onto a text heading. |
| F4 does nothing when I press it | Type the dollar signs by hand — that always works. F4 only helps on some Windows/Mac setups after you click the exact reference in the formula bar (fn+F4 on many Mac keyboards). On a Chromebook, skip F4 and type {{code:$B$1}} yourself. |
| Mixed formula is right across but wrong down (or the opposite) | Check which lock you need. Lock the column with {{code:$A14}} when filling right across tiers. Lock the row with {{code:B$13}} when filling down quantities. Type the $ signs, or use F4 on Windows/Mac to cycle until both locks sit where you need them. |
5 - Independent Practice ~20 mins
Independent Practice
Plan B: If you cannot find a shared rate, add one clearly labelled cell above your data (VAT, markup, hourly rate, or target cost). Point existing row formulas at it with {{code:$}} locks, fill down, and spot-check two rows. That still counts.
In an empty cell or a one-line note at the top of the sheet, write the cell you changed and the new formula (for example {{code:C4 now =B4*$B$1}}).
- At least one formula uses the correct reference type for the job (relative or absolute is enough)
- Filling that formula down or across keeps every result sensible when you spot-check two cells
- A shared rate or input (if you have one) stays locked while row-level totals still move with each line
- A short note on the sheet names the cell you changed and shows the new formula
- The updated workbook is still in your {{code:sm3}} folder under its existing project filename (do not rename it Untitled)
Unlock the full learning experience
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.