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

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

**Excel:** `=IFERROR(B2/C2,0)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Campaign | Clicks | Impressions | Click rate |
| Spring sale | 420 | 18000 |  |
| Newsletter | 0 | 0 |  |
| Retargeting | 310 | 9500 |  |
| New launch | 95 | 0 |  |
| Brand search | 880 | 12400 |  |

Formula in D2, filled to D6: `=IFERROR(B2/C2,0)`

Result: 2.33%; 0.00%; 3.26%; 0.00%; 7.10%

## 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.

## Other ways

- `=IF(C2=0,0,B2/C2)`: Catches only the zero case, so any other problem (text in the cell, a deleted column) still shows as an error.
- `=IFERROR(B2/C2,"")`: Shows 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.

## 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".

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/replace-div0-error-with-zero