Handling and Blanking Out Excel Errors
Handling and blanking out Excel errors is one of the fastest ways to make a spreadsheet look finished. A column full of #DIV/0! and #N/A makes people stop trusting the numbers, even when most of them are right.
The trick is knowing which errors to hide and which ones to fix. Some errors are expected, like a lookup that has no match yet or a month with no sales to divide by. Others mean a formula is broken, and hiding them only buries the problem.
Below you will find what every Excel error means, the functions that handle them cleanly (IFERROR, IFNA, NA, and XLOOKUP), how to hide errors when you print, and a free error fixer that writes the safer formula for you.
What Each Excel Error Means
Every error code tells you something specific. Read it before you hide it.
| Error | What Causes It | How to Fix It |
|---|---|---|
| #DIV/0! | A formula divides by zero or by an empty cell. | Check the divisor. If a blank or zero is expected (a new month with no sales yet), test for it before dividing. |
| #N/A | A lookup could not find the value, or a value is marked as not available. | Check spelling, extra spaces, and number-stored-as-text mismatches. If a missing match is normal, use IFNA or the XLOOKUP if_not_found argument. |
| #VALUE! | The formula uses the wrong type of data, such as text where a number belongs. | Look for text, spaces, or dates stored as text in the cells the formula uses. |
| #REF! | The formula points to a cell that no longer exists, usually after rows, columns, or sheets were deleted. | Undo the deletion or rewrite the reference. Do not hide this one, it means the formula is broken. |
| #NAME? | Excel does not recognize text in the formula, often a misspelled function or a missing quote. | Check the function name and put text inside double quotes. |
| #NUM! | A number in the formula is invalid or too large, or an iterative function cannot find a result. | Check for impossible math, such as the square root of a negative number. |
| #NULL! | Two ranges were joined with a space (the intersection operator) but do not overlap. | Use a comma to separate ranges or a colon to define one range. |
| #SPILL! | A dynamic array formula cannot spill its results because cells in the way are not empty. | Clear the cells below or beside the formula. |
| #CALC! | The calculation engine hit a problem, such as FILTER returning no results. | For FILTER, add the if_empty argument, for example "" or "None". |
| ##### | Not a real error. The column is too narrow to show the value, or a date or time is negative. | Widen the column, or check the date math. |
Microsoft's guide on how to avoid broken formulas walks through the most common causes in more detail.
Fix It or Hide It?
| Situation | Best Move |
|---|---|
| A lookup has no match yet, and that is normal | Hide it with IFNA or XLOOKUP's if_not_found |
| Dividing by a blank or zero that will fill in later | Test the divisor with IF |
| Missing data in a chart series | Return NA() so the point is not plotted as zero |
| #REF! or #NAME? | Fix the formula. Never hide these. |
| #VALUE! from text in a number column | Clean the data first |
How to Use IFERROR
IFERROR checks a formula and returns something else if the result is any error.
Syntax: =IFERROR(value, value_if_error)
- value is the formula to check.
- value_if_error is what to show instead, such as
""for a blank,0, or a message in quotes.
Example: =IFERROR(A2/B2, "") shows a blank cell instead of #DIV/0! when B2 is empty or zero.
IFERROR is convenient, but it catches every error type. If someone deletes a column and the formula turns into #REF!, IFERROR hides that too, and you may not notice for weeks. Use it when you are sure the only possible error is an expected one. Full details are in Microsoft's IFERROR function reference.
How to Use IFNA for Lookups
IFNA works like IFERROR but only catches #N/A. That makes it the safer choice for lookups, because a missing match gets handled while real mistakes still show up.
Syntax: =IFNA(value, value_if_na)
Example: =IFNA(VLOOKUP(D2, A:B, 2, FALSE), "Not found") returns "Not found" when D2 is not in column A. If the formula is broken some other way, you still see the real error. See Microsoft's IFNA function reference.
Use XLOOKUP's Built-In Not Found Option
If you have XLOOKUP, you do not need a wrapper at all. Its fourth argument sets what to return when there is no match.
Example: =XLOOKUP(D2, A:A, B:B, "Not found")
This is the cleanest option in current versions of Excel, and it is easier for the next person to read. Microsoft's XLOOKUP reference covers the other optional arguments.
Blank Out Divide-by-Zero the Precise Way
Instead of wrapping a division in IFERROR, test the divisor directly:
=IF(B2=0, "", A2/B2)
An empty cell counts as zero in this test, so this handles both blanks and zeros. The advantage over IFERROR is that only the divide-by-zero case is hidden. If A2 contains text by mistake, you still see #VALUE! and can fix it.
How to Use NA on Purpose
The NA function returns the #N/A error deliberately. That sounds strange, but it is useful for marking data that does not exist yet.
Syntax: =NA()
Example: =IF(A2="", NA(), A2) returns #N/A when A2 is blank and the value otherwise.
The main reason to do this is charts. If a formula returns a blank-looking "" or a 0 for future months, a line chart can drop to zero and make your trend look like a crash. Excel does not plot #N/A as zero, so the line does not dive. In current versions of Excel you can also control how those points appear under Select Data > Hidden and Empty Cells, including a "Show #N/A as an empty cell" option, as explained in Microsoft's guide to displaying empty cells and #N/A in a chart.
To keep the #N/A cells from looking messy in the table itself, use conditional formatting to turn their font the same color as the background.
Free Excel Error Fixer
Free Excel Error Fixer
Pick the error you are seeing, paste the formula that causes it, and choose what you want to show instead. The fixer explains the cause and writes the safer formula for you. Nothing you enter is saved or sent anywhere.
What It Means
Safer Formula
More Ways to Handle Errors Without Changing Formulas
Sum a column that contains errors
A regular SUM returns an error if any cell in the range has one. AGGREGATE can skip them: =AGGREGATE(9, 6, B2:B100). The 9 tells it to sum, and the 6 tells it to ignore error values.
Find every error on a sheet
Go to Home > Find & Select > Go To Special, choose Formulas, and leave only Errors checked. Excel selects every cell with an error so you can review them at once.
Hide errors with conditional formatting
Select the range, create a new rule using the formula =ISERROR(A1) (adjusted to the top-left cell of your range), and set the font color to match the background. The formulas stay the same, and the errors disappear from view.
Hide errors when printing
Open Page Setup, go to the Sheet tab, and change Cell errors as to <blank>. Errors still show on screen, but the printout is clean.
Trace where an error starts
On the Formulas tab, use Error Checking to jump between errors and Evaluate Formula to step through a formula one piece at a time and see exactly where it breaks.
Best Practices for Handling Errors
- Fix before you hide. Only hide errors you expect and understand.
- Prefer the narrowest tool. IF for divide-by-zero, IFNA or XLOOKUP for lookups, IFERROR last.
- Use NA() for chart data so missing values are not plotted as zeros.
- Clean your inputs. Extra spaces and numbers stored as text cause most #N/A and #VALUE! errors.
- Leave a note. If you hide errors in a shared file, say so in a comment so nobody is surprised later.
Keep Your Business Numbers Out of Fragile Spreadsheets
Spreadsheets are great for analysis, but they get fragile when a whole team depends on them for hours, projects, and payroll. Updoot is business management software that keeps time tracking, projects, invoicing, HR, and KPIs in one system, and every tool exports to Excel or Google Sheets when you want to analyze the numbers yourself. Plans start at $5 per user per month.
Final Thoughts
Excel errors are messages, not just clutter. Read what each one is telling you, fix the ones that mean something is broken, and blank out only the ones you expect. Use IF for divide-by-zero, IFNA or XLOOKUP for lookups, NA() for chart data, and IFERROR when nothing narrower fits. Your spreadsheets will look cleaner and stay trustworthy.
Opens in Google Drive. View and download for free.
Frequently Asked Questions About Excel Errors
How do I blank out errors in Excel?
Wrap the formula in IFERROR and use two double quotes as the second argument, for example =IFERROR(A2/B2, ""). For lookups, IFNA or the XLOOKUP if_not_found argument is safer because it only hides the not found case.
What is the difference between IFERROR and IFNA?
IFERROR catches every error type, including broken references and typos. IFNA only catches #N/A, so it handles a missing lookup match while still showing real mistakes.
How do I stop VLOOKUP from showing #N/A?
Wrap it in IFNA, for example =IFNA(VLOOKUP(D2, A:B, 2, FALSE), "Not found"). If you have XLOOKUP, use its fourth argument instead: =XLOOKUP(D2, A:A, B:B, "Not found").
Why would I use the NA function on purpose?
NA returns #N/A deliberately to mark data that does not exist yet. Excel does not plot #N/A as zero, so charts do not drop to the bottom for future or missing periods.
How do I hide errors when printing in Excel?
Open Page Setup, go to the Sheet tab, and set Cell errors as to blank. Errors still show on screen but not on the printout.
Should I hide #REF! errors?
No. #REF! means a formula points to a cell, row, column, or sheet that no longer exists. Hiding it buries a broken formula. Fix the reference instead.