Relative and absolute references ($A$1)

In this lesson you will learn

Explain how a relative reference changes when copied
Create absolute and mixed references with $
Switch reference types quickly with F4

Aysel works in an electronics shop and has to add 18% VAT to the price of every product. She types the VAT rate into one cell, writes the first formula, drags it down… and suddenly sees zeros in the other rows. The reason lies in how Excel copies references.

Relative references

A plain reference like B4 is relative. Excel remembers it not as a fixed address but as a position: “two columns to the left of the formula cell”. So when you copy =B4*C4 one row down, it becomes =B5*C5. Usually that is exactly what we want — each row is calculated with its own data.

Absolute references: the `$` sign

Aysel has typed the VAT rate into B1 and written =B4*B1 in C4. Copied down, C5 gets =B5*B2: B2 is empty, so the result is 0. The rate is always in B1, so this reference must not change. The fix: =B4*$B$1. The $ sign “locks” the column letter and the row number.

Definition

Absolute reference

A reference with $ before both the column letter and the row number, such as $B$1. Wherever the formula is copied, it keeps pointing to the same cell.

Reference	Type	When copied
`A1`	relative	both column and row change
`$A$1`	absolute	nothing changes
`A$1`	mixed	row is fixed, column changes
`$A1`	mixed	column is fixed, row changes

While editing a formula with the cursor on a reference, cycles its type: A1 → $A$1 → A$1 → $A1 → A1. On a Mac: Cmd+T.F4

Interactive

Loading simulation…

Calculating VAT: the rate is in one cell, and the formulas refer to it absolutely.

Mixed references: a multiplication table

One formula for the whole table

Cells B1:J1 contain the numbers 1 to 9, and so do cells A2:A10. Write a formula in B2 that produces the multiplication table when copied to the whole range B2:J10.

Show solution

The first factor is always in column A → lock the column: $A2.
The second factor is always in row 1 → lock the row: B$1.
Formula: =$A2*B$1.
Copied to E7, for example, it becomes =$A7*E$1, i.e. 6 × 4 = 24.

Key points

A relative reference (A1) “moves” along with the formula.
An absolute reference ($A$1) always points to the same cell.
Mixed references (A$1, $A1) lock only the row or only the column.
F4 cycles through the reference types (Cmd+T on a Mac).
Keep fixed values such as rates in one cell and refer to it with $.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

You copy =A1+B1 from C1 to C2. What formula will C2 contain?