Excel formulas normally adjust when copied. That is helpful until one part of the formula should always point to the same tax rate, lookup table, exchange rate, or fixed total. Dollar signs tell Excel which part of a reference must stay put.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web when copied formulas change a reference that should remain fixed.
Lock a cell reference with dollar signs
Use this when a formula works once but breaks after being copied down or across.
- Edit the formula cell.
- Click inside the reference that should not move, such as B1 in =A2*B1.
- Press F4 to change it to $B$1.
- Press Enter.
- Copy or fill the formula to other cells.
The $B$1 reference stays fixed while relative references without dollar signs continue to move normally.
Use mixed references
Mixed references lock only the row or only the column.
- Edit the formula and click the reference.
- Press F4 repeatedly to cycle through B1, $B$1, B$1, and $B1.
- Use B$1 when the row should stay fixed.
- Use $B1 when the column should stay fixed.
- Press Enter and copy the formula.
Mixed references are common in grids, multiplication tables, and models copied both sideways and downward.
Use a named range instead
A name can make formulas clearer than dollar signs.
- Select the fixed value cell.
- Type a name such as TaxRate in the Name Box beside the formula bar.
- Press Enter.
- Use that name in formulas, such as =A2*TaxRate.
More control
Read the dollar signs
The dollar sign locks the part immediately after it. $B$1 locks column B and row 1. $B1 locks only column B. B$1 locks only row 1.
Lock lookup table ranges
Lookup formulas usually need the table range locked, such as $A$2:$D$100, while the lookup value stays relative so it changes row by row.
Repair copied formulas
Correct the first formula with the needed dollar signs, then copy it back over the other formulas.
Skip them when nothing is copied
Absolute references only matter when a formula will be duplicated elsewhere. In a single formula that never moves, they do not change the result.
Sources
- Microsoft Support – Switch between relative, absolute, and mixed references (2025)
- Microsoft Support – Overview of formulas in Excel (2025)
- Microsoft Support – Create or change a cell reference (2025)