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

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.

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.

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.

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.

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.

What does the #REF! error indicate in Excel?

A Division by zero

B A misspelled function name

C A missing or invalid cell reference

D Incorrect data type

The #REF! error means the formula refers to a cell that was deleted or is invalid.

Which Excel tool helps you see which cells a formula depends on?

A Goal Seek

B Trace Precedents

C Data Validation

D Pivot Table

Trace Precedents shows arrows pointing to cells used in a formula.

What causes a #NAME? error in Excel?

A Dividing by zero

B Referencing deleted cells

C Adding text to numbers

D Using a wrong function name or missing quotes around text

Misspelled function names or missing quotes around text cause #NAME? errors.

If a formula tries to add a number and text, what error will likely appear?

A #VALUE!

B #REF!

C #DIV/0!

D #NAME?

Adding incompatible types like text and numbers causes a #VALUE! error.

How can you fix a #REF! error caused by deleting cells?

A Update the formula to valid cell references

B Rename the worksheet

C Change cell formatting

D Use conditional formatting

Fix #REF! by correcting the formula to point to existing cells.

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.

Describe how Excel's Formula Auditing tools help troubleshoot formula errors.

These tools show relationships and calculation steps.