Absolute and relative references

Why formulas break when you copy them

You write a formula that works in one cell, copy it down the column, and suddenly the numbers are wrong. Almost always, the reason is how the formula refers to other cells.

Relative references

A normal reference like B2 is relative. It means "the cell one column to the left and in the same row" or whatever position B2 has compared with the formula. When you copy the formula down one row, B2 becomes B3. That is usually what you want. If C2 says =A2*B2 (quantity times price), copying it down gives =A3*B3, =A4*B4 and so on.

Absolute references

Sometimes one value must stay fixed for every row, like the VAT rate. Put 7.5% in cell F1 and write =C2*F1 in D2. Copy it down and D3 becomes =C3*F2, which points at an empty cell. The fix is an absolute reference: =C2*$F$1. The dollar signs lock the column and the row, so every copy still points at F1.

Press F4 while your cursor is on a reference to cycle through the options: $F$1, F$1, $F1 and F1.

Mixed references

$F1 locks only the column; F$1 locks only the row. Mixed references are useful in tables where one input runs across the top and another down the side, such as a price table for different quantities and different products.

A worked example

A Lagos provisions shop keeps a price list: product in column A, cost in B, and a markup percentage in cell H1. The selling price in C2 is =B2*(1+$H$1). When the owner changes the markup from 20% to 25% in H1, every selling price updates at once. Nothing needs retyping.

Habits that prevent errors

1. Put every assumption (rates, markups, exchange rates) in its own labelled cell at the top or on a separate sheet.
2. Refer to that cell with an absolute reference, never type the number into formulas.
3. After copying a formula, click two or three cells further down and read the formula bar to check it points where you expect.
4. Colour input cells one colour and formula cells another, so nobody types over a formula.

What this means for you

Most "the spreadsheet is wrong" problems are reference problems. Once you understand relative and absolute references, you can build sheets that stay correct as they grow, which is the aim of this whole course.