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
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.
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 123Learn.ie 2017 - 2026. Unauthorised use, copying or distribution is not allowed.