Why reference types matter in Excel - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula referencing a cell range?

Given the values in cells A1=5, A2=10, and A3=15, what is the result of the formula =SUM(A1:A3)?

A 30

B 15

C 5

D Error

💡 Hint

SUM adds all numbers in the range.

The formula sums 5 + 10 + 15, resulting in 30.

📊 Formula Result

intermediate

What happens when you copy a formula with relative references?

If cell B1 contains =A1+10 and you copy this formula to cell B2, what will the formula in B2 be?

A =A2+10

B =A1+10

C =B1+10

D =B2+10

💡 Hint

Relative references adjust based on the new location.

Copying a relative reference formula down one row changes A1 to A2.

❓ Function Choice

advanced

Which formula keeps the reference fixed when copied?

You want to always refer to cell A1 in your formula, even when copying it to other cells. Which formula correctly does this?

A =$A1+5

B =A1+5

C =A$1+5

D =$A$1+5

💡 Hint

Use $ to fix both column and row.

The $ before column and row fixes the reference to A1 absolutely.

🎯 Scenario

advanced

What is the result when a formula references a deleted row?

If cell B1 contains =A1+10 and row 1 is deleted, what will be the formula and result in B1 after deletion?

A =A1+10 with original value

B =A2+10 with correct value

C =#REF!+10 with error

D =A1+10 with #REF! error

💡 Hint

Deleting a referenced cell breaks the reference.

Deleting row 1 removes A1, causing the formula to show #REF! error.

❓ data_analysis

expert

How many unique values are counted with mixed relative and absolute references?

You have values in A1:A5 as {2, 2, 3, 4, 4}. In B1 you enter =COUNTIF($A$1:$A$5, A1) and copy it down to B5. How many cells in B1:B5 will show the value 2?

A 3

B 2

C 1

D 0

💡 Hint

COUNTIF counts how many times the value appears in the fixed range.

The value 2 appears twice in A1:A5, so cells with 2 in column A show 2 in column B.