Relative and absolute cell references

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. You copy the formula =B2*B1 from C2 down to C3. What does the copied formula in C3 become?

A =B2*B1

B =B3*B2

2. Which reference stays fixed on cell B1 when a formula is copied anywhere?

A $B$1

B B1

3. A GST rate is in cell B1. Which formula is best for A4 so it can be copied down a column of prices?

A =A4*$B$1

B =A4*B1

4. In the reference B$1, what does the dollar sign lock?

A Only the column (column B)

B Only the row (row 1)

5. You copy =A1+B1 from C1 across to D1. What does the formula in D1 become?

A =B1+C1

B =A1+B1

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1=B2*$B$1		A$B$1
2B$1		BOnly the column is locked, so the row can still change. To lock the row of a cell in row 1, use B$1 or $B$1.
3A relative reference with no dollar signs changes to match its new position when the formula is copied one row down		C=A2+B2
4An absolute reference locks both the column and the row so the cell never changes when the formula is copied		DB2 is relative and shifts when copied down, but $B$1 stays locked on the same cell, such as a fixed tax rate
5=C2*B1 (B1 holds the tax rate, copied down)		EB1 is relative, so when copied down it shifts to B2, B3 and so on, and the formula stops using the tax rate. It should be $B$1.
6=C2*$B1 (copied across to column D)		FA mixed reference that locks the row only, so the row stays 1 but the column can change when copied across

Definitions

$B$1

Fill in the Blanks

Complete each line of code by filling in the missing parts.

=A5*____

=A2*B_1

D2: =B2*C2 D3: =B3*__

C2: =B2*$F$1 C3: =B3*____

B2: =___*B$1

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A E1: 0.1

B C2: =B2*$E$1

C C3: =B3*$E$1

D C4: =B4*E1

Question 2

A C2: =$B$2*$E$1

B C3: =B3*$E$1

C C4: =B4*$E$1

Question 3

A Row 1: 1,2,3 in B1:D1

B Column A: 1,2,3 in A2:A4

C B2: =A$2*B$1

D Copy B2 across and down

Question 4

A C2: =B2/$B$10

B C3: =B3/B10

C C4: =B4/$B$10

Question 5

A Prices A2:A4, rates B1:D1

B B2: =$A2*(1-B1)

C Copy B2 across and down

Answer Key

Relative and absolute cell references

Section A: Quick Questions

B — =B3*B2

A — $B$1

A — =A4*$B$1

B — Only the row (row 1)

A — =B1+C1

Section B: Match the Pairs

=B2*$B$1 → B2 is relative and shifts when copied down, but $B$1 stays locked on the same cell, such as a fixed tax rate

B$1 → A mixed reference that locks the row only, so the row stays 1 but the column can change when copied across

A relative reference with no dollar signs changes to match its new position when the formula is copied one row down → =A2+B2

An absolute reference locks both the column and the row so the cell never changes when the formula is copied → $B$1

=C2*B1 (B1 holds the tax rate, copied down) → B1 is relative, so when copied down it shifts to B2, B3 and so on, and the formula stops using the tax rate. It should be $B$1.

=C2*$B1 (copied across to column D) → Only the column is locked, so the row can still change. To lock the row of a cell in row 1, use B$1 or $B$1.

Section C: Definitions

$B$1 — The dollar signs lock a cell reference, so $B$1 stays fixed when a formula is copied. A plain B1 is relative and shifts to match its new position. B$1 locks only the row.

Section D: Fill in the Blanks

=A5*$B$1

=A2*B$1

D2: =B2*C2
D3: =B3*C3

C2: =B2*$F$1
C3: =B3*$F$1

B2: =$A2*B$1

Section E: Spot the Error

D — E1 is relative, so when the formula is copied down it shifts to E2, E3 and so on. The rate must be locked as $E$1 so every row uses the same cell.

A — $B$2 is locked, so every copy would multiply the first price. The price B2 should be relative so it moves to B3, B4 and so on when copied down.

C — A$2 locks the row, so copying down would keep using row 2. The column should be locked instead, as $A2, so the row changes but column A stays fixed.

B — B10 is relative, so it shifts to B11 when copied down and no longer points to the total. The total must be locked as $B$10.

B — B1 is fully relative, so copying down moves the rate to B2, B3 and so on, which are not rates. The row should be locked as B$1 so it always reads row 1.