Purpose
By the end of this lesson, you will be able to recognize common Excel error types and use auditing tools to trace and fix them.
Lesson Explanation
Excel shows a specific error code depending on what went wrong. #DIV/0! means a formula tried to divide by zero or an empty cell. #N/A means a lookup function couldn’t find a match. #REF! means a formula references a cell that has since been deleted. #VALUE! means a formula is trying to perform a calculation on the wrong data type, like adding text to a number. #NAME? means Excel doesn’t recognize a function name, usually from a typo.
IFERROR wraps a formula to catch any error and replace it with a custom value or message: =IFERROR(VLOOKUP(A2,B:C,2,FALSE),”Not Found”) returns “Not Found” instead of a raw #N/A error whenever the lookup fails, which is far more readable in a shared report.
The Trace Precedents and Trace Dependents tools (Formulas → Formula Auditing) draw visual arrows showing which cells feed into a given formula (precedents) or which formulas depend on a given cell (dependents) – useful for understanding a complex, unfamiliar spreadsheet or finding the source of an error that has spread across multiple formulas.
The Evaluate Formula tool steps through a complex formula one calculation at a time, showing the intermediate result at each stage – useful for finding exactly where in a nested formula an error originates.
Practice Questions
1. A formula returns #DIV/0!. What does this error specifically indicate?
View Answer
The formula is attempting to divide by zero or by an empty cell.
2. A VLOOKUP formula returns #N/A. What does this specific error indicate?
View Answer
The lookup value could not be found in the specified range.
3. A formula that used to work now shows #REF! after a colleague deleted a column. What does this error indicate?
View Answer
The formula references a cell that no longer exists, because the column (or row) it pointed to was deleted.
4. Write a formula that wraps a VLOOKUP on A2 against range B:C, returning column 2, so that instead of showing a raw #N/A error, it displays the text “Not Found.”
View Answer
=IFERROR(VLOOKUP(A2,B:C,2,FALSE),”Not Found”)5. A formula shows #VALUE! when trying to add two cells together. What is a likely cause?
View Answer
One of the cells contains text rather than a true number, so the addition operation fails on a data-type mismatch.
6. A formula shows #NAME?. What is the most likely cause?
View Answer
A typo in the function name (e.g., typing =VLOOKUP as =VLOOKUP with a misspelling), which Excel doesn’t recognize as a valid function.
7. A colleague wants to understand which cells feed into a complex formula in cell D10, without manually clicking through each reference. What tool would show this visually?
View Answer
Trace Precedents (Formulas → Formula Auditing → Trace Precedents), which draws arrows from the source cells into D10.
8. A value in cell B5 is changed, and a manager wants to know every other formula in the workbook that depends on B5, to check what else might be affected. What tool answers this?
View Answer
Trace Dependents, which draws arrows from B5 to every formula that references it.
9. A deeply nested formula combining IF, VLOOKUP, and SUM produces an unexpected result, and it isn’t clear which part is causing the issue. What tool would help pinpoint the exact stage where the problem occurs?
View Answer
Evaluate Formula, which steps through the calculation one stage at a time, showing intermediate results.
10. A shared report has several #N/A errors visible to external stakeholders, which looks unprofessional even though the underlying data is otherwise correct. What formula-level fix would clean this up before sharing?
View Answer
Wrapping the lookup formulas in IFERROR to replace raw error codes with a more presentable message, like “Not Found” or a blank result.
11. A formula =A2/B2 is copied down a column, and several rows show #DIV/0!. What formula change would replace these errors with a blank cell instead, while leaving correctly calculated rows unaffected?
View Answer
=IFERROR(A2/B2,””)12. Why might using IFERROR to hide every error be risky in some cases, even though it makes a report look cleaner?
View Answer
If IFERROR silently replaces a genuine data problem (not just an expected, harmless case like a missing lookup) with a blank or generic message, it could mask an underlying issue that actually needed investigation and fixing, rather than hiding.