Cell references: relative, absolute and mixed
A cell address inside a formula is called a reference. A relative reference (B2) changes when the formula is copied, according to how far it moves: copied one row down, B2 becomes B3. An absolute reference ($B$2) always points to the same cell, because the $ sign fixes both the column and the row. In a mixed reference only one part is fixed: the column in $B2, the row in B$2. In Windows the F4 key cycles through the types (B2, $B$2, B$2, $B2). A shared number such as a tax rate or an exchange rate sits in one cell and is joined to every formula by an absolute reference; if the value changes, all the results update at once. A block of neighbouring cells is written with its two corner cells: B2:C5 is 8 cells in 2 columns and 4 rows.
| Form | Kind | When copied |
|---|---|---|
| B2 | relative | changes |
| $B$2 | absolute | stays |
| $B2 | mixed | column fixed |
| B$2 | mixed | row fixed |
In D3: =A1 → copied to E5: =B3
The $ sign freezes the column or the row (orange cell: the cell referred to)
The leader of a reading club keeps each pupil’s weekly pages in a table, while the weekly target sits in one separate cell. Every row refers to that one cell. When the target changes, the leader corrects it in a single place and all the rows update by themselves.
Budget planners, scientists processing experiments and game designers keep a fixed number in one place and use it in many calculations.
In your notebook, write a short list of daily jobs, draw one box in the corner saying “minutes per job”, and use arrows to show which rows depend on that box.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 12% | ||||
| 2 | 12 000 | 1440 | |||
| 3 | 8500 | 1020 | |||
| 4 | 15 000 | 1800 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 5 | 6 | 7 | |
| 2 | 2 | 10 | 12 | 14 |
| 3 | 3 | 15 | 18 | 21 |
| 4 | 4 | 20 | 24 | 28 |
“The dollar sign”: the cards say B2, $B$2, B$2 and $B2. The teacher says “the formula was copied from C2 to E4” and students write what each reference becomes (correct answers: D4, $B$2, D$2, $B4).