Absolute cell references with $

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. You copy the formula =A2*B1 down a column. Which kind of reference is B1?

A Absolute, so it always stays as B1

B Relative, so it changes to B2, B3 and so on

2. Which reference locks both the column and the row?

A $B$1

B B$1$

3. Cell B1 holds a GST rate of 10%. Which formula keeps pointing at B1 when copied down?

A =A4*$B$1

B =A4*B1

4. Which keyboard shortcut adds the $ signs to a cell reference?

A Press F1 after typing the reference

B Press F4 after typing the reference

5. You copy =C2*$E$1 from row 2 to row 3. What does the formula become?

A =C3*$E$1

B =C3*$E$2

Match the Pairs

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

Left column		Right column
1=B2*$E$1		AAn absolute cell reference that always points to cell B1, even when the formula is copied to another cell
2$B$1		BOnly the row number is locked, so the column letter changes when copied across. Both parts need locking, so it should be $B$10.
3A GST rate is stored in cell F1. Every price in column B must be multiplied by that same rate when the formula is copied down.		CF4
4The keyboard shortcut that adds $ signs to a cell reference while you are editing a formula		DThe rate cell F1 has no $ signs, so it is relative. When the formula is copied down, F1 shifts to F2 and the rate is wrong. Fix it by writing $F$1.
5=B2F1 (copied down to row 3 becomes =B3F2)		E=B2*$F$1
6=B2/B$10 copied across to column C		FMultiplies B2 by a fixed value in E1. When copied down, B2 changes to B3, B4 and so on, but $E$1 stays locked.

Definitions

$B$1

Fill in the Blanks

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

=A2*____

Total is in B8. Percent in C2: =B2/____

Select B1 and press __ to add $ signs

C2: =A2*$B$1 C3: =A3*$B$_

A2 is relative, $B$1 is ________

Spot the Error

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

Question 1

A =B2*E1

B =B3*$E$1

C =B4*$E$1

Question 2

A =C2*$F1

B =C3*$F$1

C =C4*$F$1

Question 3

A E1: 0.9 (discount)

B D2: =B2*$E$2

C D3: =B3*$E$1

Question 4

A Click D2 and type =B2*E1

B Select E1 in the formula

C Press F2 to make it $E$1

D Press Enter and copy down

Question 5

A B5: =SUM(B2:B4)

B C2: =B2/$B$5

C C3: =B3/$B$5

D C4: =B4/B5

Answer Key

Absolute cell references with $

Section A: Quick Questions

B — Relative, so it changes to B2, B3 and so on

A — $B$1

A — =A4*$B$1

B — Press F4 after typing the reference

A — =C3*$E$1

Section B: Match the Pairs

=B2*$E$1 → Multiplies B2 by a fixed value in E1. When copied down, B2 changes to B3, B4 and so on, but $E$1 stays locked.

$B$1 → An absolute cell reference that always points to cell B1, even when the formula is copied to another cell

A GST rate is stored in cell F1. Every price in column B must be multiplied by that same rate when the formula is copied down. → =B2*$F$1

The keyboard shortcut that adds $ signs to a cell reference while you are editing a formula → F4

=B2*F1 (copied down to row 3 becomes =B3*F2) → The rate cell F1 has no $ signs, so it is relative. When the formula is copied down, F1 shifts to F2 and the rate is wrong. Fix it by writing $F$1.

=B2/B$10 copied across to column C → Only the row number is locked, so the column letter changes when copied across. Both parts need locking, so it should be $B$10.

Section C: Definitions

$B$1 — An absolute cell reference. The $ before the column letter and row number locks the cell, so the reference does not change when the formula is copied. Press F4 to add the $ signs.

Section D: Fill in the Blanks

=A2*$B$1

Total is in B8. Percent in C2:
=B2/$B$8

Select B1 and press F4 to add $ signs

C2: =A2*$B$1
C3: =A3*$B$1

A2 is relative, $B$1 is absolute

Section E: Spot the Error

A — The rate cell is not locked. When the formula is copied down, E1 becomes E2, E3 and so on. It should be =B2*$E$1 so the rate stays in E1.

A — Only the column is locked. The row number is not, so F1 changes to F2 when copied down. It should be =C2*$F$1.

B — The $ signs lock the wrong cell. The discount is in E1, not E2. It should be =B2*$E$1.

C — F2 edits a cell. It does not add $ signs. Press F4 to turn E1 into $E$1.

D — The total cell B5 is not locked, so it shifts when the formula is copied. It should be =B4/$B$5 like the lines above.