Module 1 · The sheet that survives a handover Lesson 3 of 15
Absolute and relative references
The dollar sign in $B$2 is the difference between a formula you write once and a formula you write forty times. This is the single most common gap in a fresher sheet.
The concept
A reference without dollars — A2 — moves when you copy the formula down, which is what you want for rows. A reference with dollars — $A$2 — stays fixed, which is what you want for a rate, a target or a lookup table that lives in one place. Two characters decide which.
Mixed references are the useful in-between: $A2 locks the column but lets the row move, and A$2 does the reverse. They are how a multiplication table is filled from one formula, and how a formula dragged across months keeps pointing at the same starting cell.
=A2*$B$1 the rate in B1 stays fixed as you copy down
=B2*(1+$C$2) the tax rate stays fixed
=$A2 column locked, row moves
=A$2 row locked, column moves
Copy a formula without dollars and get errors?
Click the cell, press F2, and watch which reference moved.Common mistakes
The mistake is writing a formula without dollars, copying it down, and getting errors that look like missing data. Click the cell with the error, press F2, and watch which references moved — the wrong arrow is the whole bug, and it becomes obvious in two seconds.
Worked example
A rate stored in $B$1 and referenced from C2 as =B2*$B$1 copies down correctly. Written as =B2*B1 it drifts to B2, B3, B4 and produces plausible wrong numbers — the worst kind of wrong.
Your practice task
Build a small invoice sheet with one tax rate cell, then copy the formula down twenty rows and check that every result still points at that one cell.
Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.
Key takeaway
A reference without dollars — A2 — moves when you copy the formula down, which is what you want for rows.
Keep your place in this course
A free account saves which lessons you have finished and unlocks the certificate. The lesson itself stays free to read.
Create free account or sign inCourse complete
You finished every lesson.
Your certificate is ready, and the projects you built are the proof to attach to an application.
Almost there
15 lessons left of 15
Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.