Excel IFERROR for Multiple Cells: Formulas and Tips

Formulas & functions · Updated November 20, 2026

To handle errors across multiple cells in Excel, use IFERROR in the first output cell and fill the formula down or across; for a compatible range calculation, you can also use a formula that spills results into neighboring cells. For example, with values in A2:A100 and B2:B100 and results starting in C2, enter =IFERROR(A2/B2,"Check input") in C2, then copy it down.

What IFERROR does in Excel

IFERROR checks a calculation or value and returns one result when it works and a replacement result when it produces an error. Its syntax is IFERROR(value, value_if_error). The first argument is the expression Excel evaluates; the second is what Excel displays if that expression returns an error.

It can catch common Excel errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NUM!, #NAME?, and #NULL!. It does not correct the underlying cause. Instead, it replaces the visible error with a result you choose, such as a note, a zero, or an empty text string. That distinction matters: hiding an error can make a report look cleaner, but it can also conceal incorrect data or a broken reference.

IFERROR is not a special worksheet setting that you switch on for a whole range. Usually, each output cell contains its own formula, and you copy that formula to the other rows or columns. Excel adjusts relative cell references as it copies the formula, so each row can test its own inputs.

Apply IFERROR to multiple rows with fill

This is the most straightforward approach when a worksheet already has a row-by-row calculation. With numerators in A2:A6, denominators in B2:B6, and output cells beginning at C2, enter =IFERROR(A2/B2,"Check input") in C2. If A2 is 12 and B2 is 3, the result is 4; if B2 is 0, the result is Check input.

  1. Select C2 and enter the formula, then press Enter.
  2. Select C2 again and point to the small square at the lower-right corner of the selection, called the fill handle.
  3. Drag the fill handle down to the last row that needs a result. Double-clicking it can fill down alongside a neighboring column of continuous data.
  4. Check a few rows, especially rows with blank or unusual inputs, to make sure the replacement result is appropriate.

You can also select the formula cell and the cells below it, then use Home > Fill > Down. On Windows, Ctrl+D fills selected cells down. In Excel for Mac, Control+D is commonly the Fill Down shortcut; keyboard settings can affect shortcuts, so the ribbon command is a dependable alternative. Excel for the web supports filling formulas by dragging the fill handle, though the exact interface can vary slightly.

When copying horizontally, Excel updates relative references by column. If a reference must remain fixed while the formula moves, make it absolute with dollar signs. For example, with an exchange rate in F1, amounts in A2:A6, and converted values in B2:B6, use =IFERROR(A2*$F$1,"Check input") in B2 and copy down. The dollar signs keep the rate reference fixed at F1.

Use a spilling formula for a range calculation

In Microsoft 365 for Windows, a formula that operates on ranges can return multiple results and spill them into cells below. With numerators in A2:A6, denominators in B2:B6, and C2:C6 empty, enter =IFERROR(A2:A6/B2:B6,"Check input") in C2. Excel evaluates each pair and spills the results down: if the inputs are 12 and 3, 8 and 2, 9 and 0, 20 and 4, and 10 and 5, the outputs are 4, 4, Check input, 5, and 2.

Spilled formulas can be convenient because a single formula manages the range calculation. The cells where results would appear must be clear; otherwise, Excel may show a #SPILL! error. You cannot edit an individual result inside the spill range independently: change the formula in its top-left cell or adjust the source data instead. Dynamic array behavior is available in Microsoft 365 and newer Excel versions, but older perpetual versions may require a different approach. Excel for the web also supports dynamic arrays.

A range formula is suitable when you are calculating a consistent operation over matching ranges. It is not a universal way to wrap every unrelated formula already present in a worksheet. For a mixed set of existing calculations, copying an IFERROR version into a separate output range is generally easier to inspect.

Choose what should replace an error

The replacement should tell the truth about the data, not merely make the worksheet look tidy. A zero can be appropriate if an error genuinely means there is no amount to include. It is misleading if the calculation is unknown, because a zero may then be included in averages, totals, charts, or decisions as if it were a real measurement.

  • A short message: Use text such as Check input when someone should investigate the row. With quantities in A2:A20, prices in B2:B20, and totals in C2:C20, enter =IFERROR(A2*B2,"Check input") in C2 and fill down.
  • Zero: Use it only when zero is a meaningful substitute. With costs in A2:A20, units in B2:B20, and cost per unit in C2:C20, enter =IFERROR(A2/B2,0) in C2 and copy down. A zero denominator then displays 0, even though the division itself is undefined.
  • Empty text: To make an error appear visually blank, use =IFERROR(A2/B2,""). With inputs in A2 and B2 and the result in C2, this returns an empty-looking cell if the division fails.

An empty text result is not the same as a genuinely empty cell: the cell still contains a formula. For example, COUNTA may count a formula cell that displays an empty string. If later formulas must distinguish truly missing values from formulas returning blank text, plan for that difference.

IFERROR or IFNA: which should you use?

IFERROR catches any error returned by its first argument. That broad behavior is useful when several kinds of calculation errors should receive the same fallback. However, it can also hide errors you would rather diagnose. IFNA is narrower: it replaces #N/A but leaves other error types visible.

For instance, a lookup that returns #N/A when a key is absent may need a friendly Not found message, while a #REF! error in the same formula may indicate a broken range that needs repair. With lookup keys in A2:A20, a lookup table in F2:G100, and results in B2:B20, enter =IFNA(XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100),"Not found") in B2 and fill down. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; for older Excel versions, use a lookup function available in that version. If every error truly should be replaced, IFERROR is the broader choice.

A useful rule is to avoid wrapping an entire complicated calculation in IFERROR automatically. First determine which errors are expected and which point to a problem. A broad fallback is appropriate for a polished report only when the workbook still has a way to identify and investigate unexpected failures.

Common problems and how to fix them

  • The formula appears unchanged in every row. Check whether the references were made absolute unintentionally. If a row-specific reference should move from A2 to A3 as you fill down, it should be relative rather than written as $A$2.
  • The formula returns a blank even though you expected a number. The calculation may be producing an error that IFERROR is concealing. Temporarily remove IFERROR or change the fallback to a visible message to identify the affected rows.
  • The formula spills into blocked cells. Clear the cells in the expected spill area, or use the fill-down method. A value, another formula, or merged cells in the destination area can prevent a spill.
  • A zero appears where the result should be missing. Review whether zero is a meaningful fallback. A message or empty text may better distinguish an invalid calculation from a real zero.
  • Only some rows are handled. Check the fill range and confirm that the formula was copied through the last data row. Excel tables can help: formulas entered in a calculated column generally fill through the table rows and extend to new rows.

Also check for errors in the source data and references. IFERROR does not repair a deleted cell, a misspelled function, text where a number is needed, or an invalid denominator. If the fallback is concealing several different issues, use a visible message while investigating, then choose a final replacement that fits the worksheet’s purpose.

When to use a helper column or a table

A helper column is often the clearest way to apply error handling without changing source data. Keep the original values and calculations intact, and place the IFERROR result in a separate column with a descriptive heading such as Adjusted total or Status. This makes it easier to compare the original inputs with the displayed outcome and to find rows that need attention.

If the data is formatted as an Excel table, adding a formula to a table column can make the process easier to maintain. In Microsoft 365 for Windows, select a cell in the data and choose Insert > Table, or use Ctrl+T, then confirm whether the table has headers. Enter the formula in the first data cell of a new calculated column. Excel commonly propagates that formula down the column and applies it to rows added later. On Mac, the Insert tab also includes Table; Excel for the web offers table creation as well. Table behavior is useful, but still verify the result after pasting irregular data or editing the table structure.

Frequently Asked Questions

Can I apply IFERROR to an entire selected range at once?
Not as a setting that changes all existing formulas in place. The usual method is to create IFERROR formulas in an output range and fill them down or across. A range-based formula can spill results for a compatible calculation in Microsoft 365, but it does not automatically rewrite unrelated formulas already stored in cells.

How do I copy an IFERROR formula to many cells?
Enter it in the first output cell, then drag the fill handle, use Home > Fill > Down, or select the destination cells and press Ctrl+D on Windows. Check that relative references shift as intended and that the formula reaches every required row.

Can IFERROR hide only #DIV/0! errors?
IFERROR replaces all error types, not just division-by-zero errors. If you need to handle one specific condition, test that condition directly or consider whether IFNA applies to a lookup that can return #N/A. Avoid broad error handling when other errors should remain visible.

Why does IFERROR return a blank instead of zero?
The second argument determines the replacement. If it is an empty text string, the cell looks blank. If you want a zero, set the second argument to 0; first make sure that zero accurately represents the situation rather than disguising missing or invalid data.

Does IFERROR work in older Excel versions?
IFERROR is available in Excel 2007 and later, including Microsoft 365. Dynamic array formulas that spill ranges require a newer version, such as Microsoft 365 or Excel 2021 and later, so use a copied formula in older editions if range spilling is unavailable.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.