What you will build

Reproduce a #REF! error on a small table, identify the invalid reference, and verify a guarded output while keeping the broken example visible for diagnosis.

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 Google Sheets source table before entering formulas, with Broken result and Guarded output still blank.

Hands-on practice

Step 1: Select D2 and enter =INDIRECT("A0"). Because row 0 does not exist in Google Sheets, A0 is an invalid address and D2 must return #REF!.

Step 2: Read the error before changing anything. #REF! means the formula cannot resolve a referenced cell or range. In this controlled example, the broken reference text is A0.

Step 3: In E2, enter =IFERROR(INDIRECT("A0"),"Check reference") and confirm the guarded message appears. This does not repair A0; it only replaces the displayed error. In real work, change the bad reference to the intended valid cell or range once you have identified it.

=INDIRECT("A0")
D2 selected with =INDIRECT("A0") visible in the formula bar and #REF! displayed in the sheet.

D2 is the intentionally broken example; keep it visible so you can compare the raw #REF! error with the guarded output.

=IFERROR(INDIRECT("A0"),"Check reference")
E2 selected with =IFERROR(INDIRECT("A0"),"Check reference") visible in the formula bar and Check reference displayed in the sheet.

E2 demonstrates a guarded output. It should display Check reference while D2 continues to show #REF!.

Checkpoint

CaseInputBroken resultGuarded output
Invalid referenceA0#REF!Check reference

D2 must be calculated by =INDIRECT("A0"). E2 must be calculated by IFERROR; the fallback is not a repaired reference.

Common mistakes and fixes

Correction: return to the formula that produces #REF!, identify the invalid address, and replace it with the intended valid reference. Use IFERROR only when a fallback is part of the design, not as a substitute for fixing the reference.

Independent practice

Add one row with the same schema, repeat the action, verify the new row behaves correctly, then remove it to restore the sample.

Key takeaways

  • ✓#REF! means a formula cannot resolve a referenced cell or range.
  • ✓A0 is invalid because Google Sheets rows start at 1.
  • ✓Keep the broken formula visible while diagnosing the reference.
  • ✓IFERROR can replace the displayed error with a helpful message, but it does not repair the bad reference.
  • ✓When the intended target is known, correct the reference instead of typing over the formula.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. What is the correct first action in “Fix #REF! errors”?
2. Which follow-up action belongs to “Fix #REF! errors”?
3. What should you verify before “Fix #REF! errors” is complete?
4. Which correction is appropriate if “Fix #REF! errors” behaves unexpectedly?

Next lesson

Continue in recommended learning order with Set up a clean sheet for Class attendance.