Today you will
- see how copying a formula can break it
- fix it with a dollar sign lock
- fix one formula in your own project spreadsheet
Warm-up
Think of a formula in your Something Real spreadsheet, like VAT or a fixed fee. If you dragged it down ten rows, would every row still be right?
A relative reference like B4 moves when you copy the formula. So =B4+C4 copied down becomes =B5+C5.
An absolute reference like $B$1 never moves. The dollar sign is the lock.
The fill handle is the small square at the bottom-right corner of a cell. You drag it to copy a formula.
Every product uses the one VAT rate in B1, so the formula is =B4*$B$1.
You will build a price sheet for a GAA club stall. First you will watch a formula break, and then you will fix it with dollar signs. Follow the steps in the box below for your app. The last steps are a stretch for early finishers.
Row 3 headings (A3 to D3): Item | Net (€) | VAT (€) | Gross (€)
Stall items (from A4):
| Item | Net (€) |
|---|---|
| Club hoodie | 40 |
| Cap | 15 |
| Scarf | 10 |
Stretch answers to check:
| Qty | Retail | Bulk | Staff |
|---|---|---|---|
| 5 | 50 | 40 | 30 |
| 12 | 120 | 96 | 72 |
Common issues
- You get 0 or #VALUE! after filling down. The shared rate has moved, so change it to
$B$1and fill again. - F4 does nothing. Just type the dollar signs yourself, because that works on every device.
- The stretch formula is only right one way. Check that it says
=$A14*B$13, with a dollar before A and a dollar before 13.
Independent Practice
- Open your project spreadsheet in {{code:sm3}}, such as {{code:sm3_vat_calc}}, {{code:sm3_from_template}} or {{code:sm3_unit2_polished}}.
- Find a formula that uses a shared rate, fee or target.
- Add dollar signs to lock that cell, for example {{code:$B$1}}.
- Fill the formula down and check two cells by hand.
- At the top of the sheet, write the cell you changed and its new formula, like {{code:C4 now =B4*$B$1}}.
If you have no shared rate, add a labelled rate cell above your data and point your formulas at it.
- One formula uses the right reference type.
- The two cells I checked give sensible answers.
- A note shows the cell I changed and the new formula.
- My file is still in {{code:sm3}} under its own name.
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 - 2026. Unauthorised use, copying or distribution is not allowed.