Fixes / Excel · Checked

Excel #DIV/0! error: what it means and how to fix it

Short answer

#DIV/0! means an Excel formula tried to divide by zero or by an empty cell. Most often the divisor cell is blank because data hasn't been entered yet. Fill in the divisor, or make the formula wait for it: change =A2/A3 to =IF(A3,A2/A3,"") so the cell stays empty until A3 has a non-zero value.

What this message means

Excel shows #DIV/0! in a cell when the formula in that cell ends up dividing a number by zero. Excel counts an empty cell as zero, so a formula like =A2/A3 fails as soon as A3 is blank. The error also comes from functions that divide internally. AVERAGE, AVERAGEIF and AVERAGEIFS work out sum divided by count, so they return #DIV/0! when there are no numbers to average, and MOD(number, 0) returns it too. Any formula that refers to a cell showing #DIV/0!, such as a SUM over a column, shows the same error. The spreadsheet itself is not damaged. The formula just has nothing valid to divide by yet.

Common causes

CauseHow to tell
The divisor cell is empty or contains 0Click the error cell and read the formula bar. The cell after the / sign (for example A3 in =A2/A3) is blank or shows 0. This is common in template rows where the data hasn't been typed in yet.
AVERAGE, AVERAGEIF or AVERAGEIFS found no numbers to averageThe formula uses an AVERAGE function, and the range is empty or none of the cells match the criteria (for example a misspelled name, extra space or wrong date in the criteria).
Numbers are stored as textThe values look like numbers but are left-aligned, often with a small green triangle in the top-left corner of each cell. =ISTEXT(A1) returns TRUE. This usually happens with data copied from a website, PDF or another system, and changing the format to Number does not fix it.
The error is inherited from another cellThe formula has no division in it (for example =SUM(D2:D50)), but one of the cells it refers to shows #DIV/0!. Fixing that cell clears the error here too.
A formula divides by a result that works out to zeroThe divisor is another formula, such as =B2-C2 or =COUNTIF(...), that currently returns 0. Select that cell to check its value.

How to fix it

1. Find the divisor and give it a real value

  1. Click the cell that shows #DIV/0! and read the formula in the formula bar.
  2. Find the cell or expression after the / sign (or the range used by the AVERAGE function).
  3. If it is blank or 0 by mistake, type the correct number into it or point the formula at the right cell.
  4. If the divisor is itself a formula, click it and check why it returns 0.
  5. To follow the chain step by step, select the error cell and go to Formulas > Evaluate Formula, or Formulas > Error Checking > Trace Error, which draws arrows to the cells involved.

2. Make the formula wait until the divisor is filled in (IF)

  1. Select the cell with the error.
  2. Wrap the division in IF so it only runs when the divisor is not blank or zero. For =A2/A3, type =IF(A3,A2/A3,"") to leave the cell empty.
  3. To show 0 instead, use =IF(A3,A2/A3,0). To show a note, use =IF(A3,A2/A3,"Input needed").
  4. Press Enter, then drag the fill handle down to copy the formula to the rest of the column.

3. Hide the error with IFERROR (after the formula is known to be correct)

  1. Select the cell and change =A2/A3 to =IFERROR(A2/A3,0), or use "" instead of 0 to show a blank.
  2. For averages, use =IFERROR(AVERAGEIF(B2:B100,"East",C2:C100),"") so an empty match shows blank instead of the error.
  3. Copy the formula down the column.
  4. Keep in mind that IFERROR hides every error type, including #REF!, #NAME? and #VALUE!. Only add it once you have checked that the formula itself is right.

4. Convert numbers stored as text into real numbers

  1. Select the cells that hold the text numbers.
  2. If a green triangle appears, click the warning icon next to the selection and choose Convert to Number.
  3. If there is no triangle, go to Data > Text to Columns, choose Delimited, clear every delimiter checkbox and click Finish.
  4. Or type 1 into an empty cell and copy it. Then select the text numbers, choose Paste Special, pick Multiply and click OK.
  5. Check with =ISNUMBER(A1), which should now return TRUE. The AVERAGE or division formula should recalculate on its own.

5. Fix AVERAGEIF or AVERAGEIFS criteria that match nothing

  1. Compare the criteria text in the formula with the actual cell values. Look for different spelling, extra spaces or a text date compared with a real date.
  2. Remove stray spaces from the source data with =TRIM(A2) in a helper column, or retype the criteria to match exactly.
  3. Make sure the range and average_range cover the rows you expect (for example B2:B100 and C2:C100, not ranges that are offset from each other).
  4. If some groups really will have no data, wrap the formula in IFERROR as shown above.

6. Hide #DIV/0! in a PivotTable

PivotTables in Excel for Windows and Mac (Microsoft 365, 2021, 2019). Tab name is PivotTable Analyze in current versions, Analyze or Options in older ones.

  1. Click anywhere inside the PivotTable.
  2. Go to PivotTable Analyze > Options (or right-click the PivotTable and choose PivotTable Options).
  3. On the Layout & Format tab, tick For error values show.
  4. Leave the box empty to show a blank, or type 0 or a dash, then click OK.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

If the error keeps coming back, select the cell and use Formulas > Evaluate Formula to see exactly which step becomes a division by zero. Check whether the source data comes from an external link, Power Query or a copy-paste that brings the numbers in as text each time it refreshes. In that case, fix the data type at the source (in Power Query, set the column type to Decimal Number) instead of in the sheet. For a shared or company workbook, ask whoever built the template which cells are supposed to hold the divisor values. You can also post the formula and a sample of the data on Microsoft Q&A or the Microsoft Tech Community Excel forum.

Questions people ask

How do I make #DIV/0! show as 0 or blank in Excel?

Wrap the formula in IFERROR, for example =IFERROR(A2/A3,0) for zero or =IFERROR(A2/A3,"") for a blank cell. If you only want to catch divide-by-zero and leave other errors visible, use =IF(A3,A2/A3,0) instead.

Why does AVERAGE return #DIV/0! when my cells have numbers in them?

The numbers are most likely stored as text, which AVERAGE ignores, so it ends up dividing 0 by a count of 0. Convert them with Convert to Number or Data > Text to Columns. Changing the cell format alone does not work.

Is it better to use IF or IFERROR to fix #DIV/0!?

IF only handles the case you test for (a blank or zero divisor), so real mistakes like #REF! or #NAME? still show up. IFERROR is shorter but hides every error type, so use it only on formulas you have already checked.

Why does my SUM show #DIV/0! when there is no division in it?

SUM returns an error if any cell in its range contains an error. Find the cell in the range that shows #DIV/0! and fix it there, or add IFERROR to the formulas in that column.

Does a blank cell always cause #DIV/0!?

A truly empty cell counts as zero, so dividing by it gives #DIV/0!. A cell that holds an empty text string from a formula ("") gives #VALUE! instead, and a cell with a space in it also gives #VALUE!.

Sources

Related errors