Spreadsheet Practice

How to replace #DIV/0! with 0 or a blank in Excel

Formula (Excel and Google Sheets)

=IFERROR(B2/C2,0)

Wrap the division in IFERROR: =IFERROR(B2/C2,0). For only the zero case, test the divisor: =IF(C2=0,0,B2/C2).

Result on the example
one result per row in D2, filled down to D6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.

How it works

Dividing by zero (or by an empty cell) gives #DIV/0!. IFERROR returns the division when it works and the second argument, 0, when it doesn't. That keeps totals and averages from breaking.

=IFERROR(B2/C2,0)

Result0.02333333333

  1. B2420

    The value in B2.

  2. C218,000

    The value in C2.

  3. B2/C20.02333333333

    The / operator divides.

  4. IFERROR(B2/C2,0)0.02333333333

    IFERROR: Returns a fallback when the value is any error.

    • valueB2/C20.02333333333
    • value_if_error0not used

In Google Sheets

The same formula works in Google Sheets: =IFERROR(B2/C2,0). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IF(C2=0,0,B2/C2)2.33%, 0.00%, 3.26%…Catches only the zero case, so any other problem (text in the cell, a deleted column) still shows as an error.
=IFERROR(B2/C2,"")2.33%, , 3.26%…Different resultShows a blank instead of 0, which reads better in reports. Blank instead of 0 for the rows with no impressions.

Common mistakes

  • IFERROR hides every error, including real mistakes. Prefer the IF(C2=0,…) version in models others rely on.
  • A 0 rate pulls an AVERAGE down. If "no data" should be left out of the average, return "" instead.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Replace #DIV/0! with 0 or a blank

In D2, divide clicks by impressions, showing 0 instead of an error when there are no impressions. Fill down to D6.

Questions

Is there a way to hide #DIV/0! without a formula?

Conditional formatting can make error cells look blank, but the error is still there and breaks calculations that use it.

How do I use IFERROR with VLOOKUP?

=IFERROR(VLOOKUP(…),"Not found"). For lookups, IFNA is safer because it catches only "not found".

Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.