An absolute reference uses dollar signs, as in $F$1, to stay fixed when a formula is copied. You need it whenever many formulas depend on one input cell, such as a rate, a limit or a lookup table. The sheet below is invented practice material.
Which part of a reference should be fixed?
Ask one question about the input: when I copy this formula, should this reference move? If yes, leave it relative. If it must always point at the same cell, lock it.
There are four styles:
| Style | Example | Behaviour when copied |
|---|---|---|
| Relative | F1 | column and row both move |
| Absolute | $F$1 | neither moves |
| Mixed, row fixed | F$1 | only the column moves |
| Mixed, column fixed | $F1 | only the row moves |
Worked example
Using the shop sheet from the previous lesson, totals are in D2:D4 (15.00, 9.00 and 19.20). A service charge rate of 0.06 sits in F1. Column E should show the charge.
Step 1: in E2 type =D2*$F$1. Result: 15.00 × 0.06 = 0.90.
Step 2: copy E2 down. E3 is =D3*$F$1, giving 9.00 × 0.06 = 0.54. E4 is =D4*$F$1, giving 19.20 × 0.06 = 1.152, which displays as 1.15 when set to two decimal places.
Step 3: change F1 to 0.08. All three charges update at once: 1.20, 0.72 and 1.536. Only one cell was edited, which is the main reason to use a rate cell.
The mistake to watch for
The student leaves the rate relative.
Mistaken formula in E2:
=D2*F1E2 shows 0.90, so it looks fine. Copied down, E3 becomes
=D3*F2. F2 is empty, so E3 shows 0.
The relative F1 moved down with the row. The correction is =D2*$F$1. A fast check is to look at the second copied cell: if a fixed input has changed address, it needed a lock.
Check yourself
Open each answer after trying.
1. =D2*$F$1 is in E2. What is it after copying to G4?
Show answer
Two columns right, two rows down. D2 becomes F4. The locked cell stays. Result: =F4*$F$1.
2. =B$2*C3 is in D3. What is it after copying to E4?
Show answer
One column right, one row down. B becomes C and the fixed row 2 stays. C3 becomes D4. Result: =C$2*D4.
3. Why is a rate cell better than typing 0.06 into every formula?
Show answer
Changing one cell updates every formula that uses it. Typed numbers would each need to be found and edited, and one would easily be missed.
Where this leads next
Next, choose an appropriate logical function to turn a rule into a result. If relative references are not yet automatic, revisit building a formula with correct relative references. Use the ICT practical task and evidence checker to review a finished sheet.
If you understand the lock but hesitate on mixed references, our teachers can drill that live in online one-to-one ICT tuition.