Bird
Raised Fist0
Excelspreadsheet~5 mins

Reference errors and troubleshooting in Excel - Cheat Sheet & Quick Revision

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Recall & Review
beginner
What does the #REF! error mean in Excel?
The #REF! error means that a formula is referring to a cell or range that no longer exists, often because the referenced cells were deleted or moved.
Click to reveal answer
beginner
How can you fix a #REF! error caused by deleted cells?
You can fix it by updating the formula to refer to valid cells or ranges that exist, or by undoing the deletion if possible.
Click to reveal answer
beginner
What is a common cause of #NAME? errors in Excel formulas?
A #NAME? error usually happens when Excel does not recognize text in a formula, often due to misspelled function names or missing quotation marks around text.
Click to reveal answer
intermediate
Why might a formula show #VALUE! error?
The #VALUE! error appears when a formula has the wrong type of argument or operand, like trying to add text to a number.
Click to reveal answer
intermediate
What is a good way to troubleshoot formulas with errors in Excel?
Use Excel's Formula Auditing tools like 'Trace Precedents' and 'Evaluate Formula' to see which cells affect the formula and how Excel calculates it step-by-step.
Click to reveal answer
What does the #REF! error indicate in Excel?
ADivision by zero
BA misspelled function name
CA missing or invalid cell reference
DIncorrect data type
Which Excel tool helps you see which cells a formula depends on?
AGoal Seek
BTrace Precedents
CData Validation
DPivot Table
What causes a #NAME? error in Excel?
ADividing by zero
BReferencing deleted cells
CAdding text to numbers
DUsing a wrong function name or missing quotes around text
If a formula tries to add a number and text, what error will likely appear?
A#VALUE!
B#REF!
C#DIV/0!
D#NAME?
How can you fix a #REF! error caused by deleting cells?
AUpdate the formula to valid cell references
BRename the worksheet
CChange cell formatting
DUse conditional formatting
Explain what causes the #REF! error and how you can fix it.
Think about what happens if you delete a cell that a formula uses.
You got /4 concepts.
    Describe how Excel's Formula Auditing tools help troubleshoot formula errors.
    These tools show relationships and calculation steps.
    You got /4 concepts.

      Practice

      (1/5)
      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