How to Hide Future Zero Values in a Cumulative Planned vs Actual PivotChart in WPS Spreadsheets
Question details
The user needs to keep a cumulative planned-versus-actual PivotChart dynamic but prevent future periods with zero or blank cumulative values from being plotted.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- They maintain a cumulative planned vs actual PivotChart and want future months without data to stay hidden while slicer-driven filtering still works.
- Observed behavior
- Future months appear as zero or duplicate cumulative values in the PivotChart.
Open the workbook in WPS Spreadsheets and identify the cumulative value field and the month or date column. Check whether future rows already exist as zero values or blank cells, because this determines whether you only need to change chart empty-cell settings or also add a helper column.
Add a Visible Cumulative Helper Column Returning #N/A for Future Zeros
Use this method when the source table contains future-dated rows with zero or blank cumulative values; it keeps the PivotChart dynamic and prevents future points from being plotted.
A helper column creates a chart-friendly missing value. WPS Spreadsheets charts skip #N/A points, so future months become gaps instead of zero markers while the original cumulative data remains intact.
In WPS Spreadsheets, open the workbook and select the worksheet that feeds the PivotTable. Locate the month or date column and the cumulative planned and actual columns.
Add a new column next to the cumulative series, such as Visible Cumulative Plan. In the first data row, enter =IF(OR(A2>TODAY(),B2=0),NA(),B2), replacing A2 with the month or date cell and B2 with the cumulative value cell.
Drag the fill handle or double-click it to copy the formula through all future data rows.
Click the PivotTable, go to PivotTable Tools > Change Data Source, include the helper column in the source range, and click Refresh.
In the PivotChart field list, remove the old cumulative value field and add the helper field to Values. Future zero months disappear from the chart while slicers and filters stay active.

Show Empty Cells as Gaps in PivotChart Settings
Use this quick check when future cells are genuinely empty but the PivotChart still shows them as zero or connects them with a line.
Keep Dynamic PivotCharts Clean with WPS Spreadsheets
Use WPS Spreadsheets to add a visible cumulative helper column that returns #N/A for future zero values, then refresh your PivotChart so future periods become gaps instead of zero markers. Your existing slicers, filters, and planned vs actual calculations keep working.
- 1. Open your workbook in WPS Spreadsheets: Install WPS Office, open WPS Spreadsheets, and load the planned versus actual workbook containing the PivotChart.
- 2. Create the visible cumulative helper column: In the source table, enter =IF(OR(A2>TODAY(),B2=0),NA(),B2) next to each cumulative series and fill the formula down.
- 3. Include the helper column in the PivotTable: Click the PivotTable, select Change Data Source, expand the range to include the helper column, and refresh the PivotTable.
- 4. Update the PivotChart field: Replace the original cumulative value field with the helper field in the PivotChart values so future periods stop plotting as zero.

Frequently Asked Questions
Why does my cumulative planned vs actual PivotChart show zero for future months?
Future month rows are usually present in the source data with zero or blank cumulative amounts, and the PivotTable summarizes them as zero. Hide them by using a helper column with NA() for future zero values or by setting empty cells to show as gaps.
Can I hide future zero values without changing the source data?
If the cells are truly empty, right-click the PivotChart, choose Select Data > Hidden and Empty Cells, and set empty cells to Gaps. If actual zeros exist in future rows, you need a helper column or a pivot filter to exclude them.
What formula can I use instead of DAX BLANK() in WPS Spreadsheets?
Use a worksheet formula such as =IF(OR(A2>TODAY(),B2=0),NA(),B2). WPS charts skip #N/A points, so future months become gaps instead of plotted zeros.
Will slicers and filters still work after adding the helper column?
Yes. Include the helper column in the PivotTable source range and refresh the PivotTable. Slicers continue to operate on the original date and category fields.
How do I make empty PivotChart cells appear as gaps in WPS?
Select the chart, right-click and choose Select Data, then click Hidden and Empty Cells and choose Gaps instead of Zero.




