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.

Practice

1. What does the #REF! error in Excel usually mean?

easy

A. The formula has a syntax error.

B. The formula contains a division by zero.

C. A formula refers to a cell that was deleted or moved.

D. The worksheet is protected and cannot be edited.

Solution

1. Step 1: Understand what #REF! means

   The #REF! error appears when a formula tries to use a cell reference that no longer exists, often because the cell was deleted or moved.

2. Step 2: Compare with other error types

   Division by zero shows #DIV/0!, syntax errors show #NAME? or similar, and protection errors do not show #REF!.

3. Final Answer:

   A formula refers to a cell that was deleted or moved. -> Option C

4. Quick Check:

   #REF! = deleted/moved cell reference [OK]

Hint: Remember #REF! means missing cell reference [OK]

Common Mistakes:

• Confusing #REF! with #DIV/0! error
• Thinking #REF! means syntax error
• Assuming #REF! means protected sheet

2. Which of these formulas will cause a #REF! error if cell B2 is deleted?

easy

A. =B2 * 2

B. =A1 + C3

C. =SUM(A1:C3)

D. =SUM(B1:B3)

Solution

1. Step 1: Identify formulas referencing B2 directly

   Only =B2 * 2 has a direct reference to B2. B does not reference B2. C and D use ranges that include B2.

2. Step 2: Understand effect of deleting B2

   Deleting B2 breaks direct references like in A, causing #REF!. Range references in C and D adjust automatically without error.

3. Final Answer:

   =B2 * 2 -> Option A

4. Quick Check:

   Direct reference to deleted cell causes #REF! [OK]

Hint: Direct cell references break when deleted, causing #REF! [OK]

Common Mistakes:

• Thinking ranges always cause #REF! when a cell is deleted
• Assuming SUM adjusts all deleted cells without error
• Ignoring direct references in formulas

3. Given the formula =A1 + B1 in cell C2, what will be the result if row 1 is deleted?

medium

A. 0

B. #REF!

C. Formula adjusts to =A0 + B0

D. #NAME?

Solution

1. Step 1: Understand what happens when deleting a row

   Deleting row 1 removes cells A1 and B1, so the formula in C2 loses its references.

2. Step 2: Check formula behavior after deletion

   Excel cannot adjust references to non-existent cells, so it shows #REF! error instead of adjusting to invalid references or zero.

3. Final Answer:

   #REF! -> Option B

4. Quick Check:

   Deleting referenced row causes #REF! error [OK]

Hint: Deleting referenced rows causes #REF! error [OK]

Common Mistakes:

• Thinking formula auto-adjusts to zero
• Assuming Excel changes references to invalid cells
• Confusing #REF! with #NAME? error

4. You see a #REF! error in a formula after moving cells. Which action will fix it?

medium

A. Save and close the workbook, then reopen it.

B. Replace the formula with a new one using correct references.

C. Delete the formula cell and re-enter the formula.

D. Undo the move to restore original cell references.

Solution

1. Step 1: Understand cause of #REF! after moving cells

   Moving cells that formulas reference can break those references, causing #REF! errors.

2. Step 2: Choose the best fix

   Undoing the move restores the original cell positions and fixes references automatically. Replacing formulas manually is possible but slower and error-prone.

3. Final Answer:

   Undo the move to restore original cell references. -> Option D

4. Quick Check:

   Undo restores broken references [OK]

Hint: Undo recent changes to fix broken references quickly [OK]

Common Mistakes:

• Thinking deleting and retyping fixes references
• Assuming saving and reopening fixes errors
• Replacing formulas without checking references

5. You have a formula =VLOOKUP(A2, Sheet2!A:B, 2, FALSE) that returns #REF!. What is the most likely cause?

hard

A. Column 2 does not exist in the range on Sheet2.

B. The lookup value in A2 is empty.

C. Sheet2 is protected and cannot be accessed.

D. The formula syntax is incorrect.

Solution

1. Step 1: Analyze the VLOOKUP parameters

   The formula looks up A2 in columns A:B on Sheet2 and returns the 2nd column's value.

2. Step 2: Understand #REF! in VLOOKUP context

   If the column index (2) is larger than the number of columns in the range, Excel returns #REF! because it cannot find that column.

3. Final Answer:

   Column 2 does not exist in the range on Sheet2. -> Option A

4. Quick Check:

   VLOOKUP column index > range columns causes #REF! [OK]

Hint: Check VLOOKUP column index vs range width [OK]

Common Mistakes:

• Assuming empty lookup value causes #REF!
• Thinking sheet protection causes #REF!
• Confusing syntax errors with reference errors