Reference errors and troubleshooting in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula with a deleted reference?

You have a formula =SUM(A1:A5) in cell B1. If rows 2 to 4 are deleted, what will be the new formula result assuming A1=2, A5=8?

A 2

B #REF!

C 8

D 10

💡 Hint

Deleting rows inside a range adjusts the range automatically.

When rows 2 to 4 are deleted, the range A1:A5 adjusts to A1:A2 (which is A1 and A5 shifted up). The sum is 2 + 8 = 10.

📊 Formula Result

intermediate

What error does this formula produce?

Given cell A1 contains 5, what error will the formula =A1 + INDIRECT("B" & 10) produce if cell B10 is empty?

A 5

B #REF!

C #VALUE!

D 0

💡 Hint

Empty cells are treated as zero in arithmetic operations.

INDIRECT("B10") refers to cell B10 which is empty, treated as zero. So 5 + 0 = 5.

❓ Function Choice

advanced

Which formula avoids #REF! error when deleting rows?

You want to sum values in column A from row 1 to row 10, but rows might be deleted. Which formula will NOT cause a #REF! error if rows are deleted?

A =SUM(A1:A10)

B =SUM(OFFSET(A1,0,0,10,1))

C =SUM(INDEX(A:A,1):INDEX(A:A,10))

D =SUM(INDIRECT("A1:A10"))

💡 Hint

Using INDEX with column references is more stable than direct ranges or INDIRECT.

Using INDEX to define the range keeps the reference valid even if rows are deleted, avoiding #REF! errors.

🎯 Scenario

advanced

Troubleshoot #REF! error after moving a sheet

You have a formula =Sheet2!B2 + Sheet3!C3 in Sheet1. After renaming Sheet3 to Data, the formula shows #REF! error. What is the best fix?

A Re-enter formula as =Sheet2!B2 + Sheet3!C3

B Change formula to =Sheet2!B2 + Data!C3

C Delete the formula and retype it without sheet names

D Use INDIRECT("Sheet3!C3") in the formula

💡 Hint

Sheet names in formulas must match exactly after renaming.

Renaming Sheet3 to Data breaks the reference. Updating the formula to use the new sheet name fixes the #REF! error.

❓ data_analysis

expert

Identify the cause of #REF! in a complex formula

Given the formula =SUM(A1:A5) + INDEX(B1:B5, 10), what causes the #REF! error?

A INDEX is referencing a row outside the range

B Cells A1:A5 contain errors

C Addition operator is missing

D SUM range is invalid

💡 Hint

Check the row number used in INDEX relative to the range size.

INDEX(B1:B5, 10) tries to get the 10th item, but the range only has 5 rows, causing #REF! error.