Error Reference Hub
Excel Formula Errors: Complete Guide
Every Excel error explained — what it means, what causes it, and how to fix it. Use this hub to find the right guide for any error you encounter.
Cause: A formula references a cell that no longer exists — usually after deleting a row, column, or sheet.
Fix: Click the cell, find the #REF! token in the formula bar, replace it with the correct address.
Cause: A lookup function (VLOOKUP, XLOOKUP, MATCH) cannot find the lookup value in the search range.
Fix: Check for data type mismatches and extra spaces. Wrap with IFERROR for expected missing values.
Cause: Excel cannot recognise a word in your formula — misspelled function name, deleted named range, or missing quotes.
Fix: Check function spelling. Open Formulas → Name Manager to verify named ranges still exist.
Cause: A formula divides by zero or by a blank cell — the most common cause is an empty denominator.
Fix: Use =IFERROR(A1/B1,"") or =IF(B1=0,"",A1/B1) to handle the zero case explicitly.
Cause: A formula has a mathematically invalid argument (e.g. SQRT of a negative), an out-of-range result, or IRR cannot converge.
Fix: Guard inputs with IF checks. For IRR, add a guess argument: =IRR(A1:A10, 0.1).
Cause: A space between two ranges instead of a comma — e.g. =SUM(A1:A5 C1:C5). The space is Excel's intersection operator.
Fix: Replace the space with a comma: =SUM(A1:A5, C1:C5).
Cause: A formula receives the wrong data type — text where a number is expected, or an array argument where a single value is required.
Fix: Use ISNUMBER() and ISTEXT() to identify the bad input cell. Convert text numbers with VALUE().
Cause: A formula directly or indirectly refers to its own cell, creating a loop that Excel cannot resolve.
Fix: Open Formulas → Error Checking → Circular References to find the cell. Break the cycle by restructuring the formula.
Find every error in your spreadsheet automatically
Upload your file and ExcelErrorFinder will scan all sheets for every error type — with the cell address, formula, and a plain-English explanation.
How to Debug Any Excel Error
A systematic six-step process that works for any error code.
Read the error code
Each code tells you exactly what type of problem occurred. #REF! is always a broken reference; #N/A is always a lookup miss; #DIV/0! is always a zero denominator. Start here.
Click the error cell, read the formula bar
Excel highlights the broken part of the formula in the formula bar. Look for #REF! tokens, or think about which argument could be receiving the wrong data type.
Trace precedents
Go to Formulas → Trace Precedents to draw arrows to every cell your formula reads. This shows you where to look for the bad data.
Check input data types
Use =ISNUMBER(A1) and =ISTEXT(A1) on input cells. A cell that looks like a number but returns ISTEXT=TRUE is a text-formatted number — a common silent root cause.
Use Evaluate Formula
Formulas → Evaluate Formula steps through the calculation one argument at a time so you can see exactly where the result goes wrong.
Audit all sheets at once
F5 → Special → Errors selects every error on the current sheet. For the whole workbook, use ExcelErrorFinder — it scans all sheets in one pass.
In-Depth Error Solution Guides
Fix #REF! Errors
Step-by-step guide to finding and permanently fixing every broken cell reference.
Read guide →
Fix #N/A Errors
Fix #N/A in VLOOKUP, XLOOKUP, and MATCH — data mismatches, spaces, and missing values.
Read guide →
Fix #NAME? Errors
Misspelled functions, deleted named ranges, missing quotes — every cause with exact fixes.
Read guide →
Fix #DIV/0! Errors
Empty denominators, zero values, AVERAGE on blanks — and the right way to suppress each.
Read guide →
Fix #NUM! Errors
Invalid SQRT/LOG args, out-of-range results, and IRR that fails to converge.
Read guide →
Fix #NULL! Errors
The space-operator trap — almost always a missing comma between range arguments.
Read guide →
Related Error Guides
Frequently Asked Questions
What are the most common Excel formula errors?
The seven main Excel formula errors are: #REF! (broken cell reference), #N/A (value not found in lookup), #NAME? (unrecognised function or range name), #DIV/0! (division by zero or blank), #NUM! (invalid numeric argument), #NULL! (space operator on non-overlapping ranges), and #VALUE! (wrong data type in formula argument). Circular reference is a separate issue that causes 0 or incorrect values rather than an error code.
How do I find all errors in an Excel spreadsheet at once?
Three methods: (1) Press F5 → Special → Formulas → Errors to select all error cells on the current sheet. (2) Use Formulas → Error Checking to step through each error one by one. (3) Upload your file to ExcelErrorFinder — it scans every formula across all sheets and lists every error with cell address, formula text, and explanation.
Why does my Excel formula show an error but the formula looks correct?
The most common cause is a data type mismatch — the formula is syntactically correct but the data it is reading is not in the expected format. Check the input cells with =ISNUMBER() and =ISTEXT() to diagnose.