Fix Missing, Blank, or Zero Values in a WPS Office Spreadsheets PivotTable
Question details
The user wants to understand why a WPS Office PivotTable shows missing, blank, or zero values after refreshing and how to correct the source data, range, and refresh behavior.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a PivotTable in WPS Office Spreadsheets and notices that blank items, zero values, or missing entries still appear after refreshing the report.
- Observed behavior
- The PivotTable continues to show missing, blank, or zero values after refresh because of an incorrect source range, extra empty rows, empty source cells, or actual zero values.
Before changing the PivotTable, open the workbook in WPS Office Spreadsheets and make sure the source worksheet is visible. Verify that the source data has a clear header row and note any extra blank rows or columns that may be included in the current range.
Verify and Correct the PivotTable Data Source
Use this solution first to check whether the selected range includes empty rows or misses the latest records, which is the most common cause of blank or missing items.
A PivotTable keeps the original cached range until you change or refresh it. If the source range was created with extra blank rows, the PivotTable may show blank category items; if it does not include new rows, values may appear missing.
Click any cell inside the PivotTable, then select the PivotTable Analyze tab at the top of WPS Office Spreadsheets.
Click Change Data Source and check the range displayed in the dialog box against the actual source data.
Type or select the correct range so it covers only the populated rows and columns, excluding extra empty rows.
Look in the source columns for blank cells and actual zero values. Replace blanks with a value such as 'N/A' or delete the row if the blank record is not needed.
Click PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh, to confirm the blank or missing entries disappear.
Convert the Source Data to a Table for Automatic Range Updates
Use this solution when your source data grows regularly, so new records are included in the PivotTable without manually changing the source range every time.
Fix Missing, Blank, or Zero PivotTable Values in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets, verify the PivotTable data source, and convert the source range to a table so you can refresh without blank, missing, or zero value issues caused by incorrect ranges or empty source cells.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel workbook that contains the PivotTable you want to fix.
- 2. Check the PivotTable source range: Select a cell inside the PivotTable, go to PivotTable Analyze > Change Data Source, and confirm the correct range is selected.
- 3. Convert source data to an Excel Table: Select the source range, click Insert > Table or press Ctrl+L, and confirm the header row checkbox.
- 4. Refresh the PivotTable: Click PivotTable Analyze > Refresh so WPS Office reads the corrected table-based source.
- 5. Review blank and zero entries: Inspect the source columns for empty cells and real zero values; replace blanks or filter the field to hide unwanted zero items.
Frequently Asked Questions
Why does my WPS PivotTable still show blank items after refresh?
Usually the selected source range includes extra blank rows or the source column has empty cells. Click a cell inside the PivotTable, choose PivotTable Analyze > Change Data Source, and reduce the range to the actual data. Then inspect the source column for blanks.
How do I find and correct the current PivotTable data source?
Select any cell in the PivotTable, go to PivotTable Analyze, and click Change Data Source. The dialog shows the current range, which you can edit by typing the correct range or selecting it directly on the worksheet.
Can new rows be added to my PivotTable automatically?
Yes. Convert the source data to a table using Insert > Table or Ctrl+L before building the PivotTable. When you add records to the table and click PivotTable Analyze > Refresh, the PivotTable includes the new rows.
What if my data has real zero values that I want to hide?
Real zero values are not the same as blank cells. You can filter the PivotTable field and uncheck 0, or replace or exclude zero-value rows in the source data, depending on how the report should display.
