To show errors as blank in an Excel PivotTable, select the PivotTable, open PivotTable Analyze > Options, go to Layout & Format, check For error values show, and leave its box empty. This changes how errors appear in that PivotTable; it does not remove or repair errors in the source data.
What the error-display setting does
A PivotTable can display an error when an error value in its source data affects a summarized result, or when an error arises in a PivotTable calculation. The setting tells Excel what text or value to display in place of an error inside the selected PivotTable. If the box beside For error values show is empty, the cell looks blank.
This is a presentation choice, not a data-cleaning step. The underlying source cell may still contain an error such as #N/A or #DIV/0!, and another formula, chart, or report that uses the source may still be affected. The PivotTable may also show the error again after a refresh if the setting is not retained or the error is still present.
For example, suppose a source table has a region column and a margin column, and one margin calculation returns #DIV/0!. If that error appears in a PivotTable result, the display option can make the result cell appear empty instead. It does not calculate a valid margin for that row, nor does it establish whether the region’s true summary is zero, unavailable, or incomplete.
Steps in Excel for Microsoft 365 on Windows
- Click any cell inside the PivotTable you want to change. The PivotTable-specific ribbon tab will appear.
- Choose PivotTable Analyze > Options. Depending on the ribbon layout, the Options command may appear in the PivotTable group. You can also right-click inside the PivotTable and choose PivotTable Options.
- In the PivotTable Options dialog box, open the Layout & Format tab.
- Under Format, select For error values show.
- Leave the adjacent entry box empty, then choose OK.
Check the result in the PivotTable. If errors are still visible, make sure you selected the correct PivotTable and that the displayed cell is actually an error value rather than text that looks like one. The setting applies to the selected PivotTable, not automatically to every PivotTable in the workbook.
Error values and empty cells are different
Excel provides a separate option for empty cells. In the same Layout & Format tab, For empty cells show controls what the PivotTable displays when a source value is empty. To keep those cells empty, enable that option and leave its box blank. This is independent of For error values show.
The distinction matters because an empty source cell is not the same as an error. A blank might mean that a value was not entered, does not apply, or is not yet available. An error indicates that Excel encountered a problem evaluating a value or calculation. Hiding both in the same way can make a report cleaner, but readers may no longer be able to tell missing information from a failed calculation.
Also, a displayed zero is not necessarily an error or an empty cell. If a PivotTable shows 0, the summarized result may genuinely be zero. The error-display setting will not hide it. Number formats and other display choices can affect how zeroes look, but they do not change this error option.
How to troubleshoot when errors remain visible
If the option appears to have no effect, work through the source and the PivotTable separately. A common source of confusion is that a cell merely contains the characters #N/A as text. Excel treats that text differently from an actual #N/A error, so the PivotTable error-display setting may not replace it.
- Confirm the selected object. Click inside the PivotTable that shows the error before opening its options. A workbook may contain several PivotTables built from similar data.
- Confirm the setting is enabled. In PivotTable Options, the box must be checked; leaving the text-entry field empty alone is not enough.
- Refresh and check again. If the source has changed, use PivotTable Analyze > Refresh. A refresh updates the report from its source, but it does not correct an error that remains in that source.
- Inspect the source row. Find the corresponding record and determine whether its value is a genuine Excel error, text, or a blank. Check any source formulas or lookup results that feed the PivotTable.
- Check calculated results. If the error comes from a calculated field or another calculation associated with the report, hiding it can conceal the result without fixing the calculation logic.
Some errors are caused by a denominator of zero, a missing lookup match, or an invalid input. The right correction depends on what the data is supposed to mean. For instance, a missing lookup may call for correcting a key or deliberately classifying the record as unmatched. Replacing every error with zero can distort totals and averages, so do not treat zero as a neutral substitute unless it is genuinely the correct value.
When to hide errors and when to fix the source
Leaving the display blank can be useful for a presentation-ready report when the audience does not need to see technical error codes in the PivotTable itself. It is also reasonable when errors are already understood and handled elsewhere, or when the report is a temporary view for reviewing the rest of the data.
Fixing the source is usually better when the error indicates a data-quality issue, affects a key measure, or could change a decision. A blank display can make a report look complete even when one or more underlying values were not calculated. Consider adding a separate status or quality check to the source data if users need to know which records are missing or invalid.
Before replacing source errors, decide what each one means. A blank, a zero, and an explicit label such as “Not available” communicate different things. Blank values can be excluded from some summaries or make a result harder to interpret; zero can count as a real numeric amount; and text may not behave like numbers in a numeric summary. The appropriate treatment depends on the measure and the reporting question.
What happens after a refresh or when sharing the workbook?
The display choice belongs to the PivotTable’s options, while its source values remain separate. When you refresh, Excel reads the current source data and rebuilds the PivotTable; the error setting is intended to control the PivotTable’s display, but it is still sensible to verify the report after a major source or layout change. If a workbook has multiple PivotTables, inspect each one that needs the same treatment.
When sharing a workbook, tell readers that blank cells may represent hidden errors as well as genuinely empty results. This is especially important in operational or financial reports, where someone might assume that a blank means no activity. If that ambiguity matters, keep an error indicator in a separate field or use a clearly explained label rather than relying on an invisible error.
In Excel for Mac, select the PivotTable and look for its PivotTable Analyze options or open PivotTable Options from the context menu; the precise ribbon arrangement can vary by release. The relevant setting is in the Layout & Format area when available. Excel for the web has a more limited set of PivotTable controls than the desktop app, so if you cannot find this option there, open the workbook in desktop Excel for Microsoft 365.
Common misconceptions
- It does not delete errors. The source data and any dependent formulas can still contain or produce errors.
- It does not make errors equal zero. A blank-looking cell should not be read as a numeric zero.
- It is not the empty-cell setting. Use the separate empty-cells option for source blanks.
- It does not automatically apply to every report. Set and verify the option for each relevant PivotTable.
Frequently Asked Questions
How do I hide #DIV/0! in an Excel PivotTable?
Click inside the PivotTable, open PivotTable Analyze > Options, choose Layout & Format, check For error values show, and leave the box empty. This hides the displayed error in that PivotTable; it does not fix the source calculation.
Why is my PivotTable still showing an error after I selected the option?
Check that the option is checked, that its box is empty, and that you changed the options for the PivotTable displaying the error. The visible characters might also be text rather than a genuine Excel error, or the error may be coming from another part of the report.
Can I show a word such as “Error” instead of leaving the cell blank?
Yes. Enable For error values show and type the label you want in its box. That label is a display substitute for errors in the PivotTable; it does not replace the underlying source values.
Does showing errors as blank change PivotTable totals?
The setting changes how error values are displayed, not the source data or the meaning of the calculation. Do not assume that a blank-looking result is zero or that an affected summary is complete; inspect the source and calculation if the total matters.
How do I make empty source cells blank too?
In the same PivotTable Options dialog, use For empty cells show separately. Check it and leave its entry box empty if you want empty source cells to appear blank. This does not control actual error values.
Related
- Excel Decimal Comma: Change Number Separators
- Excel Number Format to Hide Zeros Without Changing Values
- Excel Number Custom Format Codes: Examples and Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Box Plot in Excel: Show Outliers and Read the ChartYes. In Excel for Microsoft 365 on Windows, select your data, choose Insert > Insert Statistic Chart >…
- Circular Radar Chart in Excel: Create and Format ItTo make a circular radar chart in Excel for Microsoft 365 on Windows, arrange your categories in one…
- Excel Waterfall Charts: How to Create and Format ThemIn Microsoft Excel for Microsoft 365, select a table of categories and values, then choose Insert > Insert…
- Excel Line Chart: How to Fill Gaps in Missing DataIn Excel for Microsoft 365 on Windows, select the chart, then choose Chart Design > Select Data >…
- Power Pivot Not Working? Fix Common Excel ProblemsIf Power Pivot is not working in Excel for Microsoft 365 on Windows, first enable its COM add-in:…
- Speedometer Chart in Excel: A Practical Step-by-Step GuideTo create a speedometer chart in Excel for Microsoft 365 on Windows, make a doughnut chart from three…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.