Chart & Visualization Issues

How to Hide Future Zero Values in a Cumulative Planned vs Actual PivotChart in WPS Spreadsheets

Jason MillerJason Miller Nov 13, 2026 869 views

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.

How to Hide Future Zero Values in a Cumulative Planned vs Actual PivotChart
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the source data sheet

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.

2
Insert the helper column

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.

3
Fill the formula down

Drag the fill handle or double-click it to copy the formula through all future data rows.

4
Update the PivotTable data source

Click the PivotTable, go to PivotTable Tools > Change Data Source, include the helper column in the source range, and click Refresh.

5
Add the helper field to the chart

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.

Add a Visible Cumulative Helper Column Returning #N/A for Future Zeros
Result: The PivotChart now plots non-zero past and current months and leaves future months as gaps.
WPS Spreadsheets PivotChart

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. 1. Open your workbook in WPS Spreadsheets: Install WPS Office, open WPS Spreadsheets, and load the planned versus actual workbook containing the PivotChart.
  2. 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. 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. 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.
Suppresses future zero values while preserving dynamic slicer filtering.Uses familiar spreadsheet formulas instead of complex data-model measures.Reads, edits, and saves Microsoft Excel .xlsx PivotTable workbooks with high compatibility.Free and lightweight alternative to Microsoft Excel for planned vs actual reporting.
microsoft office alternative - wps office

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.