Confident Cells

Absolute references: what the $ does, and the F4 key

20 November 2026 · 2 min read

A $ in a cell reference, as in $F$1, stops that reference changing when you copy the formula. Use it whenever every row needs to point at the same cell, such as a rate, a target or a lookup table.

A worked example

Each salesperson earns 10% commission. The rate is typed once, in F1.

The formula in C2 is =B2*F1, which gives £200. Copy it down to C3 and it changes to =B3*F2. Excel moved both references down a row, because that's what it does when you copy. B3 is right (Tom's sales), but F2 is empty, so Tom gets £0.

ABCDEF
1NameSalesCommissionRate10%
2Grace£2,000.00£200.00
3Tom£1,500.00£0.00
4Priya£1,800.00£0.00
C2 holds =B2*F1. Copied down, C3 becomes =B3*F2 and C4 becomes =B4*F3, which point at empty cells.

The fix: lock F1 with $

=B2*$F$1

The $ before F stops the column changing, and the $ before 1 stops the row changing. Copy this down and C3 becomes =B3*$F$1: the sales move down a row, the rate stays put.

ABCDEF
1NameSalesCommissionRate10%
2Grace£2,000.00£200.00
3Tom£1,500.00£150.00
4Priya£1,800.00£180.00
C2 holds =B2*$F$1, copied down to C4.

The F4 key

You don't need to type the dollar signs. Click on the reference in the formula bar (or just after it) and press F4. Each press moves to the next kind:

Press F4ReferenceWhen copied, it
once$F$1never changes
twiceF$1keeps the row, so it can move across
three times$F1keeps the column, so it can move down
four timesF1moves both ways (back to the start)

On a Mac, use Cmd+T in Excel, or Fn+F4 on some keyboards. F4 also works in Google Sheets.

When to lock, and when not to

Common mistakes

Typing the number instead

=B2*10% works today, but when the rate changes you have to edit every formula. Keep it in one cell and lock it.

Locking only half

=B2*$F1 keeps the column but lets the row move, so copying down still slides off to F2. For a single cell that everyone shares, lock both.

Locking the wrong reference

=$B$2*F1 locks the sales instead of the rate, so every row uses Grace's sales.

Try it now

Your turn

The commission rate is in F1. In C2, work out each person's commission (sales × rate). Your formula will be copied down to C7, so lock the rate.

ABCDEF
1NameSalesCommissionRate8%
2Grace£2,000.00
3Tom£1,500.00
4Priya£1,800.00
5Marek£2,450.00
6Nia£960.00
7Ollie£3,100.00