Understand formula errors
Read the symptom, inspect the inputs and fix the cause.
By the end, you’ll be able to:
- Recognize common formula errors
- Trace a result back to its inputs
- Handle a known zero divisor without hiding other problems
An error is a clue about the calculation
If a formula does not return the expected result, read what Excel is telling you before replacing it. Some problems produce an error code; others produce a plausible but wrong number.
| Symptom | Common cause | First check |
|---|---|---|
#DIV/0! | Dividing by zero or an empty divisor | The denominator and missing inputs |
#VALUE! | An incompatible input type | Text where a number is expected |
#REF! | An invalid cell reference | Deleted cells or broken references |
#NAME? | Unrecognized function/name or unquoted text | Spelling and quotation marks |
##### | Often insufficient display width | Column width and date/time display |
These are common explanations, not the only possible causes. A lookup may also return #N/A when a requested match is unavailable. We will examine lookup-specific errors later.
Work backward from the unexpected result
- Select the result cell and read the formula bar.
- Check its referenced cells: values, data types, units and row labels.
- Check parentheses, range endpoints and copied references.
- Calculate a small example independently.
- In desktop Excel, use tools such as Trace Precedents or Evaluate Formula where available.
If a total suddenly doubles, look for a subtotal included alongside its detail. If a copied formula changes a shared rate, inspect the $ signs. Neither problem necessarily produces an error code.
For example, =SUM(B2:B6) may be valid syntax, but wrong if B6 is already the subtotal of B2:B5. Always ask what each row represents.
Worked example: wait for a usable denominator
Total cost is in B2 and units in C2. The formula =B2/C2 works when units are nonzero. If zero units means the measure is not ready, use =IF(C2=0,"Not ready",B2/C2).
The formula explicitly tests the known condition. With 72 cost and 8 units, the result is 9. With zero units, it returns Not ready. The result intentionally mixes numeric values with an informative text status, so downstream calculations must account for that.
This guard does not validate negative units, text or incorrect costs. Choose input rules from the real business meaning instead of assuming every unexpected value should become zero.
Fix the cause before adding a fallback
IFERROR can provide a deliberate alternative when a calculation fails, but using it to return 0 everywhere can make broken inputs look like successful zero results. Learn to diagnose the original calculation first.
Keep the source value, correct the confirmed issue, and recheck the result. Avoid overwriting a formula with a hard-coded answer just to make the error disappear.
The practice checker is a small teaching interpreter. Its unsupported-formula messages are not a complete reproduction of Excel’s error system. Use the lesson’s diagnosis process when working in Excel itself.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
Read the symptom, inspect references and data types, then fix the confirmed cause. A deliberate fallback should not conceal an unknown problem.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.