Theory
One tax rate, a column of wrong answers
Aryan needs GST on every item: each row's price times the one tax rate sitting in cell E1 (18%). He writes =B2*E1 in the first row, gets the right answer, and copies it down.
Every row below is wrong, most show zero.
The price reference behaved perfectly, but the tax-rate reference drifted: in row 3 it became E2 (empty), in row 4 E3 (empty). This is the most important, most misunderstood idea in all of Excel formulas: when you copy a formula, references move, unless you lock them. Mastering the lock is mastering Excel.
Theory
Anchored or floating
Imagine formulas as boats. A relative reference is a floating boat: copy the formula one row down and the boat drifts one row down too (B2 becomes B3). Usually helpful, each row reads its own data. But some things must stay put: the tax rate is a lighthouse. An absolute reference ($E$1) drops an anchor so it never drifts. The `$` is the anchor chain. Aryan forgot to anchor the lighthouse, so it floated away.
At a glance
The four reference states
| Form | Locked | Behaviour when copied |
|---|---|---|
| B2 | Nothing (relative) | Both column and row shift |
| $B$2 | Both (absolute) | Never changes |
| B$2 | Row only (mixed) | Column shifts, row stays |
| $B2 | Column only (mixed) | Row shifts, column stays |
Theory
The dollar sign locks what follows it
The rule is simple: `$` locks whatever comes right after it.
$Blocks the column B.B$2locks the row 2.$B$2locks both, fully absolute.B2locks nothing, fully relative.
So Aryan's fix is =B2*$E$1: the price B2 stays relative (each row reads its own price), while $E$1 is anchored to the tax rate forever. Copy it down and every row multiplies its price by the same locked rate. Correct at last.
F4 cycles a reference through all four states, no typing dollar signs by hand.
Quiz
Aryan copies =B2*$E$1 from row 2 down to row 5. What does the formula in row 5 look like?
- =B5*$E$1, the price shifts, the locked rate stays
- =B5*$E$4, both shift
- =B2*$E$1, nothing shifts
- =B5*E5, the lock is ignored
Show the answer
=B5*$E$1, the price shifts, the locked rate stays
The relative B2 shifts with the copy to B5 (each row its own price), while $E$1 is absolute and stays locked on the tax rate. So row 5 reads =B5*$E$1. This is exactly the fix for the drift bug: lock the constant, leave the per-row value relative. Predicting how references change on copy is the most-tested Excel formula skill.
Think first
When do you need MIXED?
Aryan builds a multiplication-table grid: row headers 1-10 down column A, column headers 1-10 across row 1, and each inner cell multiplies its row header by its column header. What reference pattern lets him write ONE formula and copy it across the whole grid?
Show the answer
Mixed references: =$A2*B$1. For any inner cell, the row-header always sits in column A (lock the column: $A2, row still floats down), and the column-header always sits in row 1 (lock the row: B$1, column still floats across). One formula, copied across and down the entire grid, works everywhere. Grids are exactly where mixed references shine, and a favourite exam scenario for testing whether you truly understand the $.
Watch out
Where marks leak
The classic bug: forgetting to lock a constant (tax rate, exchange rate), so copying drifts the reference off it and returns zeros/errors. Misreading what $ locks, it locks what follows it ($B = column, B$2 = row). Not knowing F4 cycles the four states. And using absolute everywhere out of fear, then formulas that should shift do not. Predict-the-copy questions live on precisely this; work them cell by cell.
Theory
This unlocks the whole unit
Every powerful formula ahead, VLOOKUP tables, SUMIF ranges, dashboard calculations, depends on locking the right references so one formula copies correctly across hundreds of rows. Get the $ wrong and even a correct function gives wrong answers. This is the skill; practise the drift bug and its fix until it is automatic. Next: the basic aggregate functions, revisited deeper than BCA105, SUM, AVERAGE, COUNT, MAX, MIN, and their variants.
Summary
Key takeaways
- When a formula is copied, references move, unless you lock them with $.
- Relative (B2): shifts with the copy, good for per-row calculations.
- Absolute ($B$2): locked, never changes, used for a constant like a single rate cell.
- Mixed (B$2 locks the row, $B2 locks the column): locks one part, used for grids.
- $ locks what follows it; F4 cycles a reference through all four states.
- Memory hook: relative boats float, absolute drops an anchor ($); lock the lighthouse.