Common Excel Errors: Every Error Code Explained and Fixed
Excel has 7 formula error codes. Each one tells you exactly what went wrong — if you know what to look for. This guide covers every error code with the most common causes and the exact steps to fix each one.
Quick Reference
To scan your entire workbook for all of these at once — across every sheet — drag your file into ExcelErrorFinder. Full report in about 10 seconds. No install. 100% private.
A formula references a cell that no longer exists.
Common Causes
- •A row or column that the formula referenced was deleted
- •A formula was copied into a cell where a relative reference goes out of range
- •An external workbook link points to a file that has been moved or renamed
How to Fix
- 1.Press Ctrl+H (Find & Replace) → find #REF! → identify the broken formula
- 2.Click the cell with #REF! → look at the formula bar — the broken reference appears as =SUM(A1:#REF!)
- 3.Restore the deleted rows/columns, or update the formula to reference the correct cells
- 4.For workbook links: Data → Edit Links → Update or change the source
Pro Tip
Use absolute references ($A$1) for cells you frequently copy formulas across — they won't shift when copied and are far less likely to become #REF!.
A lookup formula could not find the value it was searching for.
Common Causes
- •The lookup value doesn't exist in the lookup range (VLOOKUP, XLOOKUP, MATCH)
- •Trailing spaces or different data types between lookup and table values (number vs. text "42")
- •VLOOKUP with exact match (0) and the value is genuinely absent
- •Named range used in a formula was deleted or renamed
How to Fix
- 1.Wrap the formula in IFERROR: =IFERROR(VLOOKUP(...), "Not found") to handle gracefully
- 2.Check for data type mismatch: use =ISNUMBER(A1) vs =ISTEXT(A1) on lookup values
- 3.Trim trailing spaces: =VLOOKUP(TRIM(A1), ...) or clean the source data first
- 4.For XLOOKUP: use match_mode -1 or 1 for approximate matching when exact isn't needed
Pro Tip
Most #N/A errors in VLOOKUP are caused by number-stored-as-text mismatches. Convert both columns to the same type before looking up.
A formula received the wrong data type for what it expected.
Common Causes
- •Trying to add, subtract, or multiply text and numbers (=A1+B1 where A1 contains text)
- •A date cell being used in a formula but stored as text rather than a real date
- •Array formula receiving a range when it expects a single value
- •TRIM, LEN, or text functions applied to a range instead of a single cell without array entry
How to Fix
- 1.Identify which cell has bad data: click the cell → Formulas → Evaluate Formula to step through
- 2.Convert text to numbers: select the column → Data → Text to Columns → Finish
- 3.For text dates: use DATEVALUE() to convert them to real dates Excel can calculate with
- 4.Wrap math in VALUE() to force text-number conversion: =VALUE(A1)+VALUE(B1)
Pro Tip
Numbers imported from external systems (CSVs, web scrapes, ERP exports) almost always come in as text. Always check with =ISNUMBER() before using them in formulas.
A formula is attempting to divide a number by zero or an empty cell.
Common Causes
- •A formula like =A1/B1 where B1 is 0 or blank
- •AVERAGE() applied to a range of empty cells
- •Division in a percentage formula where the base value is not yet entered
How to Fix
- 1.Wrap in IFERROR: =IFERROR(A1/B1, 0) or =IFERROR(A1/B1, "—")
- 2.Use IF to check first: =IF(B1=0, 0, A1/B1)
- 3.For AVERAGE: use AVERAGEIF to exclude zeros: =AVERAGEIF(range,"<>0")
Pro Tip
#DIV/0! is common in template spreadsheets where denominator columns haven't been filled yet. Use IFERROR to suppress it cleanly rather than hiding the formula.
Excel does not recognise the formula or named range in the cell.
Common Causes
- •Typo in a function name (=VLOOKUP vs =VLOOKP)
- •Using a function that requires a newer Excel version (XLOOKUP in Excel 2016, for example)
- •Referencing a named range that was deleted or renamed
- •Text in a formula not wrapped in quotes: =IF(A1=Yes, ...) instead of =IF(A1="Yes", ...)
How to Fix
- 1.Check the function spelling: start typing and let Excel's autocomplete suggest the correct name
- 2.Check your Excel version supports the function — XLOOKUP requires Excel 2019 or Microsoft 365
- 3.Formulas → Name Manager → check that named ranges still exist
- 4.Add quotes around text values in formulas: =IF(A1="Yes", ...) not =IF(A1=Yes, ...)
Pro Tip
A common source of #NAME? is accidentally typing a formula in a cell that's formatted as Text. The formula text is stored literally instead of calculated. Reformat the cell to General and re-enter the formula.
A formula contains an invalid numeric value or produces a number too large to display.
Common Causes
- •Square root or logarithm of a negative number: =SQRT(-4)
- •IRR or RATE function cannot converge on a solution with the data provided
- •A number result is too large or too small for Excel to represent (beyond ±9.99×10^307)
- •DATE function receives an invalid date argument (month 13, day 32)
How to Fix
- 1.Check inputs: ensure numeric functions receive positive values where required
- 2.For IRR/RATE: provide a reasonable guess value in the optional argument, and check cash flows have at least one sign change
- 3.Use IFERROR to handle edge cases gracefully: =IFERROR(SQRT(A1), "Invalid input")
Pro Tip
#NUM! from financial functions like IRR often means the input data doesn't represent a valid investment (e.g., all positive cash flows with no initial outflow). Review the economic logic, not just the formula.
A formula uses a space where a comma or colon was intended, creating an invalid range intersection.
Common Causes
- •Using a space instead of a comma between arguments: =SUM(A1 B1) instead of =SUM(A1,B1)
- •Using a space instead of a colon in a range: =SUM(A1 A10) instead of =SUM(A1:A10)
- •Two ranges separated by a space that genuinely have no intersection (intentional operator but no overlap)
How to Fix
- 1.Replace spaces between cell references with commas (for multiple arguments) or colons (for ranges)
- 2.Example: =SUM(A1 B1) → =SUM(A1,B1) and =SUM(A1 A10) → =SUM(A1:A10)
- 3.Note: the space IS a valid operator in Excel — it returns the intersection of two ranges. It's only an error when the ranges don't intersect.
Pro Tip
#NULL! is the rarest Excel error and almost always caused by a typo. It's easiest to spot in the formula bar — look for an unexpected space between cell references.
##### — Not an Error, But a Common Problem
##### is not a formula error — it means the column is too narrow to display the cell's value. To fix it, double-click the column border in the header row to auto-fit the width, or drag the column border to make it wider. It appears most often in date and large number columns. If the column is already wide enough and ##### still shows, the cell might be formatted as a date but contain a negative number (which dates cannot represent).
How to Find All Errors in a Workbook
Excel's built-in Error Checking (Formulas tab → Error Checking) steps through errors one sheet at a time. For a workbook with multiple sheets, you would need to repeat this process for every sheet manually — and it only catches formula errors, not broken links, hardcoded values, or hidden content.
For a faster full-workbook audit: drag your file into ExcelErrorFinder. It scans every sheet simultaneously and produces a structured report showing every error type, broken link, hardcoded value, inconsistent formula, and hidden sheet — all in one pass. Files are processed entirely in your browser and never uploaded.
Find every error in seconds — across all sheets
ExcelErrorFinder catches all 7 error types plus broken links, hardcoded values, hidden sheets, and inconsistent columns — in a single drag-and-drop audit. No install. No upload. 100% private.
Audit My Spreadsheet Free →Frequently Asked Questions
What are all the Excel error codes?
Excel has 7 formula error codes: #REF! (broken reference), #N/A (value not found), #VALUE! (wrong data type), #DIV/0! (division by zero), #NAME? (unrecognised function), #NUM! (invalid number), and #NULL! (bad range intersection). ##### is a display issue, not an error code.
What does #REF! mean in Excel?
#REF! means a formula references a cell that no longer exists — usually because a row or column was deleted. Find the formula, look for #REF! in the formula bar, and update the reference to the correct cell.
How do I find all errors in an Excel workbook?
Formulas → Error Checking for a single sheet. For a full workbook scan across all sheets: use ExcelErrorFinder — drag your file in and get a complete report in seconds.
What is the difference between #N/A and #VALUE! in Excel?
#N/A means a lookup could not find the search value. #VALUE! means a formula received the wrong data type (e.g., trying to add text and a number). #N/A is a "not found" error; #VALUE! is a "wrong type" error.
Related Guides
Formula Errors Explained
Deeper dive into why formula errors happen and how Excel's error hierarchy works.
Read →
VLOOKUP Guide
The most common source of #N/A errors — complete syntax and fix guide.
Read →
Formula Auditing Tools
Use Excel's built-in Trace Precedents and Error Checking to diagnose formulas.
Read →
Best Excel Audit Tools
Full comparison of ExcelErrorFinder, Inquire, and paid add-ins.
Read →