Reference errors and troubleshooting in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to correctly reference cell A1.

Excel

=SUM([1])

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

A1A

BCellA1

CA1

DA_1

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the column letter and row number.

Using invalid characters in the cell reference.

The correct cell reference format is the column letter followed by the row number, like A1.

2fill in blank

medium

Complete the formula to fix the #REF! error caused by a deleted cell reference.

Excel

=SUM(B1:[1])

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

AC0

BZ1

CA1

DB10

Attempts:

3 left

💡 Hint

Common Mistakes

Using invalid cell references like C0.

Choosing a cell outside the intended range.

The range must be valid and continuous. B10 is a valid cell reference to complete the range.

3fill in blank

hard

Fix the error in the formula by completing the missing reference.

Excel

=VLOOKUP(A2, [1], 2, FALSE)

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

ASheet1!A:B

BA2:B10

CA:B

DSheet1!1:2

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range without the sheet name when referencing another sheet.

Using row numbers instead of columns for the table array.

The table array must include the sheet name and the correct columns. Sheet1!A:B correctly references columns A and B on Sheet1.

4fill in blank

hard

Fill both blanks to create a formula that sums values in column C only if column B equals "Yes".

Excel

=SUMIF([1], [2], C:C)

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

AB:B

B"Yes"

CC:C

D"No"

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column for the range.

Not putting the criteria text in quotes.

The range to check is column B (B:B), and the criteria is the text "Yes".

5fill in blank

hard

Fill all three blanks to create a formula that counts cells in column D that are greater than 100.

Excel

=COUNTIF([1], [2] & [3])

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

AD:D

B">"

C100

D"D:D"

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the range in quotes incorrectly.

Not combining the operator and number properly.

The range is column D (D:D), the operator is ">", and the number is 100. The operator and number are combined as a string.

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