Excel PivotTable: Hide #DIV/0! Errors Without Fixing Data

To hide #DIV/0! in an Excel PivotTable, select the PivotTable, open PivotTable Analyze > Options, choose the Layout & Format tab, check For error values show:, and leave its box empty. This changes how errors appear in that PivotTable; it does not correct the source data or the calculation causing them.

What the PivotTable error setting does

The For error values show: option tells Excel what to display in PivotTable cells that contain an error. If the option is checked and its text box is blank, Excel displays an empty-looking cell instead of an error such as #DIV/0!. You can also enter a replacement, such as “Not available,” if readers should know that the cell is not a normal numeric result.

This is a display choice, not an error-handling formula. The underlying calculation can still be invalid, and other formulas or reports that use the source data may still encounter the error. This distinction matters when a PivotTable is used for decisions or when someone expects its values to reconcile with a source worksheet.

The setting belongs to a particular PivotTable. If a workbook contains several PivotTables, set the option for each one where you want errors hidden. A PivotTable created by copying another one may inherit some settings, but check the new report rather than assuming the display option is in place.

Steps in Excel for Microsoft 365 on Windows

Click any cell inside the PivotTable that displays #DIV/0!.
On the ribbon, select PivotTable Analyze > Options. In some ribbon layouts, the Options command is a drop-down button on the PivotTable Analyze tab.
In the PivotTable Options dialog box, open Layout & Format.
Under Format, check For error values show:.
Leave the adjacent box empty to show a blank, or type a short replacement message.
Select OK and check the affected cells.

You can often open the same dialog by right-clicking a cell in the PivotTable and choosing PivotTable Options. If the error remains, confirm that you selected the PivotTable that contains it and that the checkbox is enabled. Excel for Mac has PivotTable options as well; select the PivotTable and look for PivotTable Analyze > Options, or use the right-click menu. Ribbon labels and dialog layouts can differ slightly by release. Excel for the web may not expose every desktop PivotTable formatting option, so open the workbook in the desktop app if you cannot find this setting.

Why #DIV/0! appears in a PivotTable

#DIV/0! means a calculation attempted to divide a number by zero or by an empty cell treated as zero. The error may already exist in a source column, for example where a worksheet formula calculates a rate, cost per item, or percentage. If that column is included in the PivotTable, errors from the source can show up in the report.

An error can also be produced by a calculation associated with the PivotTable, such as a calculated field, or by a measure in a Data Model or Power Pivot report. In these cases the division may occur as the report calculates its result rather than in a visible source cell. The display setting can hide the resulting error in the PivotTable, but it does not tell you which of these causes applies.

To investigate, locate the field that contains the error and compare its source rows with the PivotTable result. Check whether the denominator is zero, blank, or unexpectedly stored as text. If the report uses a calculated field or measure, inspect that calculation too. Fixing the cause is preferable when the value is meant to be meaningful; hiding it is useful when the error is understood and the report should remain readable.

When to fix the source formula instead

If the error comes from an ordinary worksheet formula, you can decide what a zero denominator should mean before changing the formula. For example, suppose column D contains revenue, column E contains units, and column F calculates revenue per unit. With the revenue in D2 and units in E2, this formula returns a blank if the division produces an error:

=IFERROR(D2/E2,"")

If D2 is 120 and E2 is 0, the result is a blank string rather than #DIV/0!. That may be suitable for a report where no rate can be calculated. It is not the same as a rate of zero: treating an undefined value as zero can lower an average or mislead readers. If the source needs to distinguish “no valid rate” from a real zero, consider a separate status field or an explicit message instead of silently substituting a number.

After editing source formulas, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click the PivotTable and select Refresh. Then inspect the result and confirm the field is summarized as intended. A formula that returns an empty string can be handled differently from a numeric zero, and aggregation behavior depends on the field and report setup. Do not assume that replacing an error with a blank will preserve every count, average, or calculation exactly.

Blank cells, zero values, and error values are different

Excel provides separate PivotTable options because an empty source value, the number zero, and an error are not interchangeable. For error values show: controls cells containing errors. For empty cells show: controls how empty cells in the report are displayed. Neither setting removes numeric zeros. If a PivotTable shows 0 rather than #DIV/0!, the error-display option will not hide it.

Before changing a report, identify what the cell actually contains. A zero may be a valid result, such as zero sales, and hiding it could conceal useful information. A blank may mean no source record or no calculated value. An error indicates that Excel could not produce a valid result. The right display depends on the meaning, not just the appearance.

Use For error values show: to replace or hide errors.
Use For empty cells show: to replace or display empty PivotTable cells differently.
Use number formatting or a carefully chosen report design if the issue is displaying zeros.

Choosing between hiding and correcting errors

Hiding errors is often appropriate for a presentation copy of a report when the cause is expected and the reader does not need to see technical error codes. For example, a margin-per-unit calculation may be undefined for a category with no units. Showing a blank can make the report easier to scan, while a text replacement can make the limitation explicit.

Correct the source or calculation when the error indicates a data-quality problem, an incorrect formula, or a denominator that should not be zero. If you distribute the workbook for analysis, hiding an error without documenting it can make an incomplete calculation look like an ordinary blank. A note beside the report, a descriptive replacement such as “No denominator,” or a separate exception check can preserve that context.

Also consider how the result will be used. A visual report may benefit from blank error cells, but a downstream process that reads PivotTable output may need an explicit status or corrected numeric data. The display option does not convert an error into a valid number, and it should not be treated as data cleaning.

Common problems and checks

If the option appears to do nothing, first check whether the visible error is inside the PivotTable rather than in a nearby source cell. The setting only affects error values displayed by the selected PivotTable. It does not hide errors elsewhere on the worksheet, in another PivotTable, or in the source range itself.

The error returns after a refresh: The source or report calculation still produces an error. The display setting should govern the PivotTable view, but verify the checkbox on the affected report and inspect the calculation.
Only some reports are fixed: Each PivotTable may have its own options. Select each affected report and check its Layout & Format settings.
A cell is still showing zero: Zero is a numeric value, not an error. Use an appropriate zero-display choice only if hiding valid zeros is safe for the report.
The dialog has no effect on a chart: The option concerns PivotTable cells. A PivotChart can have its own display behavior, so check the chart’s settings and the worksheet report separately.
You cannot find the command: Click inside the PivotTable first so the contextual PivotTable Analyze tab appears. On the web, use the desktop application if the relevant option is unavailable.

For a workbook that is refreshed regularly, test the report after refreshing and after changing filters. A filter can expose a different combination of source values or a different calculation result. If colleagues use the workbook in multiple Excel versions, have them confirm that the report remains understandable in their environment.

Frequently Asked Questions

Can I hide #DIV/0! in a PivotTable without changing the source data?
Yes. Check For error values show: in the PivotTable Options dialog and leave the replacement box blank. This changes the report’s display, not the underlying source or calculation.

How do I replace the error with text instead of a blank?
In PivotTable Analyze > Options > Layout & Format, check For error values show: and type the text you want in its box, such as “Not available.” Select wording that cannot be mistaken for a numeric result.

Will refreshing the PivotTable bring the errors back?
Refreshing updates the report from its source, so the underlying error can still be present. The display option is separate from fixing that cause. If errors appear again, verify the setting on the correct PivotTable and inspect the source or calculation.

Does this setting hide zeros or blank cells too?
No. It applies to errors. Use the separate empty-cell option for blanks, and treat zeros as numeric results. Hiding zeros may remove valid information, so check what they represent before changing the report.

Can I use this for a PivotChart?
The setting controls error values in the PivotTable, not every chart display choice. A chart may omit, plot, or label values according to its own settings and the data it receives. Check the PivotChart separately if an error-related display remains there.

Best practice for a readable report

For a quick cleanup, use the PivotTable error-display option and leave the box blank. For a reliable analytical workbook, also identify why the division fails and decide whether the correct outcome is blank, zero, a message, or a corrected formula. Keep the difference between “hidden error” and “valid result” clear to anyone who will refresh or rely on the report.

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.