Cell References in Excel: Relative vs Absolute

A cell reference is the address a formula points to, like A1 or B5. In Excel there are two kinds you must know. A relative reference (A1) shifts when you copy the formula to another cell. An absolute reference ($A$1) stays locked on the same cell no matter where you copy it. You add the dollar signs by pressing F4. This one idea is behind most "why is my formula wrong?" problems.

Why references matter

When you copy a formula down a column, Excel is helpful: it changes the references so each row uses its own data. That's usually what you want. But sometimes one value should never move, like a fixed GST rate or a total in one cell. If you don't lock it, your copied formula points to the wrong place and gives wrong answers.

Relative reference (the default)

Type =A1+B1 in C1 and copy it down to C2. Excel automatically turns it into =A2+B2. The references moved with the row. This is a relative reference, and it's the default behaviour.

Relative references are perfect when every row should calculate from its own row.

Absolute reference (locked with $)

Now suppose a discount rate sits in cell E1, and every product's discount uses it. If you write =B2*E1 and copy it down, E1 becomes E2, E3… and breaks, because there's nothing in those cells.

Lock it: =B2*$E$1. The dollar signs freeze both the column (E) and the row (1). Copy it down and it stays $E$1 every time.

The F4 shortcut

Don't type the dollar signs by hand. While editing a formula, click the reference and press F4. Each press cycles through four options:

Mixed references

A mixed reference locks only one part. A$1 locks the row but lets the column move. $A1 locks the column but lets the row move. You'll need these when you copy a formula both across and down, like in a multiplication table or a rate grid.

Worked example (GST on a price list)

Say the GST rate is in E1 = 18%. Prices are in column B.

Copy the formula in C3 down to C4 and the price reference moves (B3→B4) while the rate stays locked on $E$1. Exactly what you want.

Pro tips

• Whenever a formula uses one fixed cell (a rate, a total, a target), lock that cell with F4 before copying.
• If a copied formula shows #DIV/0! or blanks, check whether a reference that should have been locked slid away.
• On a laptop, F4 may need the Fn key: press Fn + F4.

Common mistakes

• Locking everything. Don't put dollar signs on references that should move. Lock only the cells that must stay fixed.
• Typing $ in the wrong spot. $A1 and A$1 behave very differently. Use F4 so you don't guess.
• Copying without checking. Always click one copied cell and read its formula in the Formula Bar to confirm the references moved correctly.

Key takeaways

• Relative (A1) references move when copied; absolute ($A$1) stay locked.
• Press F4 to add or cycle the dollar signs.
• Mixed references (A$1 or $A1) lock only the row or only the column.
• Lock any cell that every formula should keep pointing to.

Practice task

Put a fixed conversion rate (say ₹83 per USD) in one cell. In a column of dollar prices, write a formula that multiplies each price by that rate, using an absolute reference so you can copy it down without it breaking.

Small ideas like this are what separate a slow Excel user from a fast one. Learn them properly in the ADCA program at HCI.