Excel Pivot Table: Filter Out Zero Values

To filter zero values out of an Excel PivotTable, open the drop-down for the row or column field you want to filter, choose Value Filters > Does Not Equal, select the value field to check, and enter 0. This hides items whose summarized value is exactly zero; it does not remove zero-valued records from the source data.

What a zero-value filter does

A PivotTable usually groups source records into categories and calculates a result for each category, such as a sum, count, or average. A value filter acts on those summarized results. If a PivotTable lists products in rows and sums sales in Values, a filter on the product field can hide products whose summed sales equal zero.

This is different from filtering the original worksheet to remove records with a zero in the Sales column. The PivotTable filter keeps the source records available and hides matching categories in the report. It is also different from hiding the number 0 through number formatting: a value filter changes which items appear, while formatting changes how a displayed value looks.

Step-by-step: hide items with a zero total

Click anywhere inside the PivotTable. Confirm that the field you want to summarize, such as Sales, appears in the Values area.
Open the drop-down beside the row field whose items you want to hide. In a compact layout, this may be the Row Labels arrow; in other layouts, it may show the field name. To filter column items instead, open the corresponding column-field drop-down.
Point to Value Filters, then choose Does Not Equal.
In the filter dialog, choose the value field to evaluate, select or confirm does not equal, and enter 0 as the value. Click OK.

The remaining items are those whose selected summary is not zero. To show only zero-valued items instead, use Value Filters > Equals and enter 0. The filter is attached to the row or column field, so check that you opened the drop-down for the categories you intend to filter, not a different field.

A concrete example

Suppose a source list contains a Region column and an Amount column. A PivotTable places Region in Rows and sums Amount in Values. If North totals 125, South totals 0, and West totals -30, applying Value Filters > Does Not Equal to Region using the Sum of Amount field keeps North and West and hides South. The negative total remains because it is not equal to zero.

The same logic can produce a result that is surprising at first. If South contains transactions of 40 and -40, its sum is zero, so the region is hidden even though neither source transaction is zero. The filter checks the group’s summarized result, not each underlying row. If you need to remove individual zero transactions, use a source-data filter or a separate helper column before building or refreshing the PivotTable.

Choose the right value field and filter level

A PivotTable can contain more than one field in Values, for example Sum of Sales and Count of Orders. When you create a value filter, choose which measure should determine whether an item is kept. Filtering Region by Sum of Sales does not necessarily give the same result as filtering it by Count of Orders. A region with no sales total could still have records, and a count is generally not zero if records are present.

Filters can also behave differently in nested row fields. Imagine Country above City in the Rows area. Applying a value filter to Country evaluates countries; applying one to City evaluates city items in the PivotTable’s hierarchy. If the report has multiple levels, inspect the field whose drop-down you used and verify the visible result at each level. A filter on one field is not a general instruction to remove every zero displayed anywhere in the PivotTable.

For a PivotTable with several measures, decide which measure answers the reporting question. If the goal is to hide products with no net sales, use the sales sum. If the goal is to hide products with no transactions, use a count or another measure that represents activity. Selecting the wrong measure can hide categories that readers expect to see.

Zero, blank, and a displayed 0.00 are not the same

Excel treats a numeric zero and a blank cell as different values. A filter for “does not equal 0” is not automatically a filter for “is not blank.” Blank source entries may be ignored by a sum, while they can affect counts differently depending on the calculation. If the requirement is to exclude blank categories or empty source values, use the appropriate label or value filter for blanks rather than assuming the zero filter will do it.

Number formatting can also make a small nonzero result look like zero. For example, a value such as 0.004 may display as 0.00 when the field is formatted to two decimal places. A filter for a value that does not equal zero can retain that item because the underlying value is not exactly zero. Increase the displayed decimal places to inspect it, or decide whether the business rule should treat rounded values as zero.

Likewise, a true zero can arise from positive and negative values cancelling out. Decide whether the report should hide a zero net total or whether it should show categories that had activity but ended at zero. Those are different questions and require different measures or a separate activity count.

Keep the filter useful after refreshing

Value filters are generally more useful than manually unticking a category in the item checklist when the source data changes. A value filter states a rule, so after a PivotTable refresh, Excel can recalculate the summaries and apply the rule to the updated results. A manually unchecked item is a selection of particular labels, not a rule that says to exclude every item whose current total is zero.

When new records are added, refresh the PivotTable to recalculate it. In Excel for Microsoft 365 on Windows, right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. If new rows are not included, check whether the PivotTable’s source range includes them; converting the source list to an Excel table before creating the PivotTable can make a growing source easier to manage. A refresh cannot include records outside the PivotTable’s source.

If the filter seems to disappear or the results look stale, check that the PivotTable refreshed successfully and that the same value field is still used in the filter. Also consider whether another report filter or slicer is restricting the items. Those controls can combine with the value filter and make the visible list shorter than expected.

Alternatives when a value filter is not the right tool

Hide individual categories: Open the row or column field’s item list and clear the checkbox beside a specific label. This is suitable for a one-off presentation, but it does not dynamically identify all items that total zero.
Exclude zero source records: Filter the source data or add a suitable source-data condition before summarizing. Use this when the records themselves should not contribute to any PivotTable result, rather than merely hiding categories with a zero total.
Show a different measure: Add a count of records alongside the sum. This can reveal categories with activity even when positive and negative amounts cancel. It also helps distinguish “no transactions” from “net total is zero.”
Change the calculation or data preparation: If the rule is based on rounded totals or a business-specific threshold, prepare a field that represents that rule in the source data, then use an appropriate PivotTable value filter. Document the rule so readers know why an item is excluded.
Hide zeros visually: Number formatting may be appropriate when the goal is a cleaner display but the categories and underlying values should remain in the report. This is not equivalent to filtering them out, and it may leave blank-looking cells or totals that still include those values.

Common problems and how to fix them

“Value Filters” is unavailable: Confirm that you opened a row or column field drop-down in a PivotTable and that a value field is present. Clicking a worksheet cell outside the PivotTable, or opening a different control, will not show the relevant filter options.
The zero item still appears: Check which value field the rule uses, whether the field is summarized as Sum, Count, or another calculation, and whether the underlying result is a small nonzero value formatted as 0.00. Also check whether the item appears in another field level or in a total rather than as a filtered category.
Some categories vanish unexpectedly: A value filter hides categories based on the selected summary. A total of zero can result from cancellation, so inspect the source records or add a count measure if activity matters.
The report does not reflect edits: Refresh the PivotTable and verify its source range. Edits to source data do not necessarily update a PivotTable’s displayed results immediately.
A grand total remains zero: Filtering categories is not the same as filtering the grand total cell. The total is calculated from the items that remain visible and may itself be zero, including when positive and negative values offset.

On Excel for Mac, the same general approach is to open the PivotTable field’s filter menu and choose its value-filter option; labels and menu placement can vary by release. Excel for the web also supports PivotTable filtering, though available controls and their layout can differ from the Windows desktop interface. If a command is not visible, confirm that the selected object is a PivotTable and consult the controls available in that version rather than applying a worksheet AutoFilter to the report.

Frequently Asked Questions

Can I filter a PivotTable to show only nonzero values?
Yes. Apply Value Filters > Does Not Equal to the relevant row or column field, select the value field, and enter 0. This keeps nonzero summaries, including negative values.

Does “Does Not Equal 0” remove zero rows from my source data?
No. It hides PivotTable items according to their summarized value. The underlying records remain in the source and can still contribute to other calculations or reports.

Why does a row showing 0.00 remain in the PivotTable?
The stored result may be a small nonzero number that rounds to 0.00 with the current number format. Show more decimal places to check the underlying result, and decide whether the filter should use exact zero or a rounded business rule.

Can I hide zero totals but keep categories with transactions?
A zero-sum filter alone cannot tell whether a category had no transactions or whether positive and negative amounts cancelled. Add a count measure or another activity indicator, then use that measure when deciding which categories to retain.

Will the filter work after I add data?
Refresh the PivotTable so Excel recalculates its summaries. The source range must include the new records; a filter cannot show results based on data that is outside that range.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.