Locking cells with $
8 minutes · 4 steps · free, no sign-up
The most common beginner formula bug is a reference that slides when you copy it. You will cause the bug on purpose, see why it happens and fix it with the dollar sign.
- Tell relative and absolute references apart
- Lock a cell with $
- Fix a broken fill-down
- Change one input and update many results
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceReference types
B2 relative: moves in both directions
$E$1 absolute: never moves
$E1 column locked, row moves
E$1 row locked, column movesThe key ideasSummary: Locking cells with $
By default a reference is relative: copy the formula one row down and every reference shifts one row down too. That is great for B2, B3, B4, but wrong for a single cell such as a tax rate that every row should share.
Putting a $ in front of the column letter, the row number or both locks that part. $E$1 always points at E1 wherever the formula is copied. Press F4 in the real Google Sheets to add the dollar signs quickly.
Watch out forCommon mistakes
- Locking only half of it.
$E1locks the column but lets the row move, andE$1does the reverse. These are useful in tables that fill both across and down, but a wrong choice looks just like the bug in this lesson. - Adding $ everywhere out of caution. Locked references cannot adapt when you copy a formula, so only lock the cells that should stay put, such as a single rate or a lookup table.
- Expecting cut and paste to behave like copy. If you move a cell with cut and paste, formulas that pointed at it follow it to the new place. Copy and paste shifts references instead.
Next stepTry it in the real Google Sheets
While you are editing a formula in Google Sheets, click on a reference such as E1 and press F4. It cycles through $E$1, E$1, $E1 and back to E1, so you never have to type the dollar signs by hand. On some laptops you need to hold the Fn key as well.