Data cleanup
Absolute vs Relative References in Excel (with F4 Tips)
Whether a formula keeps working when you copy it across a sheet comes down to one thing: cell references. A relative reference like A1 shifts as you fill the formula, while an absolute reference like $A$1 stays locked on the same cell. Getting this right is the difference between a formula that fills perfectly and one that returns garbage.
The dollar sign ($) is the control. Put it before the column letter, the row number, or both, and you decide exactly what stays fixed when the formula moves. This guide explains relative, absolute, and mixed references, and shows how the F4 key cycles through them so you never have to type the dollar signs by hand.
Cell references syntax
=$A$1 (absolute) | =A1 (relative) | =$A1 / =A$1 (mixed) | Argument | Required | What it does |
|---|---|---|
A1 | Required | Relative reference: both the column and row shift when the formula is copied to another cell. |
$A$1 | Required | Absolute reference: the $ before the column AND the row locks both, so the reference never moves when copied. |
$A1 | Optional | Mixed reference: the $ locks only the column (A stays fixed), while the row number is free to shift. |
A$1 | Optional | Mixed reference: the $ locks only the row (1 stays fixed), while the column letter is free to shift. |
Note: Press F4 while editing a reference to cycle A1 to $A$1 to A$1 to $A1 and back. On a Mac, F4 works in most versions; if not, use Cmd + T.
Examples
Real, copy-paste-ready formulas.
1. Relative reference fills naturally
=A2+B2 With no dollar signs, copying this down to row 3 becomes =A3+B3 automatically. Relative references are what you want when every row should use its own data.
2. Absolute reference locks a constant
=B2*$E$1 The $E$1 stays fixed no matter where you copy the formula, so every row multiplies by the single tax rate in E1. Without the dollar signs, the reference would drift to E2, E3, and break.
3. Mixed reference for a multiplication table
=$A2*B$1 Locking only the column ($A2) and only the row (B$1) lets one formula fill an entire grid correctly: the column stays anchored to A, the row stays anchored to 1, and the free parts move with the cell.
How to write Cell references step by step
- 1
Type your formula normally, for example =B2*E1, before deciding what to lock.
- 2
Click on the reference inside the formula that should stay fixed (place the cursor in or next to it).
- 3
Press F4 once to make it fully absolute ($E$1); the dollar signs appear before both the column and the row.
- 4
Press F4 again to cycle to row-only locked (E$1), a third time for column-only ($E1), and a fourth time to return to relative (E1).
- 5
Press Enter to commit, then copy or fill the formula across your range and check that locked references stayed put.
- 6
If a filled formula returns wrong results, click a copied cell and inspect its references: a constant that drifted needs dollar signs added.
Frequently asked questions
What does the dollar sign do in an Excel formula?
The dollar sign locks part of a reference so it does not change when the formula is copied. $ before the column letter locks the column, $ before the row number locks the row, and $A$1 locks both.
What is the F4 shortcut for in Excel?
While editing a formula, F4 cycles the selected reference through the four states: relative (A1), fully absolute ($A$1), row-locked (A$1), and column-locked ($A1). It saves you from typing dollar signs by hand.
When should I use an absolute reference?
Use an absolute reference when a formula must always point to the same cell as you fill it across a range, such as a single tax rate, exchange rate, or total that every row needs to reference.
What is a mixed reference?
A mixed reference locks only the row or only the column, like A$1 or $A1. It is essential for grids such as multiplication tables, where you fill one formula across rows and columns and need each axis anchored separately.