#DIV/0! Error
Math's oldest rule: you can't divide by zero. When Excel tries and the divisor is 0 (or empty, which counts as 0), you get #DIV/0!. The fix is almost always guarding the formula with an IF or IFERROR wrapper.
Signals division by zero — either literal /0 or a divisor cell that's empty or contains zero. Also shows up in AVERAGE when the range has no numeric values, and in ratios when the denominator hasn't been populated yet.
What #DIV/0! means
Excel returns #DIV/0! any time your formula attempts to divide by zero. This includes direct division (=10/0), division by an empty cell (Excel treats blank as 0), and functions that internally divide — like AVERAGE, which divides the sum by the count. If there are no numeric values, count is 0, and AVERAGE returns #DIV/0!.
Unlike most errors, #DIV/0! is easy to prevent because you almost always know when a divisor might be empty. New rows that haven't been populated yet, ratio calculations on incomplete data, percentage calculations before the total is entered — all predictable. The pattern is the same in every case: check for zero before dividing.
💡 The universal #DIV/0! fix pattern
Wrap every division in =IFERROR(numerator/denominator, 0) or, more explicitly, =IF(denominator=0, 0, numerator/denominator). The first is shorter; the second is clearer. Both prevent #DIV/0! from ever appearing. Which to use is personal preference — but pick one and use it consistently.
The 4 root causes of #DIV/0!
Formula like =A1/B1 where B1 is 0. Straightforward math error. Most common in percentage calculations, ratios, and unit-cost formulas.
Formula like =A1/B1 where B1 is blank. Excel treats blank cells as 0 in arithmetic, so this triggers #DIV/0! just like literal zero.
AVERAGE internally divides sum by count. If the range contains only text or blanks, count is 0, so AVERAGE returns #DIV/0!. Same for MEDIAN, STDEV, and other statistical functions that internally divide.
MOD function returns the remainder after division. =MOD(10, 0) returns #DIV/0! — you can't compute remainder of division that can't happen. Less common but occasionally appears in time/date calculations.
5 real-world fix scenarios
Percent of total when total is 0
Calculating what percentage of revenue each region contributes. If total revenue is 0 (start of period, or all values pending), you get #DIV/0!.
Better for percentages: return 0% instead of 0 — =IFERROR(A2/SUM(A:A), 0) formatted as percentage displays "0%" which is visually cleaner.
AVERAGE returning #DIV/0! on filtered data
Your AVERAGE covers a range that's currently filtered to zero rows, or contains only text and blanks.
Check first: use COUNT to verify at least one number exists before averaging. Prevents the error at source.
Year-over-year growth when prior year is 0
New product launched this year had no revenue last year. Growth rate formula divides by 0.
Note: growth from 0 is mathematically undefined (technically infinite). "N/A" is more honest than 0 or 100%.
Cost per unit when quantity hasn't been entered
Rows waiting for data entry. Formula divides total cost by quantity — but quantity cell is still empty.
Choice of empty return: empty string ("") looks like a blank cell; 0 shows a real value. Depends on what your dashboard should communicate.
AVERAGEIF returning #DIV/0! when no matches
Averaging revenue only for West region rows, but there are no West rows yet.
Same fix works for: AVERAGEIFS, MEDIAN, and any statistical function that could receive an empty selection.
Prevention checklist
Eliminating #DIV/0! from your workbooks
- Wrap every division in IFERROR. Make it a habit.
=IFERROR(A/B, 0)or=IFERROR(A/B, "")is 4 extra characters that prevent every #DIV/0!. - Use IF for explicit intent.
=IF(B=0, "", A/B)is more readable and communicates that you specifically care about zero. - Set default values in template workbooks. If B is meant to be a quantity, initialize to 1 rather than blank. Prevents #DIV/0! in the first calculation.
- Add data validation on divisor cells. Restrict them to positive numbers only. Users can't accidentally enter 0 or leave blank.
- Verify AVERAGE ranges contain data. Wrap AVERAGE with COUNT check for empty-range protection.
- Return meaningful text for "N/A" cases. Growth from zero is undefined — "N/A" is clearer than 0.
- Test with edge cases. When you build a formula, plug in a 0, an empty cell, and text to see what happens. Fix each error before shipping.
#DIV/0! vs other errors
Version compatibility
#DIV/0! behaves identically across all Excel versions and Google Sheets. Fix patterns using IFERROR require Excel 2007+.
Related errors and resources
Frequently asked questions
Should I return 0 or empty string when catching #DIV/0!?
Depends on downstream usage. If other formulas reference this cell, return 0 (arithmetic works with 0 but breaks with empty string). If it's a display cell only, empty string ("") looks cleaner. For dashboards, consider returning specific text like "N/A" or "—" so viewers know it's intentionally missing.
Why does AVERAGE return #DIV/0! when I can see numbers in the range?
Because your "numbers" are stored as text. AVERAGE ignores text values silently and counts them as 0 rows. If all values are text-that-looks-like-numbers, count is 0, division fails. Fix with Data → Text to Columns → Finish to convert text-numbers to real numbers.
Is IFERROR always better than IF?
Not always. IFERROR catches EVERY error including bugs you'd want to see (typos → #NAME?, broken refs → #REF!). IF with an explicit zero check only handles the zero case, letting other errors surface. IF is safer for auditing; IFERROR is faster to write.
How do I calculate percent growth from zero?
Mathematically, growth from 0 is undefined (technically infinite). Common conventions: return "N/A" (most honest), return "100%" (interpret as "went from nothing to something"), or return blank. Never return 0 — that would suggest no growth, which is false.
Does hiding #DIV/0! affect other formulas?
Yes. If cell A2 shows a hidden #DIV/0!, formulas referencing A2 propagate the error unless they also have error handling. Cascading errors. Wrap error handling at the source, not just at the display.
What's a safer division pattern for large workbooks?
Define a named LAMBDA or use IFERROR consistently: =IFERROR(numerator/denominator, 0). In Excel 365, you can create a reusable LAMBDA: SafeDivide = LAMBDA(n, d, IFERROR(n/d, 0)), then call as =SafeDivide(A2, B2). Cleaner and consistent across the workbook.
How does Google Sheets handle #DIV/0!?
Identically to Excel. Same error text, same IFERROR handling, same IF pattern. IFERROR works in both. Google Sheets also has DIVIDE() function which returns the same error on zero — no special handling.
#DIV/0! shouldn't slow down your dashboard.
The Add-in scans every division in your workbook and automatically wraps risky formulas with IFERROR — one click, workbook-wide protection.
Get the Add-in →