Excel guide · 3 min read

Absolute vs relative cell references in Excel

A relative reference like B2 shifts when you copy a formula; an absolute reference like $B$2 always points to the same cell. Add $ before the column, the row, or both to lock them. Press F4 while editing a reference to cycle through B2, $B$2, B$2 and $B2.

Sample data

Every formula on this page was calculated live against this sheet.

	A	B	C	E	G	H	I	J
1	Item	Price	Qty	Tax rate	List price	0.05	0.1	0.15
2	Notebook	3.5	40	0.0825	100
3	Gel pens	1.25	120		250
4	Binder	6.8	15		40
5	Sticky notes	2.1	60

Relative references: the default

Type =B2*C2 in D2 and copy it down. In D3 it becomes =B3*C3, in D4 =B4*C4. Excel stores the reference as "two columns left, same row", so it moves with the formula. That is what you want for row-by-row math.

fx=B2*C2

Result: 140

Notebook: 3.50 times 40 gives a line total of 140.

The problem: copying a formula that uses one fixed cell

Every row needs the same tax rate from E2. If you write =B2*C2*(1+E2) and copy it down, row 3 becomes =B3*C3*(1+E3). E3 is empty, so no tax is added and nothing warns you. The result is wrong, not an error.

fx=B3*C3*(1+E3)

Result: 150

Gel pens show 150, the pre-tax total, because the copied formula points at empty E3 instead of the tax rate.

The fix: an absolute reference with $

$E$2 locks both the column (E) and the row (2). Copy the formula anywhere and it still points to the tax rate. The price and quantity stay relative so they still follow each row.

fx=B3*C3*(1+$E$2)

Result: 162.375

Now the gel pens include 8.25% tax: 150 times 1.0825 is 162.38.

Mixed references: lock only the row or only the column

Sometimes you copy a formula both down and across. In this discount grid, list prices run down column G and discount rates run across row 1. Each cell needs "the price in column G of my row" and "the rate in row 1 of my column".

$G2 locks the column but lets the row change. H$1 locks the row but lets the column change. Enter it once in H2, copy it across to J and down to row 4, and every cell is right.

fx=$G2*(1-H$1)

Result: 95

A 100 list price with the 5% discount gives 95. Copied to I3 it becomes =$G3*(1-I$1): 250 less 10% is 225.

The F4 shortcut

You rarely need to type $ yourself. Click in a reference while editing a formula and press F4 to cycle through the four types. On many laptops you need Fn+F4. On a Mac, press Cmd+T.

Double-click the formula cell, or press F2, to edit it.
Click inside the reference you want to lock, for example E2.
Press F4 once for $E$2, twice for E$2, three times for $E2, four times to go back to E2.
Press Enter, then copy the formula.

Common mistakes

Locking everything with $

If $B$2*$C$2 is copied down, every row shows the first row's total. Only lock the cells that should stay put.

Forgetting $ on lookup ranges

A VLOOKUP table of A2:C20 shifts to A3:C21 as you copy down and starts missing rows. Use $A$2:$C$20 or a table.

Assuming moving a cell breaks absolute references

Cutting and pasting the tax cell, or inserting rows above it, updates $E$2 automatically. Absolute only means it does not change when you copy the formula.

FAQ

What does the $ mean in an Excel formula?

It locks the part of the reference that follows it. $A1 locks column A, A$1 locks row 1, and $A$1 locks both, so copying the formula does not change that part.

What is the shortcut for absolute references?

Press F4 while editing the reference on Windows (Fn+F4 on some laptops). On a Mac, press Cmd+T.

Is there an alternative to $ references?

Yes. Give the cell a name in the Name Box, such as TaxRate, and use =B2*C2*(1+TaxRate). Named cells are always absolute and easier to read.

Functions used

Keep learning

Updated 2026-10-31