Lock a Cell Reference with the Dollar Sign

Excel & Google Sheets

How it works

References are relative by default: copy =B2*E1 down one row and it silently becomes =B3*E2 — B3 is what you want, but E2 is an empty cell, so the result is 0. A $ freezes whatever it sits in front of: $E$1 locks both the column and the row, so the copied formulas read =B3*$E$1, =B4*$E$1 — the sales figure moves, the rate stays pinned. That mix of one moving reference and one locked reference is the pattern behind nearly every "rate times each row" calculation. The shortcut: click the reference in the formula bar and press F4 (Excel and Sheets on Windows, Cmd+T or fn+F4 on Mac) to cycle through $E$1 → E$1 → $E1 → E1.

B2

Relative — shifts to B3, B4 as you copy down. That's what you want for the per-row value.

$E$1

Absolute — the $ before the column AND the row pins it to E1 on every copy.

E$1 or $E1

Mixed — locks only the row or only the column. Used when filling both across and down.

When to use it

Use it any time one cell feeds many formulas: a tax rate, a commission percentage, an exchange rate, a grand total you're dividing by — set it once at the top and lock every reference to it.

Common mistakes

• ✕Copying =B2*E1 down without the $.

  ✓Row 3 becomes =B3*E2, multiplying by an empty cell — every row after the first shows 0. Lock the rate: =B2*$E$1.

• ✕Locking only half: E$1 when you also fill across.

  ✓E$1 pins the row but the column still slides — filling right turns it into F$1. If the constant must never move in any direction, use both dollars: $E$1.

• ✕Locking the wrong reference.

  ✓=$B$2*E1 freezes the sales figure and lets the rate wander — every row repeats row 2's answer. The $ belongs on the shared constant, not the per-row value.