Excel Relative and Absolute References

When you copy a formula, Excel changes its cell references. Knowing how and when this happens is one of the most important Excel skills.

Relative References

A normal reference like A2 is relative. When you copy the formula, the reference moves by the same number of rows and columns.

If C2 contains:

=A2*B2

and you fill it down, Excel adjusts it automatically:

That is usually exactly what you want: each row uses its own values.

The Problem: One Fixed Cell

Ali wants to add 18% sales tax to every price. He puts the tax rate in one cell, E1, so he can change it later.

In C2 he writes =B2*E1 and fills it down. But C3 becomes =B3*E2 and C4 becomes =B4*E3. E2 and E3 are empty, so the tax shows as 0. The reference to the rate moved, but it should have stayed on E1.

Absolute References

Add a dollar sign $ before the column letter and the row number to lock the reference:

=B2*$E$1

Now fill it down:

B2 moves to B3 and B4, but $E$1 always stays the same. If the tax rate changes to 17%, Ali changes only E1 and every row updates.

Mixed References

Sometimes you want to lock only the row or only the column:

Example: a multiplication table

Put the numbers 1 to 5 in B1:F1 and in A2:A6. In B2 write:

=$A2*B$1

Fill it right to F2, then down to row 6. Column A stays locked for the left numbers, row 1 stays locked for the top numbers:

One formula builds the whole table.

References to Other Sheets

You can use a cell from another sheet by writing the sheet name and ! before the address:

=Prices!B2*Sales!C2

If the sheet name has spaces, wrap it in single quotes:

='October Sales'!D10

The easiest way is to type =, click the other sheet's tab, click the cell and press Enter. Excel writes the reference for you.

Named Cells

Instead of $E$1, you can give the cell a name. Select E1, click the Name Box, type TaxRate and press Enter. Then:

=B2*TaxRate

Named cells are always absolute, and formulas become easier to read.

Exercise