Absolute Reference in Excel: what the $ sign does (and F4)

When you copy a formula down, Excel moves every reference with it: A2 becomes A3, A4… Usually that is what you want. Sometimes one cell must stay put — a tax rate, an exchange rate. A $ sign locks it.

The problem

Prices in A2:A10, tax rate in B12. In B2 you type =A2*B12 and copy down. Row 3 becomes =A3*B13 — an empty cell — and every result after row 2 is 0.

The fix

=A2*$B$12

Now A2 moves (A3, A4…) but $B$12 stays on the tax rate.

F4 adds the $ for you

Click inside the reference while typing the formula and press F4. Each press cycles:

On a laptop you may need Fn+F4.

When you need the half-locks

A multiplication table: numbers 1–10 down column A, 1–10 across row 1. In B2:

=$A2*B$1

Copy it across and down. Column A stays locked for the left numbers, row 1 stays locked for the top numbers.

Other places it matters

• The lookup table in VLOOKUP: $D$2:$E$100.
• Running totals: =SUM($B$2:B2).
• RANK: the list must be locked.

Rather use a name than a $? See named ranges.