Why reference types matter in Excel - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to always refer to cell A1, even when copied.

Excel

=SUM([1])

Drag options to blanks, or click blank then click option'

A$A$1

BA$1

C$A1

DA1

Attempts:

3 left

💡 Hint

Common Mistakes

Using relative reference like A1 causes the cell to shift when copied.

Using only one $ locks either row or column, not both.

Using $A$1 locks both the column and row, so the reference does not change when copied.

2fill in blank

medium

Complete the formula to multiply the value in B2 by the fixed value in C1.

Excel

=B2*[1]

Drag options to blanks, or click blank then click option'

A$C$1

BC1

CC$1

D$C1

Attempts:

3 left

💡 Hint

Common Mistakes

Using relative reference C1 causes the reference to shift.

Using partial absolute references locks only row or column.

Using $C$1 keeps the reference fixed to cell C1 when copying the formula.

3fill in blank

hard

Fix the error in the formula to always sum the range A1:A5 when copied.

Excel

=SUM([1])

Drag options to blanks, or click blank then click option'

AA1:A5

B$A1:$A5

CA$1:A$5

D$A$1:$A$5

Attempts:

3 left

💡 Hint

Common Mistakes

Using relative range A1:A5 shifts the range when copied.

Partial absolute references lock only part of the range.

Using $A$1:$A$5 locks the entire range so it does not change when copied.

4fill in blank

hard

Fill both blanks to create a formula that multiplies the value in D3 by the fixed value in E2.

Excel

=D3*[1]+[2]

Drag options to blanks, or click blank then click option'

A$E$2

BE2

C$E2

DE$2

Attempts:

3 left

💡 Hint

Common Mistakes

Using relative references for both blanks causes errors when copying.

Using absolute references for both blanks may not be intended.

The first blank uses $E$2 to keep the reference fixed. The second blank uses E2 as a relative reference.

5fill in blank

hard

Fill all three blanks to create a formula that sums a fixed range and multiplies by a fixed cell.

Excel

=SUM([1])*[2]+[3]

Drag options to blanks, or click blank then click option'

A$A$1:$A$10

B$B$1

C$C$1

DA1:A10

Attempts:

3 left

💡 Hint

Common Mistakes

Using relative references causes the formula to change unexpectedly when copied.

Mixing relative and absolute references incorrectly.

The range $A$1:$A$10 is fixed for summing. The cells $B$1 and $C$1 are fixed for multiplication and addition.