Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the $ lock error
Excel & Google SheetsWhat $ lock means
By default, cell references are relative — they adjust when copied. A '$' before the column letter locks the column; a '$' before the row number locks the row. If a reference changes unexpectedly when you copy a formula, the lock is missing or placed incorrectly.
Common causes
- The '$' is placed only in front of the column letter (e.g., $A1) when both row and column need to be fixed — the row still adjusts on fill.
- The '$' is placed only in front of the row number (e.g., A$1) when filling across columns, but the column still shifts.
- No '$' signs are present at all (a fully relative reference A1), so both row and column shift when the formula is copied in any direction.
- The formula was created by pointing at a cell with the mouse, and the reference was not toggled to absolute with F4 before pressing Enter.
- After inserting or deleting rows/columns, a previously correct absolute reference points at the wrong cell because the row/column numbers shifted.
Example fix
Broken
=B2*C1Fixed
=B2*$C$1C1 holds a tax rate that must not change when the formula is copied. Adding '$C$1' locks both the column and the row so every copied cell multiplies by the same rate in C1.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Click the cell containing the formula. In the formula bar, click inside the reference you want to fix. Press F4 repeatedly to cycle through all four lock states: A1 → $A$1 → A$1 → $A1 → back to A1.
- 2To lock both row and column (most common need for a fixed lookup table), ensure the reference reads $A$1 — a '$' before both the letter and the number.
- 3For a reference that must lock the column but slide the row (e.g., locking a column of tax rates): use $A1 — '$' before the letter only.
- 4For a reference that must lock the row but slide the column (e.g., locking a header row): use A$1 — '$' before the number only.
- 5After fixing, copy the formula down and to the side, then click a cell at the edge of the fill range and verify the reference in the formula bar is exactly what you expect.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every $ lock and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
What does F4 do in Google Sheets?
In Google Sheets, F4 cycles through the same four reference types as in Excel: relative → absolute → row-absolute → column-absolute. Make sure you click inside the reference text in the formula bar before pressing F4.
I locked a reference with $A$1 but the value still changes after I insert a new row above it — why?
Inserting rows shifts cell contents down, so $A$1 now references whatever moved into row 1. Absolute references fix the address, not the cell content. To reference a value that must never move, put it in a row that will not be affected by insertions, or use a named range.
Can I apply absolute references to an entire range like A1:A10 at once?
Yes. Click inside A1:A10 in the formula bar and press F4. Both anchors of the range become locked: $A$1:$A$10.