How to lock a cell reference (absolute vs relative, the $ sign)
✓ Verified in LibreOffice 25.8.7.3Stop a reference from shifting when you fill a formula down or across — the dollar-sign rule.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =A2*$B$1 | $ locks what follows. $B$1 = fully locked; B$1 = row locked; $B1 = column locked. F4 cycles them. |
| Google Sheets | =A2*$B$1 | Identical; F4 also cycles the $ modes. |
| LibreOffice Calc | =A2*$B$1 | Identical; Shift+F4 cycles the modes. |
How it works
A plain reference like A2 is RELATIVE — fill the formula down and it becomes A3, A4, shifting with the row. A $ freezes that part: $B$1 always points at B1 no matter where you copy it (5 x 10 = 50 here, and every filled row keeps multiplying by B1). The dollar attaches to whatever follows it, so you can lock just the column ($B1), just the row (B$1), or both ($B$1) — the mixed forms are the trick behind multiplication tables and single-rate calculations. Press F4 (Shift+F4 in LibreOffice) on a highlighted reference to cycle through the four modes.
Verified, not just documented
We ran =A2*$B$1 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 50 — exactly the expected result. Every formula here is confirmed by actually executing it.