Absolute References in Google Sheets: How to Use $ to Lock a Cell
When you copy a formula down a column, Google Sheets adjusts its cell references: A2 becomes A3, then A4. That's usually what you want. But when every row should use the same cell, such as a tax rate or an exchange rate, you need to lock it with a $ sign.
The problem
Prices are in column B and the tax rate is in E1. In C2 you type =B2*E1 and it works. Copy it down, and C3 becomes =B3*E2, C4 becomes =B4*E3… E2 and E3 are empty, so the results are 0.
The fix: add $ to lock the cell
=B2*$E$1The $ before the column letter and before the row number tells Sheets never to change them. Now every row multiplies by E1:
The F4 shortcut
You don't need to type the dollar signs. While editing a formula, click on a reference (or put the cursor next to it) and press F4 (on some laptops Fn + F4; on Mac ⌘ + T may also work). Each press cycles through the four options:
| Reference | When copied… |
|---|---|
E1 | Column and row both change (relative) |
$E$1 | Nothing changes (absolute) |
E$1 | Column changes, row stays fixed |
$E1 | Column stays fixed, row changes |
When to use mixed references
Mixed references (E$1 or $E1) are for formulas you copy both down and across, such as a multiplication table or a grid of prices by month. Lock the row of the header row and the column of the header column, and one formula fills the whole grid.
Lock a whole range
Ranges work the same way. In a lookup copied down a column, lock the table so it doesn't slide:
=VLOOKUP(A2, $F$2:$G$20, 2, FALSE)More in how to use VLOOKUP.
Frequently asked questions
Does $ change the result of the formula?
No. It only affects what happens when the formula is copied or filled. In the cell where you type it, E1 and $E$1 give the same result.
Is there an alternative to $?
Yes: give the cell a name with Data → Named ranges (for example TaxRate) and write =B2*TaxRate. Named ranges never shift.
Why did my references still shift after cutting and pasting?
Cutting and pasting (or moving cells) updates references to follow the moved cells, with or without $. The $ sign only controls copying.