Excel's IFERROR formula replaces a failed calculation with a result you choose, while leaving successful results alone. Use =IFERROR(A2/B2,0) for zero on error, or replace zero with a short message.
Choose a replacement that fits the job. Zero looks like a real numeric result to anyone totaling the column. A blank can hide a broken reference. Get the calculation right before deciding how errors should look.
In this guide
Wrap the existing calculation in IFERROR
Start with the formula that already does the work. Suppose A2 contains completed units and B2 contains the number of hours. Enter =A2/B2 in C2 first and check that a normal pair of inputs produces the result you expect.
With 120 in A2 and 6 in B2, the answer is 20. With zero in B2, division produces #DIV/0!. IFERROR lets the formula return something else for that error without changing the successful calculation.

Enter this formula in C2 and press Enter:
=IFERROR(A2/B2,"Check hours")
A valid division still returns 20, while a division error shows Check hours. The text goes in straight double quotation marks; cell references and numbers don't.
The general structure is =IFERROR(value,value_if_error). The first argument, or input, is the calculation Excel checks. The second is what you want it to return if that calculation produces an error. You need both.
This works in supported desktop Excel versions on Windows and Mac, and in Excel for the web. Enter the formula in a worksheet cell. You don't need a macro, an add-in, or an administrator account.
Choose between a message, zero, and a blank
Use a message when a person needs to fix an input. The Check hours example explains the next action more clearly than an empty cell. Choose wording that makes sense to the person maintaining the sheet.
Use =IFERROR(A2/B2,0) only when you want zero as the replacement, or fallback. Other formulas can include that number in totals and averages. An error replaced by zero can change the meaning of a report even when it looks tidy.
To display an empty-looking result, enter this version:
=IFERROR(A2/B2,"")
Those two adjacent quotation marks represent empty text. The cell still contains a formula; it hasn't become an unused cell.
That difference matters when another formula checks for blank inputs. The Excel IF not blank guide explains how an empty-looking formula differs from a genuinely empty cell. Choose the test to match the result you've created.
I'd use an explanatory message while building a sheet, then choose a numeric or blank replacement only after checking the results. It keeps unfinished calculations visible while you're still making decisions about the workbook.
Copy the formula without changing the wrong references
Once C2 behaves correctly, copy it into the next row. The relative references change from A2/B2 to A3/B3, so each row calculates using its own inputs. Check the first copied formula before filling a long column.
When the divisor is a fixed value in F1, use:
=IFERROR(A2/$F$1,"Check rate")
The dollar signs hold the F1 reference in place as the formula is copied. A missing lock can make later rows divide by unrelated cells.
Use the formula-copying steps when the source references move unexpectedly. IFERROR handles the resulting error value, but it doesn't decide whether a reference should have moved in the first place.
Test one ordinary row and one row that should produce the fallback. Then inspect a copied row near the end of the filled range. That catches a correct first formula followed by incorrect references further down the sheet.
Keep missing matches separate with IFNA
IFERROR handles several error types, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!. Use it when you want the same replacement for every error.
For a lookup, however, a missing match can deserve different treatment from a damaged reference or misspelled function. Use IFNA when the replacement should apply only to #N/A, so you can still see and fix other errors.
For example, use this formula for an exact lookup:
=IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"Not found")
It returns Not found when the lookup has no match, while another error type remains visible. Keep the original lookup working before adding its replacement message.
Microsoft's IFERROR reference lists the errors it catches, while its IFNA reference describes the narrower alternative. Choose the function that catches the error you want to replace.

Check an unexpected blank or a formula that won't enter
If a cell shows the fallback unexpectedly, try the original formula without IFERROR in a spare cell. The error helps you work out whether to fix the number you're dividing by, the lookup range, a cell reference, or the kind of data entered.
If Excel refuses the formula, check the parentheses, separators, and quotation marks. A text message needs matching straight double quotes. Some regional settings use semicolons instead of commas between function arguments.
With semicolons, the formula looks like this:
=IFERROR(A2/B2;"Check hours")
Use the separator your copy of Excel accepts. Changing the replacement result won't fix a formula Excel can't read.
For a cell displaying the typed formula, check its opening = and whether its format is Text. Set the format to General, reopen the cell for editing, and press Enter to make Excel calculate it.
If a shared workbook gives the wrong result, send its owner the formula, sample inputs, error, and desired replacement. Use an example without confidential data. That gives them a calculation to repair instead of another hidden error.
The Short Version
Wrap a working calculation in IFERROR and choose a meaningful fallback. Quote text, use zero only when intended, and choose IFNA for missing matches alone. Inspect the original error before hiding an unexpected result.
Checked November 16, 2026.







