The guide · Absolute references
The problem
You write =B2*F1 in C2: price times tax rate. Correct. Fill it down and C3 becomes =B3*F2. The price moved down a row, which is what you wanted, but the tax rate moved too, and F2 is empty. Every row after the first shows 0.
The fix: $
A dollar sign freezes the part of the reference that follows it. $F$1 freezes both the column and the row, so it always points at F1 no matter where you copy it. Write =B2*$F$1, fill down, and every row uses the same rate.
The four versions
| Reference | When copied down | When copied right |
|---|---|---|
F1 | row changes | column changes |
$F$1 | stays F1 | stays F1 |
F$1 | stays row 1 | column changes |
$F1 | row changes | stays column F |
The mixed versions (F$1, $F1) are for grids like a multiplication table, where one side should move and the other shouldn't.
The F4 shortcut
While editing a formula, put the cursor on a reference and press F4 (on a Mac, ⌘+T in Excel). Each press cycles F1 → $F$1 → F$1 → $F1. It works in this practice sheet too.
FAQ
- What does $ mean in an Excel formula?
- It locks the column, the row, or both, so the reference doesn't change when the formula is copied. $A$1 always points at A1.
- When should I use an absolute reference?
- Whenever every row of a formula must use the same cell, such as a tax rate, exchange rate or target in one fixed cell.