GuidesGoogle Sheets

How to use IFERROR in Google Sheets

Learn how to use IFERROR in Google Sheets to catch formula errors like #N/A and #DIV/0! and replace them with clean, custom values.

Quick answer

To use IFERROR in Google Sheets, wrap any formula with IFERROR(value, value_if_error), replacing errors like #N/A, #DIV/0!, or #REF! with a custom message or value. For example, type =IFERROR(A1/B1, 0) to return 0 instead of a division error, keeping your spreadsheet clean, professional, and free of confusing error codes for anyone viewing it.

Steps at a glance

  1. Click the cell where the error-handled result should appear.
  2. Type =IFERROR( followed by your original formula or cell reference.
  3. Add a comma, then type the value to show if an error occurs.
  4. Close the parenthesis and press Enter to run the formula.
  5. Drag the fill handle to apply IFERROR to other rows.
  6. Test with sample data that triggers errors to confirm results.

Summary

IFERROR is a Google Sheets function that catches formula errors such as #N/A, #DIV/0!, and #REF! and replaces them with a value you choose. It wraps around any existing formula, so instead of showing a confusing error code, your sheet displays a blank cell, a zero, or a custom message.

Step-by-step guide

  1. Step 1

    Identify the formula that may produce an error

    Look at the formula in your sheet that sometimes returns #N/A, #DIV/0!, #REF!, #VALUE!, #NAME?, or #NUM!. Common culprits include VLOOKUP searches for missing values and division formulas where the denominator can be zero. Note the exact formula and cell reference before you edit it.

  2. Step 2

    Click the cell and start typing IFERROR

    Select the cell containing the formula, or an empty cell if you're building a new one. Type =IFERROR( to begin the function. This tells Google Sheets to evaluate the next argument and check whether it returns an error.

  3. Step 3

    Enter your original formula as the first argument

    Paste or type your existing formula right after the opening parenthesis, such as A2/B2 or VLOOKUP(A2,Sheet2!A:B,2,false). This is the value IFERROR checks first; if it calculates normally, that result is what displays in the cell.

  4. Step 4

    Add a comma and specify the fallback value

    After your formula, type a comma followed by what you want displayed if an error occurs, like 0, a blank pair of quotes "", or a custom message such as "Not found". This second argument is optional — leaving it out returns a blank cell on error instead.

  5. Step 5

    Close the parenthesis and press Enter

    Complete the formula with a closing parenthesis, for example =IFERROR(A2/B2, 0), then press Enter. Google Sheets recalculates the cell and shows either the correct result or your chosen fallback value instead of an error code.

  6. Step 6

    Copy the formula down or across your range

    Click the cell with your IFERROR formula, then drag the small fill handle in the bottom-right corner down the column or across the row. This applies the same error-handling logic to every cell in the range, which is especially useful after you sort a table or add new rows of data.

  7. Step 7

    Combine IFERROR with lookups for cleaner results

    For dropdown-driven lookups or reports, nest a VLOOKUP inside IFERROR so missing matches return a clean message instead of #N/A. If you're new to lookups, review how to use VLOOKUP in Google Sheets before combining it with IFERROR, and pair it with data validation if the lookup values come from a dropdown.

Why this matters

You're building a report with VLOOKUP or division formulas, and a few rows show #N/A or #DIV/0! because of missing data. Wrapping those formulas in IFERROR replaces the ugly error codes with a clean value, so your spreadsheet stays readable and shareable with clients or teammates.

Frequently asked questions

  • What's the difference between IFERROR and IF combined with ISERROR?

    IFERROR is a shorter, single-step way to catch any error type and return a fallback value. Using IF with ISERROR requires writing the formula twice and is more prone to typos, so IFERROR is generally the simpler choice for most spreadsheets.

  • Can IFERROR catch every type of error in Google Sheets?

    Yes, IFERROR catches all standard error types, including #N/A, #DIV/0!, #REF!, #VALUE!, #NAME?, and #NUM!. It does not distinguish between them, so if you need a different message per error type, you'll need a more advanced formula combining IFERROR with IFNA or IF.

  • What happens if I leave out the value_if_error argument?

    If you omit the second argument, IFERROR returns a blank cell whenever the first argument produces an error. This works well when you simply want errors hidden rather than replaced with specific text or numbers.

Related guides

Create interactive demos like this one — free, no coding required.

Start for free →