Absolute references: what the $ does, and the F4 key
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.
- Grace sold £2,000. 10% of £2,000 is £200.
- Tom sold £1,500. 10% of £1,500 is £150.
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Sales | Commission | Rate | 10% | |
| 2 | Grace | £2,000.00 | £200.00 | |||
| 3 | Tom | £1,500.00 | £0.00 | |||
| 4 | Priya | £1,800.00 | £0.00 |
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Sales | Commission | Rate | 10% | |
| 2 | Grace | £2,000.00 | £200.00 | |||
| 3 | Tom | £1,500.00 | £150.00 | |||
| 4 | Priya | £1,800.00 | £180.00 |
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 F4 | Reference | When copied, it |
|---|---|---|
| once | $F$1 | never changes |
| twice | F$1 | keeps the row, so it can move across |
| three times | $F1 | keeps the column, so it can move down |
| four times | F1 | moves 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
- Lock anything every row shares: a rate, a target, a total you divide by, a lookup table.
- Don't lock references that should move with the row, like B2 for this person's sales.
- A quick test: after copying down, click a cell near the bottom and read its formula. Does every reference point where it should?
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
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Sales | Commission | Rate | 8% | |
| 2 | Grace | £2,000.00 | ||||
| 3 | Tom | £1,500.00 | ||||
| 4 | Priya | £1,800.00 | ||||
| 5 | Marek | £2,450.00 | ||||
| 6 | Nia | £960.00 | ||||
| 7 | Ollie | £3,100.00 |