If you've spent more than ten minutes in Excel, you've seen those dreaded hash symbols: #REF!, #VALUE!, #DIV/0!. They look alarming, but they're actually Excel trying to help — each one is a precise description of exactly what went wrong. Once you learn what each error means, fixing them becomes much faster.
This guide covers all seven major Excel formula errors, what causes them, how to find them in large workbooks, and step-by-step fixes for each.
How to Find All Errors in a Workbook Quickly
Before diving into individual errors, here are three ways to find all errors at once:
- Go To Special: Press Ctrl+G → Special → Formulas → check only "Errors" → OK. Excel selects every error cell.
- Conditional Formatting: Select your data range → Home → Conditional Formatting → Highlight Cell Rules → More Rules → "Format only cells with" → Errors.
- ExcelErrorFinder: Upload your file to the free audit tool — it finds and explains every error cell automatically, with cell-by-cell context and fixes.
The 7 Excel Formula Errors
#REF! — Broken Cell Reference
What it means: The formula is pointing to a cell or range that no longer exists. This almost always happens after a row or column deletion, or after moving data in a way that breaks a formula's reference.
Common causes:
- Deleting a row or column that was referenced by a formula in another cell
- Cutting and pasting a cell that was the target of a reference
- Deleting a named range that formulas depended on
How to fix it: Click the cell with #REF! and look at the formula bar. You'll see something like =SUM(A1:#REF!). The broken part is highlighted in red. Correct the range manually, or use Ctrl+Z to undo the deletion that caused it. For full guidance, see our complete #REF! fix guide.
#DIV/0! — Division by Zero
What it means: The formula is trying to divide a number by zero, or by an empty cell. This is extremely common in percentage calculations, ratios, and growth metrics where the denominator is pulled from a data row that hasn't been filled in yet.
How to fix it: The cleanest solution is wrapping the formula with IFERROR:
=IFERROR(A1/B1, 0)This returns 0 (or any fallback you choose) instead of the error. Use "" instead of 0 if you want the cell to appear blank.
#VALUE! — Wrong Data Type
What it means: The formula is trying to perform a mathematical operation on a cell that contains text instead of a number. This is one of the most common errors in workbooks that import data from external systems, because imported numbers are often stored as text.
How to spot it: Look for a small green triangle in the top-left corner of cells that look like numbers but have left-alignment (numbers are right-aligned by default).
How to fix it: Select the affected cells, click the warning icon that appears, and choose "Convert to Number". Or use =VALUE(A1) in a helper column to convert text to numbers.
#NAME? — Unknown Function or Named Range
What it means: Excel doesn't recognise a function name or named range in the formula. This is almost always a typo — writing SUMIF as SUMI, for example.
Other causes:
- Using a function from a newer Excel version (like
XLOOKUP) in an older workbook compatibility mode - Referencing a named range that was deleted or renamed
- Missing quotation marks around text in a formula:
=IF(A1=Yes,1,0)instead of=IF(A1="Yes",1,0)
How to fix it: Check the formula bar for red underlines on the unrecognised name. Correct the spelling. Check Formulas → Name Manager to verify named ranges still exist.
#N/A — Value Not Found
What it means: A lookup function (VLOOKUP, HLOOKUP, MATCH, INDEX/MATCH) searched for a value and couldn't find it in the lookup range.
Common causes:
- The lookup value genuinely doesn't exist in the lookup table
- Data type mismatch: the lookup column contains numbers, but you're searching for text (or vice versa)
- Extra spaces in the lookup value or the lookup table
How to fix it: Use IFERROR to handle missing lookups gracefully: =IFERROR(VLOOKUP(A1,B:C,2,0),"Not Found"). If the lookup should be finding something, use TRIM() to remove extra spaces and VALUE() to ensure matching data types.
#NUM! — Invalid Number
What it means: The formula produced a number that is too large, too small, or mathematically impossible — for example, SQRT(-1) (square root of a negative number) or IRR with no valid internal rate of return.
How to fix it: Validate your input values. For iterative functions like IRR, try providing a different initial guess. Wrap with IFERROR to handle edge cases.
#NULL! — Invalid Range Intersection
What it means: The formula uses a space character (the intersection operator) between two ranges that don't actually intersect. This is rare but confusing — it usually happens when someone accidentally types a space instead of a comma between function arguments: =SUM(A1:A5 B1:B5) instead of =SUM(A1:A5,B1:B5).
How to fix it: Check the formula for missing commas. Replace any accidental spaces between ranges with commas.
Preventing Errors in the First Place
The best approach to formula errors is preventing them before they occur:
- Always wrap divisions with IFERROR — it takes five seconds and prevents a whole class of errors
- Use structured tables (Ctrl+T) — table references like
[@Revenue]don't break when rows are deleted - Avoid hardcoding numbers in formulas — put constants in named cells instead
- Audit before sharing — use the ExcelErrorFinder tool to catch issues before they reach stakeholders
Automatically Find All Errors in Your Workbook
Hunting for errors manually is slow, especially in large workbooks. The ExcelErrorFinder audit tool scans every cell in your spreadsheet and flags all error cells, inconsistent formulas, hardcoded values, and more — with a plain-English explanation and fix for each one. It runs entirely in your browser, so your data never leaves your device.