Formula Errors

Prevent a #DIV/0! Error When Dividing by a Blank Cell in Excel

Jason MillerJason Miller Nov 18, 2026 871 views

Question details

Prevent a percentage formula from displaying #DIV/0! when its denominator is blank or zero.

Prevent a #DIV/0! Error When Dividing by a Blank Cell in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A formula divides the sum of C5, D5, and F5 by B5, but B5 may have no source data.
Observed behavior
Excel displays #DIV/0! when B5 is blank or zero.
Before you start

Check that B5 is the denominator and that C5, D5, and F5 are the cells you want to add. Select the cell containing the percentage formula so you can replace it.

Solution 1Recommended

Check the denominator with an IF formula

Use this option to leave the result visually blank when B5 is zero or empty while allowing other formula errors to remain visible.

1
Select the formula cell

In Excel, click the cell that currently contains the division formula.

2
Enter the IF formula

In the formula bar, enter =IF(B5=0,"",SUM(C5,D5,F5)/B5) and press Enter.

3
Check the result

Leave B5 blank or enter 0 to check that the result appears blank. Enter a nonzero value in B5 to calculate the ratio.

Check the denominator with an IF formula
The result is an empty string: The formula returns an empty string when B5 is blank or zero; the cell appears blank but still contains a formula.
WPS Spreadsheets

Prevent division errors in WPS Spreadsheets

Use the same IF formula in WPS Spreadsheets to show a blank result when the denominator is empty or zero.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Select the result cell: Click the cell where the percentage formula should appear.
  3. 3. Enter the formula: In the formula bar, enter =IF(B5=0,"",SUM(C5,D5,F5)/B5) and press Enter.
  4. 4. Test and format the result: Check the result with a blank, zero, and nonzero value in B5. If needed, select the result cell and choose Percentage from the Home tab.
Avoid visible #DIV/0! errors when a denominator is blank or zeroOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and a formula bar
microsoft office alternative - wps office

Frequently Asked Questions

Why does a blank denominator cause #DIV/0! in Excel?

Excel treats a blank cell used as a divisor as zero. Use =IF(B5=0,"",SUM(C5,D5,F5)/B5) to check B5 before dividing.

How can I show zero instead of a blank result?

Replace the empty string in the IF formula with 0: =IF(B5=0,0,SUM(C5,D5,F5)/B5).

Will IFERROR hide errors other than #DIV/0!?

Yes. IFERROR returns its specified fallback for any error in the wrapped calculation. Use the IF check on B5 if you only want to handle a blank or zero denominator.

How do I display the calculated ratio as a percentage?

Select the result cell and choose Percentage on the Home tab. For example, a calculated ratio of 0.25 will display as 25%.