Basics
Locking a cell with dollar signs
Definition
An absolute reference such as $A$1 keeps pointing at the same cell no matter where the formula is copied or filled.
Syntax
=B2*$E$1
Arguments
| Argument | Meaning |
|---|---|
B2 |
A relative reference that moves with the formula. |
$E$1 |
An absolute reference. The dollar signs lock both the column and the row. |
F4 |
A key that cycles a typed reference through $E$1, E$1, $E1, and E1. |
Worked example
Cell E1 holds a tax rate of 0.06. The prices are in B2:B4, and the tax formula starts in C2.
| A: Item | B: Price |
|---|---|
| Lamp | 44 |
| Rug | 125 |
| Vase | 30 |
Formula: =B2*$E$1
Result: 2.64. Filled down, C3 becomes =B3*$E$1 and shows 7.5, and C4 shows 1.8, because $E$1 never moves.
Practice
Cell H1 holds the conversion factor 2.5, and cells A2:A4 hold 14, 22, and 40. Write a formula for B2 that can be filled down, then give the result in B4.
- In B2, write a formula that multiplies A2 by the factor in H1 and can be filled down.
- Fill that formula down through B4.
Related functions
Ranges can be locked too, so a total such as SUM($B$2:$B$4) keeps the same cells when you copy it elsewhere.
Common mistake
Locking only the row, as in E$1: the row stays put but the column still shifts when the formula is copied to the right, so it ends up pointing at F1.