Cell references in Excel: relative, absolute ($) and mixed
Every formula that points at a cell, like =B2*C2, uses a cell reference. The interesting part starts when you copy that formula to other cells: Excel adjusts the references, which is usually exactly what you want, and sometimes exactly what breaks your sheet.
The $ sign is how you control it. This guide shows the three kinds of references, with small examples of what happens when you copy a formula down or across.
Relative references: they move with the formula
A reference without $ signs, like B2, is relative. Excel remembers it as "the cell one to the left, two to the left…" rather than a fixed address. Copy the formula one row down and every reference moves one row down too.
Example: Line totals, copied down
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Total |
| 2 | Pen | 2 | 10 | |
| 3 | Notebook | 5 | 3 | 15 |
| 4 | Bag | 30 | 1 |
Written in D2 as =B2*C2 and copied down. In row 3 it became =B3*C3 by itself: 5 × 3 = 15.
Absolute references: $ locks them in place
Sometimes every row must use the same cell, such as a tax rate or an exchange rate. Put a $ before the column letter and the row number, like $F$1, and the reference stays fixed wherever you copy the formula.
Example: Every row uses the same tax rate
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Price | Tax | Tax rate | 0.2 | |
| 2 | Pen | 2 | ||||
| 3 | Notebook | 5 | 1 | |||
| 4 | Bag | 30 |
Written in C2 as =B2*$F$1 and copied down. B moved to B3, but $F$1 stayed put: 5 × 20% = 1.
What goes wrong without the $
This is the most common copying mistake in Excel. The formula is right in the first row, but every row below quietly points at the wrong cell.
Example: The tax rate reference slipped
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Price | Tax | Tax rate | 0.2 | |
| 2 | Pen | 2 | ||||
| 3 | Notebook | 5 | 0 | |||
| 4 | Bag | 30 |
Written in C2 as =B2*F1 without $ and copied down. In row 3 it points at F2, which is empty, so the tax is 0. Nothing shows an error, which is why this mistake is so easy to miss.
Mixed references: lock only the row or only the column
$A1 locks the column but lets the row move. A$1 locks the row but lets the column move. You need this when one formula is copied both down and across, for example in a table that combines a list of amounts with a row of rates.
Example: One formula for a whole table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rate → | 1 | 2 | 3 |
| 2 | 10 | |||
| 3 | 20 | |||
| 4 | 30 | 90 |
Written in B2 as =$A2*B$1 and copied across and down. $A always uses the amounts in column A; $1 always uses the rates in row 1. In D4: 30 × 3 = 90.
The F4 shortcut
You rarely type the $ signs yourself. While editing a formula, click on a reference (or right after it) and press F4. Each press cycles through the four versions: F1 → $F$1 → F$1 → $F1 → F1. On a Mac, use Cmd+T.
Common mistakes
Forgetting the $ on a fixed cell. The first row is right, the rows below use empty or wrong cells.
Fix: Lock single settings cells (rates, limits, dates) with $:
=B2*$F$1.Locking everything.
=$B$2*$C$2copied down repeats the first row's result in every row.Fix: Lock only what must stay fixed; let the rest move.
Lookup tables that shift. A VLOOKUP copied down searches A3:D6, then A4:D7…, and starts returning #N/A.
Fix: Lock the table:
=VLOOKUP(G2,$A$2:$D$5,3,FALSE).
Frequently asked questions
What does the $ sign mean in Excel?
It locks the part of the reference it stands in front of: $A locks the column, $1 locks the row, $A$1 locks both. Locked parts don't change when the formula is copied.
Does the $ change the result of the formula?
No. =B2*F1 and =B2*$F$1 give the same result in the cell where you type them. The $ only matters when you copy or fill the formula.
Do references change when I move (cut) a formula?
No. Cutting and pasting keeps the references exactly as they are. Only copying adjusts them.
Related functions
- SUMSUM adds up numbers, cells and whole ranges in one formula.
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.