#DIV/0! 错误：Excel 与 Google Sheets 的原因和修法

Which versions this applies to

The results below were measured in Google Sheets on November 12, 2026. Microsoft documents the same #DIV/0! error for Excel when a number is divided by zero. I did not re-test these rows in desktop Excel today.

Cause 1: A literal zero in the denominator

=1/0         → #DIV/0!
=10/0        → #DIV/0!
=10/2        → 5

The control row, 10/2, returned 5, so the sheet was evaluating division normally. The unformatted error text in this file was Function DIVIDE parameter 2 cannot be zero.

Cause 2: A blank cell or an empty string

A denominator that looks empty is still treated as zero:

=10/C4        → #DIV/0!     (C4 is blank)
=10/C5        → #DIV/0!     (C5 is ="")

Both were #DIV/0!, with the same “parameter 2 cannot be zero” message. If a later row has not been filled in yet, a rate or unit-cost formula pointing at that row will already show #DIV/0!. That is often a missing input, not a broken function name.

You can tell a blank from a real zero if you need the distinction:

=IF(C8="","blank",B8/C8)    → blank     (C8 was empty)
=IF(C6=0,"n/a",B6/C6)       → n/a       (C6 was the number 0)

In this file a blank cell compared equal to "", so the first formula returned blank and never divided. The second formula saw a numeric zero and returned n/a.

Cause 3: Zero divided by zero, MOD, and QUOTIENT

These also came back as #DIV/0!, not #NUM! or #VALUE!:

=0/0              → #DIV/0!
=MOD(5,0)         → #DIV/0!     (Function MOD parameter 2 cannot be zero.)
=QUOTIENT(5,0)    → #DIV/0!     (Function QUOTIENT parameter 2 cannot be zero.)

0/0 is undefined, and Sheets reports it the same way as 1/0. MOD and QUOTIENT are still division underneath; a zero divisor is not a #VALUE! type problem. For the difference between #VALUE! and this error, see #VALUE! Error in Excel and Google Sheets.

A text zero does not compare equal to 0

I stored the character 0 as text in C11. ISTEXT(C11) was TRUE. Division still failed, because Sheets coerced the text to a number:

=1/C11                    → #DIV/0!
=IF(C11=0,"is zero",...)  → not equal
=IF(VALUE(C11)=0,"is zero",...)  → is zero

C11=0 was false, so a guard written as IF(C11=0,"n/a",1/C11) would still divide and return #DIV/0!. That is the pattern I hit when the zero had been typed as text. VALUE(C11)=0 caught it. ISNUMBER on the real numeric zero in C6 was TRUE; use that check if you want to be sure the denominator is a number before you compare it to 0.

Fix it with IF, and be careful with IFERROR

When C6 was a numeric zero and B6 was 10:

=IF(C6=0,"n/a",B6/C6)         → n/a
=IFERROR(B7/C7,"n/a")         → n/a

Both hide #DIV/0!. I prefer the IF form around a simple ratio, because it states the condition I actually care about. IFERROR also replaces #NAME?, #VALUE!, #REF!, and #N/A. If the real problem is a typo in the function name, IFERROR will hide it. That trade-off is covered in IFERROR vs IFNA.

IFNA is the wrong wrapper here. #DIV/0! is not #N/A.

A slightly stricter ratio, if blanks and zeros should both show as n/a and a text denominator should stay visible as a problem:

=IF(OR(C6="",C6=0),"n/a",B6/C6)

I tested the two branches separately (IF(C8="","blank",...) and IF(C6=0,"n/a",...)), not this combined OR in one cell. The idea follows directly from those two results; if the denominator can also be text, add ISNUMBER or VALUE as in the previous section.

Excel and Google Sheets differences

Microsoft’s documentation describes #DIV/0! the same way: a formula is dividing by zero or by a cell that is empty. I am not quoting an Excel-only result from this run. In both products, fixing the denominator is the real repair; wrapping the formula is only a display choice.

Common mistakes

• Assuming a blank denominator will be skipped. 10 divided by a blank was #DIV/0!.
• Writing IF(C11=0,"n/a",1/C11) when C11 holds the text 0. The comparison was false, and the division still returned #DIV/0!.
• Replacing every #DIV/0! with IFERROR. That also hides #NAME? from a typo, which is a different error (#NAME? Error).
• Treating 0/0 as #NUM!. It was #DIV/0! in this file.
• Looking at MOD or QUOTIENT and expecting a different error for a zero divisor. Both were #DIV/0!.

FAQ

Why do I get #DIV/0! when the denominator looks empty?

A blank cell and a cell with ="" were both treated as zero in division. Fill in the input, or guard with IF(C="","",A/C) so the formula stays quiet until the denominator arrives. That guard returned blank in this test (I used the label "blank" so I could see which branch ran).

Is 0/0 a different error from 1/0?

Not here. Both returned #DIV/0!.

Should I use IFERROR to hide #DIV/0!?

You can. IFERROR(10/0,"n/a") returned n/a. Use IF(C=0,...) when you only want to handle a zero denominator, and leave other errors visible. IFNA will not catch #DIV/0!.

Why did IF(C=0,“n/a”,A/C) still show #DIV/0!?

In this file, that happened when C was the text "0", not the number 0. C=0 was false and the formula divided anyway. Confirm ISNUMBER(C) or compare VALUE(C)=0.

Does MOD or QUOTIENT return #DIV/0! too?

Yes. MOD(5,0) and QUOTIENT(5,0) were both #DIV/0!, with a function-specific message that parameter 2 cannot be zero.

Will this look the same in Excel?

Microsoft documents #DIV/0! for a zero or empty divisor in Excel. I did not re-run these cells in desktop Excel today, so I am not quoting Excel’s wording of the error message.

Related tutorials

• IFERROR vs IFNA: Which Should You Use for Lookups?
• #VALUE! Error in Excel and Google Sheets: Causes and Fixes
• #NAME? Error in Excel and Google Sheets: Causes and Fixes
• Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide

Live-test findings

Google Sheets, November 12, 2026. 1/0, 10/0, 10 over a blank, 10 over ="", 0/0, 1 over the text "0", MOD(5,0), and QUOTIENT(5,0) all returned #DIV/0!. 10/2 returned 5. IF(C6=0,"n/a",B6/C6) returned n/a when C6 was a numeric zero (ISNUMBER was TRUE). IFERROR(B7/C7,"n/a") also returned n/a. IF(C8="","blank",B8/C8) returned blank for an empty cell. For a text "0", ISTEXT was TRUE, IF(C11=0,...) went to the “not equal” branch, and IF(VALUE(C11)=0,...) returned “is zero”.

Sources

• Microsoft Support: How to correct a #DIV/0! error: https://support.microsoft.com/en-us/office/how-to-correct-a-div-0-error-3a5a18a9-8d80-4ebb-a908-39e759a009a5
• Microsoft Support: MOD function: https://support.microsoft.com/en-us/office/mod-function-d64c47f3-aafd-4b2e-ae5d-c16ee3180576
• Microsoft Support: QUOTIENT function: https://support.microsoft.com/en-us/office/quotient-function-9f7bf099-2a18-4282-8fa4-65290cc99dee
• Google Docs Editors Help: Error messages, #DIV/0!: https://support.google.com/docs/answer/3093342