Excel functions / References & tools
Absolute references ($A$1)
Lock a cell reference with $ so it stays the same when a formula is copied.
At a glance
A1 | Relative. Both the column and row move when copied. |
|---|---|
$A$1 | Absolute. Neither moves. |
$A1 | Mixed. The column stays on A; the row moves. |
A$1 | Mixed. The row stays on 1; the column moves. |
How it works
When you copy a formula, Excel adjusts its references to match the new position. Copy =B2*F2 down one row and it becomes =B3*F3. That is called a relative reference.
Sometimes one cell must stay fixed, such as a tax rate or a total. A dollar sign in front of the column letter and the row number locks it: =B2*$F$2 copies down as =B3*$F$2.
While editing a formula, put the cursor on a reference and press F4 to add the dollar signs. Press it again to cycle through the mixed versions.
Example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Price | VAT | VAT rate | ||
| 2 | Desk | $120.00 | 20% | |||
| 3 | Chair | $85.00 | $17.00 | |||
| 4 |
Result: $17.00. After copying down to row 3, the price is B3 but the rate is still locked on F2.
Tips
- Percent-of-total, commission and exchange-rate columns almost always need an absolute reference.
Practise Absolute references ($A$1)
Try it yourself in a real spreadsheet, with instant feedback on every answer.