How to Fix ‘#DIV/0!’ Error in Google Sheets

In financial models, inventory trackers, and statistical dashboards built in Google Sheets, formulas that calculate ratios, percentage changes, or unit costs frequently encounter the dreaded calculation halt: ‘#DIV/0! Error: Evaluation of function DIVIDE caused a divide by zero error’. A single cell generating this error not only looks untidy on professional reports, but it also cascades downstream, breaking all dependent formulas (such as SUM, AVERAGE, or summary pivot tables). Here is why division by zero errors occur in Google Sheets and the best methods to handle them cleanly.

What Causes the #DIV/0! Error in Google Sheets?

In mathematics and computer spreadsheet engines, dividing any numeric value by zero is mathematically undefined. Google Sheets triggers the #DIV/0! flag under several common operating conditions:

  • Blank or Empty Divisor Cells: Google Sheets evaluates a completely blank referenced cell as a numeric value of 0 when passed into a division calculation (e.g., =A2/B2 when B2 is empty).
  • Cells Containing Literal Zeros: The divisor cell explicitly holds a value of 0 (such as zero units sold or zero baseline revenue).
  • Text Formatted as Zero: Invisible space characters or text strings inside the denominator cell that coerce to zero during mathematical operations.
  • AVERAGE or AVERAGEIF Across Empty Ranges: Using statistical functions like AVERAGEIF where no cells meet the specified criteria, resulting in zero matching elements to divide by.

Step 1: Wrap Formulas in IFERROR for Clean Presentation

The most straightforward method to mask division errors and display a fallback value (such as a dash, zero, or blank space) is wrapping your formula inside IFERROR.

  1. Select the cell currently displaying #DIV/0!.
  2. If your existing formula is:
    =A2 / B2
  3. Update it to display a blank cell when division by zero occurs:
    =IFERROR(A2 / B2, "")
  4. Alternatively, return a numeric zero (0) or an informative string:
    =IFERROR(A2 / B2, 0)
    =IFERROR(A2 / B2, "N/A")
  5. Press Enter and drag the fill handle down across your column.

Step 2: Use Logical IF Checks to Test Divisors Preemptively

While IFERROR catches all calculation errors, using an explicit IF check is best practice when you specifically want to prevent division only when the denominator is zero or unpopulated.

  1. To verify whether the denominator cell contains a value greater than zero before dividing:
    =IF(OR(B2=0, ISBLANK(B2)), "", A2 / B2)
  2. Alternatively, use the concise conditional test:
    =IF(B2 > 0, A2 / B2, 0)
  3. This ensures that genuine formula errors (such as #REF! or #VALUE!) remain visible for auditing, while benign zero divisions are cleanly suppressed.

Step 3: Handle #DIV/0! Inside Dynamic ARRAYFORMULA Columns

When applying calculations across entire columns with ARRAYFORMULA, unpopulated rows at the foot of your sheet automatically evaluate as empty zeros, creating hundreds of #DIV/0! errors.

  1. In your top header formula row, enter:
    =ARRAYFORMULA(IF(ISBLANK(A2:A) + (B2:B=0), "", A2:A / B2:B))
  2. This restricts array calculations strictly to rows where the denominator is populated and non-zero, keeping unused spreadsheet rows completely spotless.

Step 4: Resolve #DIV/0! in AVERAGE and AVERAGEIF Functions

The AVERAGE function divides the sum of matching values by the count of matching items. If the count is zero, it throws #DIV/0!.

  1. To protect an AVERAGEIF query from returning errors when no matches exist:
    =IFERROR(AVERAGEIF(A2:A50, "Completed", B2:B50), 0)
  2. This guarantees your summary scorecards and KPI dashboards render clean numeric values without calculation interruptions.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.