Remove #DIV/0! Errors From an Excel PivotTable

To remove a #DIV/0! error from an Excel PivotTable, either fix the division formula in its source data and refresh the PivotTable, or hide the displayed error in PivotTable Options. Fixing the source is best when the calculation itself is wrong; hiding errors is useful when the source is intentional and you only want a cleaner report. Before changing anything, identify which source field or calculated value is producing the error.

What You’ll Need

The workbook containing the PivotTable and access to its source data.
Excel for Microsoft 365 on Windows. The steps use the desktop app; Excel for Mac has similar PivotTable options, while Excel for the web may not expose every option described here.
A decision about what a zero denominator should mean in your report: zero, a blank, or a label such as “N/A.” These choices can affect summaries and interpretation.

Step-by-Step

Find where the error is coming from. Click a cell showing #DIV/0! and inspect the PivotTable’s row and column labels. If possible, double-click the affected value. Excel may create a new worksheet with the underlying records that contribute to that value. Look for a source column containing a division formula, a zero or blank denominator, or an error value. If double-clicking does not reveal the records, select the PivotTable and use PivotTable Analyze > Change Data Source to identify the source range or table. Do not edit the PivotTable’s displayed cell directly: it is a report output, not an ordinary source cell.
Check whether the source is the cause. Go to the source worksheet and locate the field used in the PivotTable. If that field contains a formula such as a numerator divided by a denominator, inspect the denominator for zero or blanks. Excel returns #DIV/0! when a formula attempts to divide a number by zero, including a blank cell treated as zero. If the error appears only in a calculated field, measure, or calculated item, note that; the fix may need to be made in that calculation instead of in a source column.
Choose the right result for a zero denominator. If zero is a meaningful result for your report, return 0. If the ratio is undefined when the denominator is zero, a blank or an “N/A” label may communicate that more honestly. Avoid replacing every error with zero automatically: a zero can be mistaken for a genuine measured result, and broad error handling can conceal errors other than division by zero.
Update the source formula if appropriate. For example, with the numerator in A2 and denominator in B2, use =IF(B2=0,0,A2/B2) to return zero only when the denominator is zero. With the same cell layout, use =IF(B2=0,"",A2/B2) to return a blank for a zero denominator. If you want to handle any formula error, not just division by zero, the same layout can use =IFERROR(A2/B2,0); use this only when turning other errors into zero is also acceptable. Replace the example cell references with the actual cells in your source data.
Fill the corrected formula through the source records. If your source is an Excel Table, enter the revised formula in the relevant calculated column; Excel normally fills it down the table. For a normal range, copy or fill the formula down every applicable row, and check that the references adjust correctly. If only some rows use the formula, make sure you update all rows that can appear in the PivotTable. Also check whether the PivotTable’s source range includes the rows you changed.
Refresh the PivotTable. Return to the PivotTable, right-click anywhere inside it, and select Refresh. To update all PivotTables and connections in the workbook, use Data > Refresh All. Check whether the error has disappeared and whether totals still make sense. If the PivotTable is based on an Excel Table, new rows are generally included when the table expands; a fixed source range may need to be extended through PivotTable Analyze > Change Data Source.
Hide the error in the PivotTable when you do not want to change the source. Right-click inside the PivotTable and choose PivotTable Options. On the Layout & Format tab, select For error values show. Type a display value such as N/A or 0 in its box. To display a blank, leave the box empty while the option is selected. Click OK. This setting changes what the PivotTable displays; it does not repair the source formula or remove the underlying error.
Verify the report and save. Check several affected groups, not just the first cell, and confirm that subtotals and grand totals are still appropriate. If the output is shared or used for decisions, note whether displayed zeros or blanks mean “no result” rather than a measured zero. Save the workbook after confirming that the source and report behave as intended.

Common Mistakes

Typing over the error in the PivotTable. PivotTable values are generated from source records or calculations. Editing an output cell is not a dependable fix and may be blocked. Change the source or the PivotTable’s display option instead.
Using IFERROR without considering other errors. =IFERROR(A2/B2,0) also changes errors such as #VALUE! into zero. That can hide a bad input or a different formula problem. If the only condition to handle is a zero denominator, use an IF test for that condition.
Assuming a displayed blank means the problem is fixed. “For error values show” can make a report look clean while error values remain in the source. Other formulas, PivotTables, or exports may still use those source errors.
Returning text without checking the summary type. A formula that returns a blank string or “N/A” returns text for those rows. Depending on the PivotTable field and summary, text can be treated differently from numeric zero, affecting averages, counts, or other calculations.
Forgetting to refresh. A corrected source formula may not appear in the PivotTable until you refresh it. Conversely, changing the error-display option does not require changing each source record.
Confusing the two error-display settings. “For error values show” controls errors; “For empty cells show” controls empty cells. Changing the empty-cells setting will not, by itself, remove #DIV/0! values.

Tips

Keep the original calculation logic visible. A targeted formula such as =IF(B2=0,"",A2/B2) makes the zero-denominator case explicit, whereas blanket error handling can conceal unrelated problems.
If the numerator and denominator represent counts, consider whether a zero denominator should be blank, zero, or a label before choosing a formula. A ratio with no denominator may be undefined rather than equal to zero.
Use an Excel Table for source records when practical. Table columns typically copy formulas into new rows, and a PivotTable based on the table can use the expanded source after refresh.
Use a visible label such as “N/A” when readers might mistake a blank for missing data. Use a blank for a less cluttered report only when the meaning is clear to its audience.
If several PivotTables show the same error, check whether they share the same source or calculation. Each PivotTable may still have its own error-display setting, even if the underlying cause is shared.
For Excel for Mac, look for the PivotTable options on the PivotTable Analyze tab or in the PivotTable context menu; tab names and dialog placement can vary slightly by release. If you cannot find the error-display checkbox in Excel for the web, open the workbook in the desktop app.

Troubleshooting

The error remains after I changed the source formula. Confirm that you changed the field feeding the affected PivotTable value, filled the formula into every relevant record, and refreshed the PivotTable. If the source uses formulas that calculate from other cells, verify those inputs too. A PivotTable based on a fixed range may omit newer rows or point to a different sheet than expected; check PivotTable Analyze > Change Data Source.

The error-display option is unavailable or appears not to work. Make sure you opened PivotTable Options for the PivotTable itself, then check Layout & Format and select For error values show. Enter the display text in that option’s box. If the PivotTable uses the Excel Data Model or a connection, the error may come from a measure or the model; hiding the displayed error may not fix that calculation. Open the model or measure definition and inspect its division logic.

Only some cells change, or another PivotTable still shows the error. The PivotTables may have different options, sources, or calculations. Apply the display setting to each report that needs it, or repair the shared source and refresh each PivotTable. Also check whether the affected cells are genuinely error values or ordinary text that merely looks like an error.

The total changes after I replace errors with blanks or zero. That may be expected: blanks and zeros have different meanings in summaries. Compare the original numerator and denominator totals, decide how undefined ratios should be treated, and use the method that preserves the intended reporting logic. Do not select a display value solely because it makes the total look cleaner.

Frequently Asked Questions

Why does #DIV/0! appear in a PivotTable?
A source formula, calculated field, or model measure is dividing by zero or a blank denominator. The PivotTable can then display or aggregate that error. Trace the value to the responsible source or calculation before choosing a fix.

Can I replace #DIV/0! with zero without changing the source data?
Yes. In Excel for Windows, right-click inside the PivotTable, choose PivotTable Options, open Layout & Format, select For error values show, and enter 0. This changes the display, not the underlying source values.

Should I use IFERROR or IF to remove the error?
Use IF when you specifically want to handle a zero denominator and leave other errors visible. Use IFERROR when you have decided that any formula error should return the chosen fallback value. The fallback should reflect what the calculation means.

Will refreshing the PivotTable fix the error?
Refreshing updates the PivotTable from its source, but it does not correct a source formula that still divides by zero. Fix the cause first, then refresh. If the source has already been corrected, refreshing is often the step needed to update the report.

Can I hide the error only in one PivotTable?
Yes. The “For error values show” option is set through that PivotTable’s options. Other PivotTables can retain their current display settings, even when they use the same source data.

Does showing a blank remove the error from the workbook?
No. It hides the error in that PivotTable’s display. Repair the source formula or calculation if the underlying value must also be correct for other reports, formulas, or exports.

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.