How to fix #N/A and other errors in Excel
To fix an error in Excel, read what the error code says, because each one points to a different cause. #N/A means a lookup found nothing, #DIV/0! means a division by zero, and #VALUE! means a value is the wrong type. Fix the cause first. Use IFERROR to hide an error only when you expect it.
1. #N/A: the lookup found nothing
VLOOKUP, XLOOKUP and MATCH return #N/A when the value you look for is not in the list. Check three things: a trailing space (clean with TRIM), a number stored as text on one side only, and a table range that does not include the row. When a missing value is normal, use IFNA to replace only this error.
Example: A missing code becomes a readable message
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Product | Look for | Result | |
| 2 | P-100 | Pen | P-999 | Not found | |
| 3 | P-101 | Pad | |||
| 4 | P-102 | Ink |
P-999 is not in the list, so VLOOKUP gives #N/A and IFNA turns it into the message.
2. #DIV/0!: division by zero
Excel cannot divide by zero or by an empty cell. Find the denominator that is zero or blank. If a zero is a normal case, as with a customer who has no orders yet, catch it with IFERROR.
Example: An average with no orders
| A | B | C | |
|---|---|---|---|
| 1 | Total | Orders | Average |
| 2 | 10 | 0 | 0 |
10 divided by 0 is #DIV/0!, so IFERROR returns the 0 you chose.
3. #VALUE!, #REF!, #NAME? and #NUM!
#VALUE! means a value is the wrong type, such as text in a calculation or a space in a number cell. Test a cell with ISNUMBER. #REF! means a reference points at a cell that was deleted, so undo the delete or rewrite the reference. #NAME? means Excel does not know a word in the formula: a function spelled wrong, a missing quote around text, or a name that does not exist. #NUM! means a number is impossible, such as the square root of a negative number.
4. #SPILL! and ####
#SPILL! appears when a dynamic array formula needs room and something blocks the cells below or beside it. Clear them. A row of #### is not a formula error: the column is too narrow for the number or date, so widen it.
5. Use Evaluate Formula to find the cause
Go to Formulas > Evaluate Formula and click Evaluate. Excel steps through the formula one part at a time, so you see exactly which part turns into the error. The green triangle in a cell corner also offers a menu with the error checking options.
Common mistakes
Wrapping every formula in IFERROR from the start. It hides real problems, such as a wrong range, and the sheet quietly shows zeros.
Fix: Find and fix the cause first. Use IFNA when only a missing lookup is expected.
Looking up a number against text. The number 100 and the text "100" look the same, but a lookup does not match them.
Fix: Make both sides the same type, for example with VALUE or by multiplying by 1.
Deleting a column that formulas use. The formulas show #REF!.
Fix: Undo with Ctrl+Z straight away, or clear the cells instead of deleting them.
Frequently asked questions
What is the difference between IFERROR and IFNA?
IFERROR catches every error. IFNA catches only #N/A, which is safer for lookups because it does not hide a wrong range or a typo.
Why do I get #N/A when the value is in the list?
Usually a trailing space, a number stored as text, or an exact match on a value that differs by one character. Check both sides with TRIM and ISNUMBER.
How do I find all the errors on a sheet?
Press Ctrl+G, click Special, choose Formulas and tick Errors only. Excel selects every cell that shows an error.
Related functions
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- TRIMTRIM removes extra spaces from text: at the start, at the end, and doubles between words.
- IFIF checks a condition and returns one value if it's true and another if it's false.
More on Excel errors
The guide explains each error and how to fix it. This piece adds a quick way to find the cause in a long formula, how to check cells before you calculate, and how to keep errors out of totals.
Find the cause in a long formula
Select part of the formula in the formula bar and press F9. Excel shows the value of just that part, so you see which piece returns the error. Press Esc afterwards, or you will replace the formula with its value. Formulas > Evaluate Formula does the same step by step without the risk.
Check the cells before you calculate
The IS functions return TRUE or FALSE without raising an error. ISNUMBER tells you whether a value is a real number, and ISBLANK whether a cell is empty. Use them to decide what to do.
=IF(ISNUMBER(B2),B2*2,"check input")Keep errors out of totals
One error in a range makes SUM of that range an error too. The fix is to repair the source cell, or to use AGGREGATE, which can ignore errors. For a quick total, wrap each cell in IFERROR and return 0, but only when a zero is the right answer for that row. A blank or a dash is often more honest than a zero, because a zero looks like a real measurement and drags averages down. Whichever you choose, write the rule once and use it everywhere, so the sheet behaves the same in every column.
Continue with [the errors lesson](/learn/errors-iferror), [the IS functions](/learn/is-functions) and [the IFERROR page](/functions/iferror).