Know and understand characteristics and use of absolute and relative cell referencing

20 Spreadsheets – Cell Referencing

Learning Objective

Know and understand the characteristics and use of absolute and relative cell referencing in spreadsheet formulas.

Key Concepts

• Relative reference – changes when the formula is copied to another cell.
• Absolute reference – remains constant regardless of where the formula is copied.
• Mixed reference – a combination of absolute column and relative row (e.g., \$A1) or absolute row and relative column (e.g., A\$1).

How References Behave When Copied

Typical Uses

1. Applying the same tax rate to many amounts – tax rate stored in a single cell (e.g., \$B\$1) and referenced absolutely in each calculation.
2. Creating a multiplication table – the row header is multiplied by the column header; use mixed references to keep one header fixed while the other changes.
3. Copying a formula across a range while keeping a constant divisor or multiplier.

Example 1 – Simple Relative Reference

In cell C2 we calculate the total price:

\$C2 = A2 \times B2\$

When the formula is dragged down to C3, it automatically becomes \$C3 = A3 \times B3\$ because the references are relative.

Example 2 – Using an Absolute Reference for a Fixed Tax Rate

Assume the tax rate (5 %) is stored in cell E1. To calculate tax on each amount in column A:

\$B2 = A2 \times \$E\$1\$

Copying this formula down column B keeps the reference to E1 unchanged.

Example 3 – Mixed References in a Multiplication Table

Row headers (values 1‑5) are in A2:A6, column headers (values 1‑5) are in B1:F1. To fill the table B2:F6:

Enter in B2 the formula \$B2 = \$A2 \times B\$1\$ and copy across and down.

Explanation:

• $A2 – column absolute, row relative – keeps column A fixed while row changes.
• B$1 – column relative, row absolute – keeps row 1 fixed while column changes.

Practice Exercise

Given the following data:

Write a formula in E2 that calculates the discounted price for the value in A2. Then copy the formula across to F2 and G2 so that each column uses its own original price but the same discount rate.

Solution (not shown) should use an absolute reference to D1 (e.g., \$D\$1) and relative references to the price cells.

Summary Checklist

• Identify whether a reference should be relative, absolute, or mixed before copying a formula.
• Use $ before the column letter, row number, or both to lock that part of the reference.
• Test a formula by copying it one cell in the intended direction to verify the reference behaviour.