To remove errors from an Excel PivotTable, first correct the error in its source data, then refresh the PivotTable. If you only need to hide the displayed error, select the PivotTable and go to PivotTable Analyze > Options > Layout & Format, select For error values show, and leave its box blank. Hiding an error changes what you see, not the underlying data.
What a PivotTable error means
A PivotTable summarizes data from a source range, an Excel table, or, in some workbooks, the Data Model. An error visible in the report may come from a formula in that source, a formula used to create a calculated field, or another calculation in the PivotTable. The right fix depends on where it originates: changing the display can make a report cleaner, but it does not repair the calculation.
Common Excel errors include #DIV/0! when a formula divides by zero, #N/A when a lookup has no match, #VALUE! when a formula gets an unsuitable value, and #REF! when a formula refers to a deleted or invalid cell. Sometimes the error appears in the original rows; sometimes it appears only after the PivotTable calculates a result. Start by finding out which case applies rather than assuming that refreshing will fix it.
One useful distinction: a blank, a zero, and an error are not interchangeable. A blank might mean that no value was entered, zero is a numeric result, and an error means Excel could not complete a calculation. Replacing errors with zero can change totals and averages, so choose that treatment only when zero is genuinely the intended business meaning.
Find the source of the error first
Click an error cell and note its exact text. Then inspect the source rows that contribute to the affected PivotTable item. If the PivotTable is based on a normal range or Excel table, locate that source through PivotTable Analyze > Change Data Source. The dialog shows the range or table name; for an Excel table, you can also click a source cell and check the Table Design tab.
For a quick check, use Find to look for the error text in the source worksheet, or filter the source column if it contains formula results. You can also select a source column and use Home > Find & Select > Go To Special > Formulas, then select Errors. This selects formula cells that currently evaluate to errors in the chosen area. Check the formulas and their inputs before editing anything.
If you cannot locate the data on a visible worksheet, the PivotTable may use the Data Model, an external connection, or a query. Check PivotTable Analyze > Change Data Source or Data > Queries & Connections for clues. A report connected to a query may need its transformation or source formula corrected, rather than a change to cells on the PivotTable sheet.
Repair errors in the source data
Fix the formula or input that caused the error whenever possible. For example, if a rate formula divides an amount by a quantity, check whether the quantity is missing or zero and decide what the result should mean. If a lookup returns #N/A, check whether the lookup value is present, whether the matching values use compatible types, and whether the lookup range includes the intended records. Correcting the actual cause keeps the PivotTable useful for future analysis and refreshes.
If an error is expected and should be treated as a specific value, handle it in the source formula deliberately. For example, with the amount in B2 and quantity in C2, this formula returns an empty string when an error occurs:
With the amount in B2 and quantity in C2, if B2 is 120 and C2 is 4, the result is 30; if the calculation encounters an error, the formula returns a blank display.
=IFERROR(B2/C2,"")
IFERROR catches any error in the expression, not just division by zero, so it can also conceal an unexpected problem. If only zero quantities are meant to produce a blank, use a specific test instead. With the same layout, the formula =IF(C2=0,"",B2/C2) returns 30 for 120 divided by 4 and a blank when C2 is zero. It does not suppress other errors, which can make problems easier to detect. Choose a blank, a label, or another outcome based on the meaning of the field and how the PivotTable will summarize it.
After changing a source formula or value, refresh the report: right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. To update every PivotTable in the workbook, use Data > Refresh All. If the PivotTable uses an Excel table as its source, new table rows are included when the PivotTable refreshes. If it uses a fixed cell range, confirm that the range includes any added rows.
Hide errors in the PivotTable display
When the error must remain in the source but should not be shown in the report, use the display option. Click inside the PivotTable, choose PivotTable Analyze > Options, and open the Layout & Format tab in the PivotTable Options dialog. Under Format, select For error values show and leave the adjacent box empty. Select OK. To show a replacement instead, enter text such as Check source; that text is only a display choice, not a repair to the underlying calculation.
The setting is useful for presentation, especially when an error is known and a report is being prepared before the source can be corrected. However, readers may mistake a blank for missing data or a zero if the report has no explanation. A short note beside the PivotTable can clarify that errors are being suppressed. Also remember that the setting affects the PivotTable display, not the original worksheet cells, and it does not make the source formula valid.
Do not confuse this option with For empty cells show, which controls how empty PivotTable cells appear. A blank source value and an error are different inputs, and the corresponding display settings address different cases.
Check calculated fields and other calculations
If every source row looks valid but a PivotTable calculation still produces an error, inspect calculated fields or measures. A calculated field uses a formula in the PivotTable, while a measure is created in the Data Model with a different calculation system. A bad reference, an invalid operation, or a calculation that divides by zero can create an error even when the underlying values are not themselves errors.
For a traditional PivotTable, select a cell in the report and look for PivotTable Analyze > Fields, Items, & Sets > Calculated Field. Review the formula and the fields it references. If the workbook uses the Data Model, inspect the measure in Power Pivot or the relevant model tools; ordinary worksheet formula instructions may not apply. Correct the calculation where it is defined, then refresh and verify the affected totals.
Check the PivotTable’s Value Field Settings as well. A field summarized by Sum, Count, or Average can produce a different result from the same field displayed as a percentage or calculated value. Changing the summary method may resolve an unexpected result, but it is not a way to erase an invalid source error. Confirm that the chosen calculation answers the question the report is meant to answer.
Refresh, rebuild, or change the source range?
Refresh is appropriate after correcting values or formulas in an existing source. It updates the PivotTable from the data it already knows about, but it cannot repair a broken source formula or automatically include every row beyond a fixed source range. If the workbook was just changed, refresh once and then check the source definition if the correction does not appear.
Changing the source range is useful when new rows were added outside the original range. An Excel table is often easier to maintain because it expands as rows are added; the PivotTable still needs a refresh to pick up those rows. Rebuilding the PivotTable is usually unnecessary for a simple error and can discard report layout choices. Consider rebuilding only when its source or structure is fundamentally wrong, and keep a copy of the workbook before making major changes.
Common mistakes and safer habits
- Hiding instead of fixing: A blank display can make a report look clean while leaving incorrect source data in place. Record why errors are hidden and revisit the source.
- Replacing every error with zero: This can lower sums or affect averages. Use zero only when it accurately represents the missing or invalid result.
- Refreshing without checking the cause: Refresh updates the report; it does not correct a formula, a missing lookup match, or a broken reference.
- Editing cells inside the PivotTable: PivotTable cells are generated from the source and can be overwritten on refresh. Make corrections in the source or calculation definition.
- Ignoring new source rows: A fixed source range may omit added records, making the report incomplete even after a refresh. Check Change Data Source when totals seem out of date.
For recurring reports, validate the source before refreshing. Filter for errors in important formula columns, check that lookup keys use consistent formats, and make sure formulas are filled through new rows. If other people rely on the PivotTable, explain whether a blank means no data, a suppressed error, or a valid empty result.
Frequently Asked Questions
Why does my PivotTable still show an error after I refresh it?
Refresh reads the source again; it does not fix an error that remains in the source formula or PivotTable calculation. Locate the source or calculated field, correct the cause, and refresh again. Also check that the PivotTable is using the source range you intended.
Can I remove errors without changing the source data?
Yes. Use PivotTable Analyze > Options > Layout & Format and set For error values show to blank or explanatory text. This changes only the report’s display. The source error remains and may still affect other formulas or reports.
Will replacing an error with a blank change my PivotTable totals?
It can. A blank, a text value, and a numeric zero may be treated differently by aggregations and calculations. Test the result against a small set of known source rows, and do not treat a blank as equivalent to zero unless that is correct for the analysis.
How do I find which source row causes an error?
Use Change Data Source to identify the source, then search or filter its error-prone columns. Home > Find & Select > Go To Special > Formulas > Errors can select error-valued formulas in a selected worksheet area. For query or Data Model sources, inspect the query or model instead.
Can I remove the error by deleting the PivotTable cell?
No. The PivotTable cell is an output of the report, and deleting or typing over it does not repair its source. Change the underlying data or calculation, then refresh. If you only want a cleaner report, use the error display setting rather than editing generated cells.
Related
- How to Paste Filtered Data in Excel Without Errors
- How to Paste Horizontally in Excel: Rows and Columns
- How to Paste Down a Column in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Formula Not Calculating? Fix the Common CausesIf an Excel formula is not calculating, first check Formulas > Calculation Options > Automatic, then make sure…
- Excel Text Box Formula Not Working? Fix ItIf an Excel text box formula is not working, select the text box’s outer border, enter a cell…
- INDIRECT Excel Not Working? Fix the ReferenceIf INDIRECT is not working, check that its text builds a valid cell reference, that the referenced sheet…
- Page Break in Excel Not Working? Fix the Common CausesIf a page break in Excel is not working, first switch to View > Page Break Preview and…
- Excel MAX Date Not Working? Fix Text Dates and ErrorsIf the cells contain real Excel dates, MAX returns the latest one: with dates in B2:B100, enter =MAX(B2:B100).…
- Excel Left and Right Mouse Buttons Not Working?If the left or right mouse button is not working in Excel, first check whether the same button…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.