Excel · Lesson 3 of 8

Relative & absolute references

Why formulas change when copied, and how $ locks a cell.

When you copy a formula, Excel adjusts its cell references — that's a relative reference (B2 becomes B3 one row down).

Sometimes a reference must stay fixed, such as a tax rate in one cell. Add $ to lock it:

$F$1 — column and row locked (absolute).
$F1 — column locked, row moves (mixed).
F$1 — row locked, column moves (mixed).

Press F4 while editing a reference to cycle through these forms. A cleaner option is to name the cell (Formulas → Define Name), e.g. GST, and write =B2*GST.

Sample sheet & formulas

Type these in Excel or Google Sheets

     A           B          C            ...   F
1    Service     Amount     With GST           18%   (GST rate in F1)
2    Website     10000
3    Logo        2500
4    Hosting     3000

C2:  =B2*(1+$F$1)      then fill down to C4
     → C3 becomes =B3*(1+$F$1)
     → C4 becomes =B4*(1+$F$1)

Without $ (wrong):  C2 =B2*(1+F1)  → copied to C3 = B3*(1+F2)  (F2 is empty!)

Results

C2 = 11800
C3 = 2950
C4 = 3540

Works in Microsoft Excel 365/2021 and, for almost every formula, in Google Sheets. Data is fictional.

Key points

Relative references shift when copied.
$ locks the column, the row, or both.
F4 toggles reference types.

Quick check

Which reference stays exactly the same when copied anywhere?

F1 $F1 F$1 $F$1

Practice task

Build a multiplication table 1–10 × 1–10 with a single formula in B2 using mixed references, filled right and down.

Interview corner

When would you use a mixed reference?

When copying in two directions — e.g. a rate table where the formula must always read row 1 for rates (B$1) and column A for quantities ($A2).

What are named ranges good for?

They make formulas readable (=Amount*GST), reduce $ mistakes and can be reused across sheets.