This is the concept that separates Excel beginners from confident users. Master the $ sign and copying formulas becomes effortless.
A worksheet showing a formula filled across many rows
When you copy a formula, relative references shift with it.
C2: =A2*B2 ← copy down → C3: =A3*B3 C4: =A4*B4
The row numbers adjusted automatically. This is usually what you want.
Sometimes one cell must stay fixed — like a tax rate in a single cell. Lock it with $:
1=B2 * $E$1 ← $E$1 never moves, even when copied| Item | Price | Tax (=Price*$E$1) | E1 = 0.08
| Pen | 10 | =B2*$E$1 → 0.80 |
| Pad | 25 | =B3*$E$1 → 2.00 | $E$1 stayed locked
| Type | Syntax | Behavior when copied |
|---|---|---|
| Relative | A1 | Column and row shift |
| Absolute | $A$1 | Nothing shifts |
| Mixed (lock col) | $A1 | Row shifts, column locked |
| Mixed (lock row) | A$1 | Column shifts, row locked |
While editing a reference, press F4 to cycle the $ signs:
A1 → $A$1 → A$1 → $A1 → A1 …
No need to type dollar signs by hand.
A multiplication table is the classic mixed-reference example. With row headers in column A and column headers in row 1:
1=$A2 * B$1$A2 locks the column (always read from column A) but lets the row move.B$1 locks the row (always read from row 1) but lets the column move.Copy that one formula across the whole grid and the entire times table fills in correctly.
Ask yourself: "When I copy this, what should stay put?" Lock that part with $. That single question solves 90% of reference problems.