Pivot Table Issues

How to Filter an Excel PivotTable to Show Only Non-Zero Totals

Ayan MasoodAyan Masood Oct 29, 2026 871 views

Question details

The user wants to exclude grouped records from a PivotTable that result in a calculated total of zero, specifically when positive and negative source values cancel each other out.

How to Filter an Excel PivotTable to Show Only Non-Zero Totals
Product
Excel
Device & OS
not provided
Scenario
Analyzing summarized financial or inventory data in a PivotTable where zero-balance groups add unnecessary clutter and need to be hidden.
Observed behavior
The PivotTable currently displays all grouped records by default, including those where the aggregated total of multiple source rows amounts to exactly zero.
Before you start

Ensure your PivotTable is fully refreshed and that the numerical data you want to filter is properly placed in the 'Values' area of the PivotTable Field List.

Solution 1Recommended

Use Value Filters to Exclude Zero Totals

Apply a Value Filter directly to the row labels to automatically hide any items where the combined sum of values equals zero.

This is the most efficient method because it dynamically filters out groups whose net total is zero, effectively addressing cases where positive and negative values cancel each other out.

1
Open the Filter Menu

Click the drop-down arrow next to 'Row Labels' or the specific category header in your PivotTable.

2
Select Value Filters

Hover over 'Value Filters' in the context menu, then choose 'Does Not Equal' from the secondary list.

3
Set the Criteria

In the dialog box, ensure the correct value field (e.g., 'Sum of Value') is selected in the first drop-down, and type '0' in the criteria box on the right.

4
Apply the Filter

Click 'OK'. The PivotTable will refresh to hide all grouped records that have a net total of exactly zero.

Use Value Filters to Exclude Zero Totals
Dynamic Updates: This filter is dynamic. If your source data changes and a previously zero-total group gets a new non-zero balance, it will automatically reappear when you refresh the PivotTable.
Advanced Data Management

Easily Filter PivotTables with WPS Spreadsheet

WPS Spreadsheet offers powerful, highly compatible PivotTable tools. You can effortlessly apply advanced value filters, sort large datasets, and analyze your numbers without the clutter of zero-totals.

  1. 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your source data and PivotTable.
  2. 2. Access Value Filters: Click the filter arrow next to your Row Labels and select 'Value Filters' > 'Does Not Equal'.
  3. 3. Apply Zero Filter: Enter '0' into the criteria field and click 'OK' to instantly clean up your PivotTable.
Fully compatible with Microsoft Excel (.xlsx) PivotTables and raw data.Intuitive Value Filter options to quickly hide zero, blank, or specific error values.Lightweight, fast, and completely free alternative for advanced data analysis.
microsoft office alternative - wps office

Frequently Asked Questions

Why are blank cells showing up as zero in my PivotTable?

By default, PivotTables may treat empty cells as zero. To change this, right-click the PivotTable, select 'PivotTable Options', check the box for 'For empty cells show:', and either leave it blank or type your preferred text.

Can I filter out multiple values at once in a PivotTable?

Yes, you can use Value Filters with 'Between' or 'Not Between' criteria to exclude a specific range of numbers, or use a Slicer to visually deselect multiple items from the dataset at once.

Will the Value Filter remain if I refresh my data?

Yes, Value Filters are linked to the calculated results. If you update your source data and hit 'Refresh', the filter will automatically re-evaluate the new totals and continue hiding any groups that now equal zero.