How to Suppress #DIV/0! Errors in a WPS Office PivotTable
Question details
The user wants to hide #DIV/0! errors that appear in a WPS Office Spreadsheets PivotTable after using Show Values As calculations such as Percentage Difference From.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building or formatting a PivotTable and wants a clean report when percentage difference calculations encounter zero or blank base values.
- Observed behavior
- PivotTable cells show #DIV/0! errors after applying Show Values As > Percentage Difference From instead of remaining blank or showing a chosen replacement.
Make sure the data range is already summarized in a PivotTable and note whether the PivotTable is based on a normal range or the Data Model. The error display setting is available in PivotTable Options and does not require changing the source data.
Use PivotTable Options to Replace Error Values with Blank or Custom Text
Use this when you want the fastest way to hide #DIV/0! errors across the entire PivotTable without modifying the source data.
Click any cell inside the PivotTable that shows the #DIV/0! error.
Right-click the selected cell and choose PivotTable Options, or go to PivotTable Tools > Options.
In the Layout & Format tab, check For error values show and leave the box blank or type a replacement such as 0 or N/A.
Click OK to apply the setting. The existing #DIV/0! cells change to blank or the replacement value.
Create a Data Model Measure with DAX to Handle Zero Denominators
Use this when the PivotTable uses the Excel Data Model and you need calculation-level control beyond the display setting.
Fix #DIV/0! PivotTable Errors Directly in WPS Office Spreadsheets
WPS Office Spreadsheets includes the same PivotTable error display settings, so you can suppress #DIV/0! errors directly without changing your data or source formulas. Open the workbook, apply the For error values show option, and keep full compatibility with Microsoft Excel files.
- 1. Open WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the workbook that contains the PivotTable.
- 2. Select the PivotTable: Click any cell in the PivotTable where the #DIV/0! error appears.
- 3. Open PivotTable Options: Right-click and choose PivotTable Options, or click the PivotTable Tools tab and select Options.
- 4. Set error values: Go to Layout & Format, check For error values show, and leave the box blank or enter 0.
- 5. Apply the change: Click OK to hide the division errors throughout the PivotTable.
Frequently Asked Questions
Can I show a zero instead of blank for PivotTable division errors?
Yes. In PivotTable Options > Layout & Format, check For error values show and type 0 in the box. The PivotTable will display 0 instead of #DIV/0!.
Does the error display setting affect only one field?
No, the setting applies to the whole PivotTable report. If you need different behavior for a specific field, use a Data Model measure such as DIVIDE(..., BLANK()) instead.
Will the #DIV/0! suppression remain when I open the file in Microsoft Excel?
Yes, the PivotTable option is stored in the workbook. When you save from WPS Office as .xlsx and open it in Excel, error values are shown as the blank or replacement text you configured.
Why does Percentage Difference From create #DIV/0! errors?
Percentage Difference From divides by a base or previous value. If that base value is zero, blank, or missing, WPS cannot calculate a percentage and displays a division error until you apply the error value setting.
