Fix #DIV/0! Error in Excel

This error occurs when a formula tries to divide a number by zero or an empty cell.

Why this happens

  • Dividing by zero explicitly
  • Dividing by an empty cell
  • Average of empty range

How to fix it

  • Use IF to check for zero before dividing
  • Use IFERROR to handle the error
  • Ensure divisor cells have values

High-impact #DIV/0! checks

  • Check whether the denominator is zero, blank, or produced by another formula returning zero.
  • Use IF or IFERROR to show a friendly result only after confirming the division is expected.

Quick answer: div/0 excel

  • Fix #DIV/0! by wrapping the formula in IFERROR, testing the divisor with IF(divisor=0,...), or replacing blank cells used as divisors. Find the zero denominator with Evaluate Formula before hiding the error.
  • Blank cell as divisor often causes #DIV/0! — use IF(B2="","",A2/B2).
  • Pair with [IFERROR](/functions/iferror/) after fixing the root cause.
  • Audit with Formulas → Evaluate Formula before blanket IFERROR.

#DIV/0! handling

  • Guard with IF: =IF(denominator=0,"",numerator/denominator).
  • Use IFERROR only after validating business logic.
  • Check for blank cells used as divisors in SUMPRODUCT ratios.

Functions that often show this error

  • AVERAGE: Returns the average (arithmetic mean) of the arguments.
  • MOD: Returns the remainder after a number is divided by a divisor.
  • AVERAGEIF: Returns the average of cells that meet a single criterion.
  • LOG: Returns the logarithm of a number to a specified base.
  • AVERAGEIFS: Returns the average of cells that meet multiple criteria.
  • CORREL: Returns the correlation coefficient between two data sets.
  • ATAN2: Returns the arctangent from x and y coordinates.
  • QUOTIENT: Returns the integer portion of a division.
  • COT: Returns the cotangent of an angle in radians.
  • COTH: Returns the hyperbolic cotangent of a number.
  • SEC: Returns the secant of an angle in radians.
  • CSC: Returns the cosecant of an angle in radians.
  • CSCH: Returns the hyperbolic cosecant of a number.
  • PERCENTOF: Calculates the percentage that a subset represents of a total.
  • FORECAST: Calculates a future value based on existing values using linear regression.

Frequently asked questions

  • What does #DIV/0! error mean in Excel? #DIV/0! (Divide by Zero) error occurs when a formula attempts to divide by zero or an empty cell. This is mathematically undefined, so Excel displays this error. It commonly appears in percentage calculations, averages, and ratio formulas when the denominator is zero or blank.
  • How do I fix #DIV/0! error in Excel? Fix #DIV/0! by checking for zero before dividing: =IF(B1=0, 0, A1/B1) or use IFERROR: =IFERROR(A1/B1, 0). For AVERAGE on potentially empty ranges, use =IF(COUNT(range)=0, 0, AVERAGE(range)). Always validate that divisor cells contain non-zero values.
  • Why does AVERAGE return #DIV/0! error? AVERAGE returns #DIV/0! when the range contains no numeric values to average (all empty or all text). AVERAGE divides the sum by the count, and if count is zero, you get #DIV/0!. Use =IF(COUNT(range)=0, "", AVERAGE(range)) to handle empty ranges gracefully.
  • How do I hide #DIV/0! errors in my report? Hide #DIV/0! errors by: 1) Wrapping formulas in IFERROR: =IFERROR(formula, ""); 2) Using conditional formatting to make error cells white text; 3) Using IF to check before dividing; 4) In newer Excel, use =IF(ISERROR(formula), "", formula). Choose the method based on whether you want blank, zero, or custom text.
  • What's the best practice to prevent #DIV/0! errors? Best practices: 1) Always validate divisors with IF(divisor=0, alternative, calculation); 2) Use data validation to prevent zero entry in divisor cells; 3) Use IFERROR as a safety net; 4) For percentages, handle the case where total is zero; 5) Document expected behavior when division isn't possible.