Absolute References in Google Sheets: How to Use $ to Lock a Cell

By Gerard Fernandez · Updated · 3 min read

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.

Google Sheets: C2 shows 5.25 but C3 to C5 show 0, and column D lists the copied formulas =B2*E1, =B3*E2, =B4*E3 and =B5*E4
Real test: only the first row works. Column D (with FORMULATEXT) shows how E1 slid to E2, E3 and E4.

The fix: add $ to lock the cell

=B2*$E$1

The $ before the column letter and before the row number tells Sheets never to change them. Now every row multiplies by E1:

Google Sheets: with =B2*$E$1 copied down, the tax column shows 5.25, 8.4, 1.68 and 6.3, and column D lists =B2*$E$1 to =B5*$E$1
Real test: B changes on each row while $E$1 stays fixed, so every row is taxed at 21%.

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. In our test, typing =B2*E1 and pressing F4 once gave $E$1, twice E$1, and three times $E1:

ReferenceWhen copied…
E1Column and row both change (relative)
$E$1Nothing changes (absolute)
E$1Column changes, row stays fixed
$E1Column 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. In our test, all four versions returned 5.25.

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.