Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 93 ยท Logical and Conditional Formulas

Handling Errors and Blanks in Formulas

Learn how to handle errors and blank cells in Excel formulas with IFERROR, IFNA, ISBLANK, ISNUMBER and ISERROR without hiding real mistakes in your data.

Every real spreadsheet meets bad input sooner or later: a division by an empty cell, a lookup for a product that was discontinued, a number that was pasted in as text. When that happens, Excel shows an error such as #DIV/0!, and because errors spread to every formula that uses the cell, one bad row can break a whole report.

Excel gives you a family of functions to deal with this: IFERROR and IFNA replace an error with something friendlier, while ISBLANK, ISNUMBER and ISERROR let you test a value before you use it. Used well, they make a sheet robust. Used carelessly, they hide real bugs, so the second half of this lesson is about when not to use them. If you need a refresher on what each error means, see Formula Errors and How to Fix Them.

Excel itself cannot run on this site. The formulas below are real Excel formulas, and the live examples run them on a small stand-in engine written in JavaScript. It parses = formulas, follows cell references, and propagates errors the way Excel does. The engine is not Excel's real code, but for these functions it behaves the same way.

Two ways to handle trouble

React Catch the error after it happens (IFERROR, IFNA).
Prevent Test the input first (ISNUMBER, ISBLANK, IF).

Prevention is more precise. Reacting is shorter.

1 / 2

IFERROR: replace any error

The syntax is =IFERROR(value, value_if_error). Excel evaluates the first argument. If it is any error, you get the second argument; otherwise you get the first argument's result.

TEXT
=IFERROR(B2/C2, 0)

If C2 is zero, B2/C2 would be #DIV/0!, so the cell shows 0 instead. IFERROR catches all seven classic errors: #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. (Spill errors are also errors, so they are caught too.)

The second argument can be anything: a number, text such as "n/a", an empty string "" to show nothing, or even another formula.

The live example below has a small price table. Column D is the raw formula, column E wraps it in IFERROR, and column F uses IF to test for zero before dividing. Read the output and compare the three columns, row by row. The last two lines add up each column, which shows how one error poisons a total.

JS Raw, IFERROR and IF versions side by side

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Look at what happened:

  • Pens work in every column.
  • Books have zero units. The raw formula gives #DIV/0!, IFERROR turns it into 0, and the IF version says no units.
  • Bags have the text n/a in the units column. The raw formula gives #VALUE! and so does the IF version, because it only checked for zero. IFERROR hides it.
  • Lamps have a blank units cell. A blank counts as zero in arithmetic, so it behaves like Books.
  • The total of column D is an error because one error in a range poisons SUM. The total of column E works, but it quietly treats the unknown prices as 0.

That last point is the heart of the matter, and we return to it below.

IFNA: only catch "not found"

#N/A means "value not available". It is the error that lookup functions return when they find no match, and some people also type NA() on purpose to mark a gap in chart data. IFNA(value, value_if_na) replaces only that one error and lets every other error through.

TEXT
=IFNA(VLOOKUP(D2, A2:B100, 2, FALSE), "unknown code")

Here a missing code shows unknown code. But a typo in a function name (#NAME?) or a deleted column (#REF!) still shows up as an error, which is exactly what you want: those are mistakes in the formula, not missing data. Lookups are covered properly in VLOOKUP and Its Limits.

The IS functions: test before you use

The IS functions return TRUE or FALSE and never produce an error themselves (apart from ISERROR and ISNA, which exist to look at errors):

Function Returns TRUE when...
ISBLANK(A1) the cell is truly empty
ISNUMBER(A1) the value is a number (dates and times count; numbers stored as text do not)
ISTEXT(A1) the value is text
ISERROR(A1) the value is any error
ISNA(A1) the value is #N/A
ISERR(A1) the value is any error except #N/A

Combine them with IF to build a precise guard:

TEXT
=IF(ISNUMBER(B2), B2 * 1.2, "check price")

If you are new to IF, see IF and Nested IF. The next example shows the subtle cases: an empty cell is blank, but a cell holding the formula ="" is not; the text "5" looks like a number but fails ISNUMBER. The same example also compares IFERROR and IFNA on a lookup with a typo in the function name (VLOKUP).

JS IFERROR vs IFNA, and the IS functions

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In the first block, the code P9 does not exist. Both IFERROR and IFNA show missing, which is right. In the last row the function name is misspelled, so Excel returns #NAME?. IFERROR covers up the typo and shows missing, which makes the sheet look healthy while every lookup is broken. IFNA lets the #NAME? through, so you notice it immediately.

Blanks: empty, zero and empty text are three different things

Blank handling causes more confusion than errors do:

  • An empty cell is ISBLANK and counts as 0 in arithmetic and as "" in text joins.
  • A cell with ="" (or a formula that returns empty text) looks empty but is not ISBLANK. Test it with A1="", which is TRUE for both kinds.
  • A cell with 0 is a number. Formats can hide a zero, but it is still there.

When you want "show nothing if the input is missing", the usual pattern is:

TEXT
=IF(B2="", "", B2 * C2)

Use B2="" rather than ISBLANK(B2) when the input cell might have been cleared by a formula or by a paste from another system. Be careful though: a formula that returns "" is text, so =A1*2 will give #VALUE! if A1 contains ="". Downstream formulas must cope with that, or you can return NA() instead of "" so charts skip the point and IFNA can catch it.

Don't hide real bugs

An IFERROR around a whole formula is a promise: "whatever goes wrong, show this". That is dangerous for three reasons:

  1. It hides mistakes. A wrong range, a misspelled name or a deleted sheet all become 0 or "", and the totals look plausible but are wrong.
  2. It turns "unknown" into a number. 0 is a real value; averages and totals use it. A missing price is not a price of zero.
  3. It makes audits harder. Tools like Evaluate Formula (see Auditing and Debugging Formulas) have nothing to show once the error is gone.

Safer habits:

  • Prefer the narrow tool. Use IFNA for lookups, and an explicit IF test for division.
  • Catch only the part that can fail. Write =B2 + IFERROR(C2/D2, 0) rather than wrapping the whole formula.
  • Use a visible fallback. Text like "check" or "no price" stands out; 0 does not. Add a conditional-format rule or a count of problem rows (=SUMPRODUCT(--ISERROR(D2:D500))) so someone sees them.
  • Fix the data when you can. Data validation (later in the course) stops bad entries at the source.
  • Add handlers last. Build the formula without them, make sure the errors you see are the errors you expect, then add the guard.

An IFERROR that returns 0 can make a wrong report look right. When a total matters, count the rows that errored and show that count next to the total.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function replaces ONLY the #N/A error and lets other errors through?

    IFNA catches only #N/A, which is what lookups return when nothing matches. IFERROR catches every error type, and ISERROR only tests, it does not replace anything.
  2. 2. A cell contains the formula ="". What does ISBLANK return for it?

    ISBLANK is TRUE only for a truly empty cell. A formula that returns empty text is not blank, although it looks empty. Test both cases with A1="".
  3. 3. Why can wrapping a whole formula in IFERROR(..., 0) be risky?

    IFERROR catches every error, including #NAME? and #REF! that signal a broken formula. The 0 it returns is then used in totals as if it were real data.
  4. 4. Cell A1 holds the text 5 (stored as text). What does ISNUMBER(A1) return?

    ISNUMBER checks the data type, not how the value looks. A number stored as text is text, so the result is FALSE.

Summary

  • IFERROR(value, fallback) replaces any error; IFNA(value, fallback) replaces only #N/A.
  • The IS functions (ISBLANK, ISNUMBER, ISTEXT, ISERROR, ISNA) return TRUE/FALSE and let you test input before you use it.
  • Empty cells, zeros and empty text ("") behave differently; test with A1="" to cover blank and empty text together.
  • Guard precisely: IFNA for lookups, an IF test for division, and IFERROR only around the part that can fail.
  • Use visible fallbacks and count the problem rows, so errors are never silently turned into zeros.

Next you will meet the most famous lookup function: VLOOKUP and Its Limits.