Excel PivotTable Average #DIV/0! Error: Causes and Fixes

Errors & fixes · Updated November 19, 2026

An Excel PivotTable showing #DIV/0! for an Average usually has an error in its source data or in a calculated field; the PivotTable is surfacing that error rather than repairing it. Find the source calculation that divides by zero, decide how those rows should be treated, correct or exclude them, and then refresh the PivotTable.

What the PivotTable error means

#DIV/0! means an Excel formula tried to divide a number by zero, or by a cell that is blank and therefore treated as zero in the calculation. For instance, a revenue-per-unit formula can fail on a row where the unit count is zero. A PivotTable that summarizes those results as Average can carry the error into the relevant group or total.

The word Average can make the problem seem like a flaw in the PivotTable’s averaging method. Usually it is not. The PivotTable can calculate an average from valid numeric source values, but an error value is not a normal number it can average. A PivotTable setting that displays replacement text for errors may make the report look cleaner, but it does not make the underlying calculation valid.

Start by checking whether the error is in the original data, in a formula column, or in a PivotTable calculated field. Those cases need different fixes. Also decide what a zero denominator means for the question you are answering: it may indicate an invalid record, or it may be a meaningful case that should remain visible as a separate category.

Find where the error originates

Begin with the source table rather than changing the PivotTable’s number format. Locate the field used in the Values area and inspect its original rows. If the values come from formulas, look for rows where a denominator is zero, blank, or produced by another calculation that has failed. A source error may be hidden if the column is filtered or the PivotTable groups many records together.

  1. Click a cell showing #DIV/0! in the PivotTable and note its row and column labels. This helps identify the affected group.
  2. Go to the source data and filter or search the relevant calculated column for #DIV/0!. If the source is a Table, use its filter menu; otherwise, use Find or inspect the formula column.
  3. Check any calculated fields in the PivotTable as well. In Excel for Microsoft 365 on Windows, select the PivotTable and look under PivotTable Analyze > Fields, Items, & Sets for the calculated-field commands.
  4. After correcting the cause, refresh the PivotTable and confirm both the affected group and the grand total.

For a quick check, temporarily add the suspected source field to the PivotTable’s Rows area or examine the underlying records. Depending on the PivotTable layout, double-clicking a value cell can also create a new sheet with the source records behind that value. This is useful for tracing a group, but it is not a substitute for checking the complete source column: other groups may contain separate errors.

Fix a divide-by-zero in the source data

If the error comes from a row-level ratio, decide what should happen when the denominator is zero before changing the formula. A blank result is often appropriate when the ratio is undefined and should not be included in an average. Returning zero is appropriate only if zero is genuinely the correct value for the question; otherwise, it can pull the average downward.

For example, suppose revenue is in column B and units are in column C, with the calculation in D2. With revenue in B2 and units in C2, enter =IF(C2=0,"",B2/C2) in D2. If B2 is 120 and C2 is 0, the result is an empty string; if C2 is 4, the result is 30. Fill the formula down the source column, then refresh the PivotTable.

This formula specifically handles a zero denominator. It does not repair unrelated errors in B2 or C2. A broader error-handling formula such as IFERROR can suppress other errors too, so use it only when hiding those errors is intended. Suppressing an error can make a report less informative if a bad source value should instead be investigated.

There is an important detail with empty results: a formula returning "" is not the same thing as a genuinely empty cell. PivotTable behavior can depend on how the result is brought into the cache and summarized. Check the resulting average and record count after refreshing. If invalid rows must be reliably excluded, consider filtering them out of the source or transforming the data so the invalid result is a true blank or null rather than assuming that every empty-string formula will be treated identically.

Choose whether to exclude, show, or replace invalid rows

There is no universally correct replacement for #DIV/0!. Choose a treatment that matches what the metric means, and make that choice consistent across the report:

  • Exclude the row from the average when the measure is undefined for that record, such as revenue per unit when there were no units. Filter the record out or make the measure blank in a way that the PivotTable treats as blank.
  • Keep the row visible as an exception when a zero denominator signals a data-quality issue that needs follow-up. Add a status field or an exception report instead of silently hiding the error.
  • Use zero only when zero is the meaningful value. For example, if a business rule explicitly defines a particular outcome as zero, document that rule; do not use zero merely to eliminate an error message.
  • Show an error label when readers need to know that the result is unavailable or invalid. A clear label such as Not calculable can be more useful than a blank, provided the PivotTable and its downstream calculations handle it as intended.

Be especially cautious with averages. Excluding a row changes the population being averaged, while replacing its result with zero changes both the values and the average. If the report is used to compare teams, products, or time periods, apply the same rule to every group and state the exclusion in a note or accompanying documentation.

Check the PivotTable’s own calculations

If the source column contains no errors, inspect the PivotTable’s calculation design. A calculated field that divides one field by another can fail when the denominator is zero for a group or calculation. In that case, changing the source formula may not help; revise the calculated field’s logic or make the denominator valid for every group it evaluates.

Also check whether the Values field is actually set to Average. Right-click a value in the PivotTable, choose Value Field Settings, and inspect Summarize Values By. This setting controls the aggregation, but changing Sum to Average will not cure a source error. It is useful to verify it because a field can be summarized differently from what its label or report layout suggests.

A related issue is the difference between an average of row-level ratios and a ratio of totals. If each row has revenue per unit and the PivotTable summarizes that field by Average, it calculates an average of the individual row results. That is not generally the same as total revenue divided by total units. For example, averaging two per-row rates of 10 and 30 gives 20, while combining the underlying records may produce a different overall rate if the rows have different unit counts. Decide which measure answers the business question before changing the PivotTable.

Refresh the PivotTable and manage displayed errors

Once the source or calculated field is corrected, refresh the report. In Excel for Microsoft 365 on Windows, right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. To refresh every PivotTable connection in the workbook, use Data > Refresh All. If the source data has grown beyond the PivotTable’s existing source range, check Change Data Source or use an Excel Table as the source so newly added rows are included.

Excel also provides a display option for errors. Right-click inside the PivotTable, choose PivotTable Options, open Layout & Format, and use the For error values show option. You can enter display text there if you want a report to show a label instead of an error value. This is a presentation choice: it does not fix the source formula, change the denominator, or establish that the average is mathematically meaningful. Keep the source error visible somewhere if it requires investigation.

On Mac, the PivotTable tools and option dialogs can be arranged differently from the Windows ribbon, and available commands can vary by Excel version. Look for PivotTable Analyze or the PivotTable options dialog rather than assuming a Windows click path is identical. Excel for the web also has a different interface and may not expose every desktop PivotTable setting; when a specific option is missing, open the workbook in desktop Excel.

Prevent recurring errors in imported and calculated data

If the data comes from a recurring export, fix the issue at the stage where the data is prepared rather than repeatedly hiding it in the report. In Power Query, inspect the relevant column and use its error-handling or replacement options only after deciding how those records should be represented. Replacing errors with null can be suitable for an undefined measure, while retaining an exception flag can preserve visibility. Refresh the query and the PivotTable after changing the transformation.

For formula-based source tables, add checks that make invalid denominators easy to detect. A separate status column can identify records with zero or missing units, allowing the PivotTable to report both valid averages and exception counts. This is often better than returning a blank with no explanation: a clean-looking average can otherwise conceal how many records were omitted.

Finally, verify the PivotTable after every data refresh. Check a few underlying records, compare the number of valid observations with the number expected, and confirm the grand total’s interpretation. A PivotTable can be free of visible errors while still answering the wrong question if invalid rows were silently converted to zeros or excluded inconsistently.

Frequently Asked Questions

Why does my PivotTable show #DIV/0! when the source looks fine?
Check calculated fields and formula results, not only manually entered values. A formula may return an error in a filtered-out row, or a PivotTable calculation may divide by zero for a particular group. Trace the affected group back to its source records.

Does Average ignore #DIV/0! values?
No. A PivotTable cannot treat an error as an ordinary numeric observation, so the error can appear in the result. Correct, exclude, or deliberately handle the source error; do not assume Average will skip it as it skips blank cells in ordinary numeric averaging.

Should I replace #DIV/0! with zero?
Only if zero is the correct value according to the metric’s definition. If the ratio is undefined because there were no units, zero may distort the average. A blank, a visible exception, or exclusion may be more appropriate, depending on the reporting purpose.

Why does the error remain after I fix the formula?
The PivotTable may still be showing cached results. Refresh it after the source formula recalculates. Also verify that the corrected formula was filled through the full source range and that the PivotTable’s data source includes those rows.

Can I hide the error without fixing the source?
PivotTable Options includes a For error values show display setting, which can replace the visible error with text. That affects how the PivotTable looks, not the source calculation. Use it for presentation only when the underlying issue is understood and handled elsewhere.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.