How to Filter an Excel PivotTable to Show Only Non-Zero Totals
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.

- 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.
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.
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.
Click the drop-down arrow next to 'Row Labels' or the specific category header in your PivotTable.
Hover over 'Value Filters' in the context menu, then choose 'Does Not Equal' from the secondary list.
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.
Click 'OK'. The PivotTable will refresh to hide all grouped records that have a net total of exactly zero.

Manually Filter Zero Values Using Slicers
Sort the PivotTable totals and manually deselect zero-value items using a Slicer.
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. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your source data and PivotTable.
- 2. Access Value Filters: Click the filter arrow next to your Row Labels and select 'Value Filters' > 'Does Not Equal'.
- 3. Apply Zero Filter: Enter '0' into the criteria field and click 'OK' to instantly clean up your PivotTable.

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.




