How to Replace a Pivot Table #REF! Error with Zero in WPS Office Excel
Question details
User needs to prevent a formula or cell linked to a pivot table from displaying #REF! when pivot table settings or filters change, replacing the error with zero.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A workbook contains formulas or cells that reference a pivot table; pivot table filters, settings, or layout changes invalidate the reference.
- Observed behavior
- The linked cell displays #REF! instead of the expected value after pivot table changes.
Open the workbook in WPS Office Excel and identify the exact cells that are linked to the pivot table. Check that the pivot table still exists and note the reference cell you want to wrap with IFERROR.
Use IFERROR to Return Zero Instead of #REF!
Best for most users because it immediately replaces the error with zero while preserving valid results.
Click the cell that currently displays the #REF! error or the cell where you want to enter the formula.
Type =IFERROR(A1,0), replacing A1 with the cell reference that points to the pivot table result.
Press Enter. The cell shows the original pivot value when it is valid, or 0 when the reference returns #REF!.
Use IF with ISERROR for Easier Formula Reading
Use this alternative if you prefer a formula that explicitly checks for any error before returning a value.
Fix Pivot Table #REF! Errors in WPS Office Excel with IFERROR
WPS Office Excel supports the same IFERROR and ISERROR formulas as Microsoft Excel, so you can wrap pivot table references to return zero instead of #REF! and keep your reports clean.
- 1. Open WPS Office Excel: Download and install WPS Office, then open the workbook that contains the pivot table and #REF! error.
- 2. Select the formula cell: Click the cell that should display the pivot table value or zero.
- 3. Enter IFERROR around the reference: Type =IFERROR(A1,0) in the formula bar, replacing A1 with the actual pivot table cell reference.
- 4. Apply the formula: Press Enter and copy the formula to other linked cells if needed.
Frequently Asked Questions
Why does my formula show #REF! when a pivot table filter changes?
The formula points to a specific cell or range that disappears or moves when the pivot table layout, filter, or settings change. WPS Office Excel returns #REF! because that exact reference is no longer valid.
Can I replace the Pivot Table error with a blank or a custom message instead of zero?
Yes. Instead of =IFERROR(A1,0), use =IFERROR(A1,"") for a blank result or =IFERROR(A1,"Check pivot") for custom text. Enclose text in double quotes.
Does the IFERROR method work if the whole pivot table is deleted?
Yes. If the referenced pivot table is deleted, the formula returns zero because the reference raises #REF!, but you still need to recreate the pivot table to restore the source data.
Can I use IFERROR with calculations that reference a pivot table?
Yes. Wrap the full calculation, for example =IFERROR(A1+1,0). The formula returns the calculated result when A1 is valid and zero when the reference produces #REF!.
Is IFERROR available in WPS Office Excel?
Yes. WPS Office Excel supports IFERROR, ISERROR, and other Excel-compatible functions. You can enter the formula the same way as in Microsoft Excel.
