Excel Pivot Table Divide by Zero Error: Causes and Fixes

Charts & pivot tables · Updated November 19, 2026

An Excel PivotTable’s #DIV/0! usually comes from a division formula in its source data or a calculated field whose denominator is zero; it is not normally caused by the PivotTable’s Sum or Count summary itself. Find the source of the division, correct or deliberately handle zero denominators, and refresh the PivotTable. Hiding the error changes only how it looks, not the underlying calculation.

What the error means in a PivotTable

Excel returns #DIV/0! when a formula attempts to divide a number by zero or by an empty cell treated as zero. A PivotTable can show the error when an error is present in its source data or when a calculated field performs a division that has a zero denominator. The visible cell may be far from the original formula, especially when the PivotTable groups records by month, product, or another category.

A useful distinction is whether the PivotTable is summarizing values or calculating a ratio. Summing revenue, counting records, or adding units does not itself require division. An Average summary does involve a count, but it generally returns a blank when there are no eligible numeric values; it does not ordinarily create a divide-by-zero error merely because a group has no records. Check for an actual error in the source, a calculated field, or a formula adjacent to the PivotTable before changing the summary setting.

Where to look for the cause

Start with the exact cell displaying #DIV/0!, then identify whether it belongs to the PivotTable or is a separate worksheet formula. Click the cell and inspect the formula bar. If the cell is inside the PivotTable, check the source range and any PivotTable calculated fields. If it is outside the PivotTable, inspect its own formula and references to PivotTable totals.

Common causes include:

  • A source-data formula divides a value by a quantity, hours, headcount, or other field that is sometimes zero or blank.
  • A PivotTable calculated field divides one source field by another, and some records have a zero denominator.
  • A source value already contains #DIV/0!, which remains an error when included in a PivotTable summary.
  • A formula beside the PivotTable divides by a displayed total that becomes zero after filters or slicers are applied.
  • The PivotTable has not been refreshed after source formulas or values were corrected, so its cached results do not reflect the latest data.

Filters matter because a denominator that was positive for the full data set can become zero for a particular month or category. Reproduce the affected filter selection and inspect the underlying records rather than assuming every group has the same data.

Trace the error back to the source data

Use the PivotTable’s drill-down feature to see the records behind an affected summary. Double-click a value cell in the PivotTable to create a new worksheet containing the contributing source rows. The available detail depends on the PivotTable’s source and settings, but this is often a quick way to identify a particular zero denominator or an error-producing record. Check the original source as well, especially if the detail sheet does not include the formulas or context you need.

For a source range that is an Excel table, use the table’s filter buttons to inspect the denominator column for zero values and blanks. You can also filter the source formula column for errors. When the source is a large range, a temporary filter or a helper column can isolate the records without changing the PivotTable itself. Confirm that the denominator is genuinely invalid for the intended calculation: zero units may mean “not applicable,” or it may signal missing data that needs correction.

Fix a division formula in the source

If the error originates in a worksheet formula, decide what the result should mean when the denominator is zero. A blank can be more honest than zero when a rate cannot be calculated; returning zero implies a valid rate of zero. For example, with revenue in B2 and units in C2, place this formula in D2 to leave the rate blank when units are zero:

=IF(C2=0,"",B2/C2)

If B2 is 120 and C2 is 0, the formula returns a blank. If B2 is 120 and C2 is 6, it returns 20. Fill the formula down the source column, then refresh the PivotTable. When the source is an Excel table, a formula entered in the calculated column normally fills down automatically. Choose a different fallback only when it matches the meaning of the report; replacing every divide-by-zero error with zero can make averages and totals misleading.

Another possible approach is IFERROR, but it catches all errors, not only division by zero. That can conceal unrelated problems such as a misspelled reference or invalid source value. An IF test for the denominator makes the intended exception clearer when zero is the specific condition you expect.

Handle a PivotTable calculated field

A standard PivotTable can contain a calculated field that performs arithmetic using fields from the source data. Select the PivotTable, then use PivotTable Analyze > Fields, Items & Sets > Calculated Field on Windows. In the dialog, choose the calculated field and inspect its formula. Field names must correspond to the PivotTable’s source fields; use the dialog’s Insert Field control rather than typing worksheet cell references.

For a calculated field using source fields named Profit and Units, a formula that returns zero for records with no units is:

=IF(Units=0,0,Profit/Units)

This avoids the error for those records, but the zero is a reporting choice, not a universal correction. If the PivotTable summarizes that calculated field by Sum, it adds the per-record results. That may not equal the overall profit-per-unit ratio. If you need a ratio of totals, calculate total profit divided by total units instead of averaging or summing individual ratios. A calculated field also differs from a measure in a Data Model PivotTable, which uses a different calculation system; do not assume the standard calculated-field dialog applies to every PivotTable.

Check averages, blanks, and genuine zero values

A zero is a numeric value; a blank is not the same thing. For an Average summary, Excel includes numeric zeroes in the calculation and generally ignores blank cells. That means changing a source error to zero can reduce an average, while changing it to blank can remove that record from the average. Neither choice is automatically right. Use the meaning of the measure and document the rule where readers could mistake a blank for missing reporting.

Also check the PivotTable’s Value Field Settings. Right-click a value, choose Value Field Settings, and confirm the selected summary calculation, such as Sum, Count, or Average. Changing the summary from Average to Sum may alter the result but will not repair a source formula that still contains #DIV/0!. If the error appears in a calculated field, changing the summary can likewise leave the underlying error in place.

Replace the displayed error without fixing it

If a report must remain readable while the source issue is being investigated, Excel can display substitute text for error values in a PivotTable. Right-click inside the PivotTable, select PivotTable Options, open the Layout & Format tab, and enable For error values show. Enter a label such as Not calculated, or leave the replacement blank if that is appropriate. The wording is important: a label makes clear that the result is unavailable, whereas a displayed zero can be mistaken for a measured result.

This setting is a presentation workaround. It does not change the source formula, correct the denominator, or make calculations that depend on the error reliable. Keep a note of the underlying problem and return to the data or calculated field to fix it. The PivotTable Options dialog is available from the right-click menu in Excel for Microsoft 365 on Windows; the ribbon and dialog arrangement can vary somewhat on Mac, but the error-display option is in the PivotTable options.

Refresh after making a correction

After changing source values or formulas, select a PivotTable cell and choose PivotTable Analyze > Refresh. On Windows, Alt+F5 refreshes the selected PivotTable; Ctrl+Alt+F5 refreshes all PivotTables and connections in the workbook. On Mac, use the Refresh command on the PivotTable Analyze tab; Windows shortcuts may not apply, and keyboard shortcuts can depend on the Mac keyboard and Excel settings.

If the error persists, confirm that the PivotTable source includes the corrected rows and that you refreshed the correct PivotTable. For a PivotTable based on an external connection or Data Model, check its refresh status and source as well. A regular worksheet refresh cannot correct a formula or measure that still divides by zero, and a PivotTable may continue to show the same result if the relevant records remain in the source.

Calculate a ratio of totals correctly

One common reporting mistake is to average individual percentages when the intended metric is a combined rate. Suppose one order has profit of 100 on 10 units and another has profit of 20 on 1 unit. The average of the two per-order rates is (10 + 20) / 2 = 15, but the combined profit per unit is 120 / 11, or about 10.91. These answer different questions.

If the PivotTable shows total profit in B5 and total units in C5, use a cell outside the PivotTable for the combined rate:

=IF(C5=0,0,B5/C5)

With B5 equal to 120 and C5 equal to 11, the result is about 10.91. If C5 is zero, this example returns zero as a deliberate fallback; use a blank or a clear label instead if zero would misrepresent the result. Because the formula is outside the PivotTable, verify that it still points to the correct totals when the report layout or filters change. For a report that will be reused with changing categories, a Data Model measure may be a more robust way to define a ratio of aggregated values.

Frequently Asked Questions

Why does my PivotTable show #DIV/0! after filtering?
Filtering can leave a category with a zero denominator, or leave an adjacent formula dividing by a total that is now zero. Check the records for the filtered group and inspect whether the error cell is inside or outside the PivotTable.

Will refreshing a PivotTable fix #DIV/0!?
Only if the source or formula has already been corrected. Refreshing updates the PivotTable from its source; it does not change a formula that still divides by zero.

Should I replace #DIV/0! with zero or a blank?
Use the value that matches the meaning of the measure. Zero means the calculated result is zero; blank or a label such as Not calculated indicates that no valid rate is available.

Can an Average summary cause the error?
An Average summary normally returns a blank when there are no eligible numeric values. A visible #DIV/0! more often points to an error in source data, a calculated field, or another formula in the report.

How do I find which source rows caused the error?
Double-click the affected PivotTable value to display its contributing records, if detail is available. Inspect those rows’ denominator values and formulas, then correct the source and refresh.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.