What you will build

Use two small, reproducible formula errors to learn a safe debugging routine: read the error first, inspect the referenced inputs, make the smallest correction, and confirm the recalculated result.

Learning objectives

Open the practice workbook

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

The _result workbook shows the complete clean source data before the action.

Hands-on practice

Step 1: In the _result workbook, select D2. The formula bar should show =B2/C2 while D2 shows #DIV/0! because C2 is 0. Read the error before editing anything.

Real Google Sheets evidence for the action step, with the relevant range or menu visible and the cursor placed on the intended control.

Step 2: Select D3. The formula bar should show =VLOOKUP(B3,$F$2:$G$3,2,FALSE). B3 contains X, but the lookup table F2:G3 contains only A and B, so an exact-match VLOOKUP returns #N/A.

Real Google Sheets evidence for the action step, with the relevant range or menu visible and the cursor placed on the intended control.

Step 3: Test the smallest input corrections without overwriting either formula: change C2 from 0 to 2 and confirm D2 recalculates to 5; change B3 from X to A and confirm D3 returns Ana. Then restore C2 to 0 and B3 to X so the reusable practice workbook again shows both error cases.

Corrected test state in Google Sheets: C2 set to 2 recalculates D2 to 5, and B3 set to A makes D3 return Ana.

Checkpoint

CaseFormulaOriginal resultTest correction
Divide by zero=B2/C2#DIV/0!C2=2 → 5
Missing lookup=VLOOKUP(B3,$F$2:$G$3,2,FALSE)#N/AB3=A → Ana

After testing, restore C2=0 and B3=X; D2 and D3 should again show the two original errors.

Common mistakes and fixes

Correction: If D2 still shows #DIV/0!, confirm C2 is a nonzero number. If D3 still shows #N/A, confirm B3 exactly matches a key in F2:F3 and keep FALSE for exact matching.

Independent practice

On your own copy, first change C2 to 5 and verify D2 becomes 2. Then restore C2 to 0. Next change B3 to B and verify D3 becomes Ben, then restore B3 to X.

Key takeaways

  • ✓Read the displayed error before changing the sheet.
  • ✓#DIV/0! in D2 comes from dividing B2 by the zero value in C2.
  • ✓#N/A in D3 comes from an exact lookup key in B3 that is absent from F2:F3.
  • ✓Fix the causal input or reference instead of replacing the formula with a value.
  • ✓After testing a changed input, restore the practice workbook to its original error examples.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. Why does D2 return #DIV/0!?
2. Why does D3 return #N/A?
3. What is the safest way to test the D2 correction?
4. After testing, why restore C2 to 0 and B3 to X?

Next lesson

Continue in recommended learning order with Check file ownership before sharing.