Course contents · Excel Basics

Relative and absolute references

Open the practice in the trainer →

When you copy a formula, Excel shifts its references. =B2*C1 copied one row down becomes =B3*C2. This is a relative reference.

If a reference must always point to the same cell (a tax rate, an exchange rate), lock it with $:

A1relative — both column and row change
$A$1absolute — nothing changes
A$1mixed — the row is locked
$A1mixed — the column is locked

In Excel the F4 key cycles through these four options for the reference under the cursor.

Tip from the manual (Develop Effective and Efficient Solutions): put all assumptions (rates, factors) in separate cells and reference them absolutely instead of hard-coding numbers into formulas.

Tasks in the trainer

  1. In C2:C6 calculate VAT = Price × Rate (G1). The formula must copy down correctly — use an absolute reference.
  2. In D2:D6 — the price including VAT.
  3. Test the model: change the rate in G1 to 0.1 (type 0.1 or 10%). All VAT amounts should recalculate.
  4. In E2:E6 convert the price incl. VAT to euros at the rate in G3 (absolute reference).
  5. In B8:E8 calculate column totals with one formula copied to the right.

Solve the tasks in the trainer →

Check yourself

  1. The formula =A$1*$B2 is copied from C3 to D5. What do you get?
  2. Which key toggles the reference type?