How to lock a cell reference (absolute vs relative, the $ sign)
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Stop a reference from shifting when you fill a formula down or across — the dollar-sign rule.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 50 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.