Excel errors look scary, but each one is a short message telling you exactly what went wrong. Once you know what they mean, most take seconds to fix.
Here’s every common Excel error, what causes it, and how to fix it.
Quick reference
| Error | What it means | Most common fix |
|---|---|---|
| #VALUE! | Wrong type of data in the formula | Check for text in a number calculation |
| #REF! | The formula points to a cell that no longer exists | Undo the deletion or rewrite the reference |
| #DIV/0! | Dividing by zero or an empty cell | Use IF or IFERROR to check the divisor |
| #NAME? | Excel doesn’t recognise a name in the formula | Fix the spelling or add quotes to text |
| #N/A | A lookup can’t find the value | Check spaces and number formats |
| #NUM! | A number is invalid or too large | Check inputs to the calculation |
| #NULL! | Two ranges don’t intersect | Replace the space with a comma or colon |
| #SPILL! | A formula’s results are blocked | Clear the cells in the way |
| ##### | The column is too narrow | Widen the column |
#VALUE!
Meaning: the formula expects a number but got text, or the data types don’t fit together.
Example: =A2+B2 where B2 contains “ten” or a number with a hidden space.
How to fix it:
- Check the referenced cells for text, spaces or symbols.
- Use SUM instead of +, because SUM ignores text:
=SUM(A2:B2). - Convert numbers stored as text using the yellow warning icon and Convert to Number.
- For dates, make sure the cell contains a real date, not text that looks like one.
#REF!
Meaning: the formula refers to a cell, row, column or sheet that has been deleted.
Example: =A2*C2, and then column C is deleted. The formula becomes =A2*#REF!.
How to fix it:
- Press Ctrl + Z straight away to undo the deletion.
- Otherwise, edit the formula and point it at the correct cell.
- In VLOOKUP, #REF! also appears if the column number is larger than the number of columns in the range.
#DIV/0!
Meaning: you’re dividing by zero or by an empty cell.
Example: =B2/C2 when C2 is blank.
How to fix it: show a blank (or 0) when the divisor is empty:
=IF(C2=0, "", B2/C2)This is common in percentage and average calculations before all data has been entered.
#NAME?
Meaning: Excel doesn’t recognise something in the formula.
Common causes:
- A misspelled function, like
=SUMM(A2:A10)or=VLOKUP(...). - Text without quotation marks:
=IF(A2=Yes, 1, 0)should be=IF(A2="Yes", 1, 0). - A missing colon in a range:
=SUM(A2A10). - Using a newer function, such as XLOOKUP or FILTER, in an older Excel version.
Tip: start typing a function name and pick it from Excel’s suggestion list to avoid typos.
#N/A
Meaning: a lookup function (VLOOKUP, XLOOKUP, MATCH) can’t find the value you searched for.
How to fix it: check for extra spaces, numbers stored as text, and an exact-match setting. We cover every cause in our full guide: How to Fix #N/A in VLOOKUP and XLOOKUP.
#NUM!
Meaning: a calculation produced an invalid number.
Examples: =SQRT(-4) (square root of a negative number), or a result too large for Excel to handle.
How to fix it: check the input values. Make sure they’re in a valid range for the function you’re using.
#NULL!
Meaning: you used a space between two ranges that don’t overlap. In Excel, a space is the “intersection” operator.
Example: =SUM(A2:A10 B2:B10).
How to fix it: use a comma to add separate ranges, or a colon for a continuous range: =SUM(A2:A10, B2:B10).
#SPILL!
Meaning: in Excel 365 and 2021, some formulas return several results that “spill” into neighbouring cells. #SPILL! means something is blocking that space.
How to fix it:
- Click the cell and look at the dashed border showing where results want to go.
- Clear any data in that area, including cells that only contain a space.
- Unmerge any merged cells in the way.
- Spilling formulas don’t work inside Excel tables; move the formula outside the table.
##### (hash signs)
Meaning: this isn’t really an error. The column is too narrow to show the number or date.
How to fix it: double-click the right edge of the column header to auto-fit the width. If ##### remains in a date cell, the date may be negative, for example when subtracting a later date from an earlier one.
Should you hide errors with IFERROR?
IFERROR replaces any error with a value you choose:
=IFERROR(B2/C2, 0)It’s useful for final reports, but use it carefully. IFERROR hides every error, including real mistakes like a deleted column (#REF!) or a typo (#NAME?). Fix the cause first, and only hide errors you expect.
How to find all errors in a sheet
- Press Ctrl + G (Go To), then click Special.
- Choose Formulas and tick only Errors.
- Click OK. Excel selects every cell with an error so you can review them.
Looking for more formula help? See 25 Excel formulas every office worker should know.