1.4Relative, absolute ($) & mixed references
The essentials in 30 seconds
- Copying shifts: =B2*C2 copied becomes =B3*C3 — the relative reference follows the movement, perfect except for a shared constant.
- The $ locks: $E$1 never moves (absolute), E$1 freezes the row, $E1 the column (mixed) — F4 cycles through the four combinations.
- The textbook case: =B2*$E$1 applies the same rate to the whole column: B2 follows each row, $E$1 stays frozen — without $, 0 everywhere.
Running mini-case — Wassim applies VAT
New instruction: apply 19% to 40 rows of net fees. Rate in E1, amounts in B2:B41. Wassim writes =B2*$E$1 in C2 and copies down: B2 becomes B3, B4… but $E$1 does not move. Without the $, the second row would have looked for E2 (empty) and displayed 0. Forty correct VATs in one drag — and when the rate changes, a single cell to update.
Continuation of case 1.1-1.3 — Wassim moves from single calculations to mass copying.
1. Copying shifts references
When you copy a formula down or right, its references shift: it is a relative reference (B2). =B2*C2 copied below becomes =B3*C3. This is very useful — except when a cell must stay fixed (a rate, a constant).
Wassim uses it for the rows that follow each other, and is wary of it for the single cell that serves everyone: the rate E1.
2. The $ freezes the reference
The $ sign locks: $E$1 = an absolute reference (column AND row frozen, never moves). E$1 freezes the row, $E1 freezes the column: these are mixed references. The F4 key cycles through B2 → $B$2 → B$2 → $B2.
Reading the $: it locks what precedes it — $ before the letter = frozen column, $ before the digit = frozen row.
3. The textbook case: applying a common rate
To apply the same rate (in $E$1) to a column of amounts, you write =B2*$E$1 then copy: B2 shifts (B3, B4…) but $E$1 stays frozen. Without the $, the formula would go look for E2, E3… (empty) and give wrong results.
Wassim's test: after copying, click a cell at the bottom — the bar must show =B41*$E$1. If E has moved, a $ is missing.
🔎 Concrete example — 🔎 Concrete example — A single VAT applied everywhere
Rate 19% in E1 (0.19), net amounts in B2:B4:
E1 = 0.19 (the rate)
C2 = =B2*$E$1 → copy down:
C3 = =B3*$E$1 (B shifts, $E$1 stays frozen) ✅
Without $: C2 = =B2*E1 → C3 = =B3*E2 (empty) → 0 ❌Reading: $E$1 keeps the rate; B2 follows each row.
Lesson: a shared constant is referenced absolutely to survive copying.
Section vocabulary
- Relative reference
- B2: shifts when copying (default behavior).
Simple example: B2 becomes B3 when copying =B2*$E$1 downward.
What it is not: a reference locked by $.
Reflex: data that scrolls = leave relative.
- Absolute reference
- $E$1: frozen in column AND row, never moves when copying.
Simple example: Wassim's rate in $E$1 survives 40 rows.
What it is not: a constant retyped hard-coded in each formula.
Reflex: shared constant = absolute systematically.
- Mixed reference
- E$1 (frozen row) or $E1 (frozen column): for two-way tables.
Simple example: A multiplication table filled with a single copied formula.
What it is not: a full absolute that blocks all copying.
Reflex: 2D grid = freeze one axis, free the other.
- F4 key
- While editing a formula, cycles through B2 → $B$2 → B$2 → $B2.
Simple example: Wassim sets his $ on the keyboard without typing them.
What it is not: a formatting button on the ribbon.
Reflex: cursor on the reference, then F4 until the right lock.
In practice — In practice — Freeze the right part
- Put a rate in E1 and amounts in B2:B5.
- In C2, write =B2*$E$1 then copy down; check that $E$1 stays frozen.
- Try without the $ and note the results at 0.
- Build a multiplication table with mixed references ($A2 and B$1).
Ready-to-say script — explaining the $ (30 s)
« The rate is in E1, unique for the whole table: I write it $E$1, absolute, so it never moves. The amount B2 stays relative: when copying, it becomes B3, B4… A frozen constant, data that scrolls — forty correct rows in a single drag. »
Say it while copying live and showing =B41*$E$1 in the bar.
Copy-paste template — common rate
E1 = 0.19 Unique rate (19%)
C2 = =B2*$E$1 Net B2 × frozen rate
Copy C2 down → B scrolls, $E$1 stays
Test: click C41 → bar = =B41*$E$1Wassim's VAT
As above: =B2*$E$1 over 40 rows — a rate changed in E1 recalculates everything.
Negotiated discount
=B2*(1-$E$1) with E1 = 0.10: the frozen discount applies to each price that scrolls.
Multiplication table
=$A2*B$1 in a grid: the column $A freezes the ones, the row $1 freezes the others — one formula for the whole table.
Exchange rate
Amounts in Dinars × rate in $E$1: same gesture — the constant does not move, the rows scroll.
✓ Best practices
- Freeze shared constants absolutely ($E$1).
- Use F4 to set the $ quickly.
- Think about copying BEFORE writing the formula.
✗ Common pitfalls
- Forgetting the $ on a rate → references shifted toward emptiness.
- Freezing everything by reflex (prevents useful copying).
- Choosing poorly between E$1 and $E1 in a two-way table.
Key takeaways
- Copying shifts relative references (B2 → B3).
- $ freezes: $E$1 absolute; E$1 / $E1 mixed.
- F4 cycles through the $ combinations.
- A shared constant is referenced absolutely.
48h challenge — apply tomorrow
Set a unique rate in E1, write =B2*$E$1, copy over 20 rows and check the formula bar at the bottom: B has scrolled, $E$1 intact.
- I distinguish relative, absolute and mixed.
- I freeze shared constants in $E$1.
- I check the copying in the formula bar.
Frequently asked questions
When should I use a mixed reference?
In two-way tables (e.g. a multiplication table or a price grid): you freeze the column of the ones ($A2) and the row of the others (B$1) so that a single copied formula fills the whole grid.
F4 does nothing, why?
You must be editing a formula with the cursor on (or just after) the reference to modify. Otherwise, add the $ by hand: $ before the letter freezes the column, $ before the digit freezes the row.
You copy =B2*E1 downward and get 0s. What is the fix?