Excel Pivot Table: How to Remove Zero Values

Charts & pivot tables · Updated November 18, 2026

To remove zero-valued results from an Excel PivotTable, open the drop-down for the row or column field, choose Value Filters, then set a condition such as Does Not Equal 0. If you only want zeros to disappear visually while keeping the rows and totals, apply a custom number format with an empty zero section instead. The right choice depends on whether you want to filter records out or merely hide their displayed zeros.

What “remove zero values” can mean

A PivotTable summarizes source records, so a zero shown in the report can have several causes. A category’s sum might equal zero because its positive and negative amounts cancel out. A count or other calculation might genuinely return zero. Alternatively, the source field may contain zero as a row label, or the PivotTable may be showing an empty cell that someone is mistaking for zero. These cases call for different fixes.

Before changing the report, decide what should happen to the data and the summary:

  • Exclude a result from the report: filter out categories whose summarized value is zero. The filtered category is no longer visible, but its source records are not deleted.
  • Keep the category but hide its zero: use a number format that displays zero as blank. The value remains in the PivotTable and may still contribute to totals.
  • Remove zero records from the analysis: change or filter the source data, then refresh the PivotTable. This changes which records are included in the summary.
  • Hide a zero that is a label: filter the row or column item itself, rather than using a value filter.

These distinctions matter in reports where a blank-looking result could be interpreted as missing data rather than a known zero.

Filter out categories with a zero result

For a common example, suppose a PivotTable has product names in Rows and summed sales in Values. To hide products whose total sales equal zero in Excel for Microsoft 365 on Windows:

  1. Select the drop-down arrow beside the row field, such as Row Labels or Product.
  2. Point to Value Filters.
  3. Choose Does Not Equal.
  4. In the filter dialog, select the value field, such as Sum of Sales, if Excel asks which summary to use, and enter 0.
  5. Select OK. The PivotTable hides row items whose selected summary equals zero.

You can instead use Greater Than and enter 0 if the report should show only positive results. That is not equivalent to removing zeros: it also hides negative results. For a balance report that should retain both positive and negative values, use Does Not Equal rather than Greater Than.

A value filter is applied to the summarized values for a field, not to individual source rows. If a product has transactions of 100 and -100, its sum is zero and the product can be filtered out, even though neither source transaction is zero. If the selected summary is a count, the filter evaluates the count; if it is an average, it evaluates the average. Check the field shown in the filter dialog so the condition is applied to the intended measure.

Hide zeros without removing PivotTable items

If you want to retain every category but make zero results look blank, use a custom number format. Right-click a numeric value in the PivotTable and choose Value Field Settings. Select Number Format, choose Custom, and enter a format appropriate to the values. For whole numbers, #,##0;-#,##0;; displays positive numbers with commas, negative numbers with a minus sign, and zero as blank. For two decimal places, use #,##0.00;-#,##0.00;;.

In a custom number format, the sections separated by semicolons describe positive values, negative values, zero values, and text, in that order. The empty third section is what suppresses the display of zero. This changes presentation, not the underlying number: calculations and grand totals still use the actual values. Someone inspecting the cell or using it in a calculation can still encounter zero, and a blank-looking cell does not mean the category had no records.

Setting the format through Value Field Settings is generally preferable to formatting a selection of PivotTable cells directly. A direct cell format can be lost or applied inconsistently when the PivotTable changes size or refreshes. For currency, select a suitable currency format through Number Format, or adapt the positive and negative sections to include the currency symbol while leaving the zero section empty. Check both ordinary values and totals after applying a custom format.

Filter a zero that appears as a row or column label

Sometimes the zero is not a summarized result. For example, a field called Region Code may contain the numeric value 0, and the PivotTable lists 0 as one of its row labels. In that case, use the field’s item filter: open the row or column label drop-down and clear the check box beside 0, then select OK. This hides the item labeled zero regardless of the values summarized beside it.

Do not confuse that item filter with a value filter. If you clear the 0 item, you are excluding source records whose label field is zero. If you apply Value Filters to Does Not Equal 0, you are excluding groups based on their summarized measure. A date field, an account code, and a sales amount can each contain a zero for different reasons, so identify which field contains the zero you mean to remove.

Why a zero may appear, or remain, after filtering

Several PivotTable behaviors can make the result seem surprising. First, an aggregate can produce zero even when the source column contains no zero entries. Positive and negative amounts may cancel, or a calculated field may return zero. A value filter evaluates the aggregate shown for its chosen field, not whether any underlying row contains a zero.

Second, the displayed value may not be exactly zero. A calculation can produce a small decimal such as 0.0000001 that is formatted to show no decimal places, making it look like 0. If the item remains after a filter that excludes exact zero, increase the displayed decimal places or inspect the value’s precision. A rounding rule in the source or a deliberate threshold may be needed if near-zero results should also be excluded; an exact zero filter does not establish that rule.

Third, a blank and a numeric zero are different. A blank source cell may be summarized differently depending on the field and calculation, while a zero is a numeric value. The PivotTable option that displays text in empty cells is for empty cells; it is not a setting for hiding numeric zeros. If an apparent zero is actually a blank, inspect the source and the value field’s summary method before changing the display.

Finally, filters can be attached to different fields. A row-field value filter may be working correctly while a similar item in another row or column field remains visible. Confirm which field owns the drop-down you used and which value field the rule references.

Refreshes, totals, and source-data changes

Filtering a PivotTable does not delete source data. If new records are added, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. A value-filter condition normally remains part of the PivotTable’s report filtering, but the visible items can change as the refreshed summaries change. For example, a category that previously totaled zero may appear after a new transaction makes its total nonzero.

Formatting zeros as blank also leaves the data untouched, and it does not remove the category. This is useful when a fixed report layout matters, but it can be misleading if readers need to distinguish a true zero from missing information. Add a note to the report or choose a visible marker if that distinction is important.

If the aim is to exclude records from the underlying analysis, filter or correct the source table instead, then refresh. Be cautious: removing every source row with an amount of zero may be inappropriate when those rows carry useful labels, dates, or counts. Keep a copy of the original data and confirm that the revised PivotTable totals match the intended scope.

Choosing the best method for a report

Use a value filter when zero-total groups should not appear in the report at all. Use a custom number format when all categories should remain visible but zero figures add clutter. Use an item filter when the zero is itself a category label. Change the source only when the records themselves should not be part of the analysis.

Consider the audience as well as the mechanics. In a compact dashboard, blanking zeros can reduce visual noise. In an audit or reconciliation report, hiding values may obscure meaningful information, so showing 0 or labeling it explicitly is often clearer. If negative balances are possible, avoid using a greater-than-zero filter unless excluding negatives is intentional. If the report is shared, explain whether blank-looking cells mean zero, no records, or unavailable data.

On Excel for Mac, the same basic distinction applies, although the ribbon layout and dialog presentation can vary by release; use the field drop-down for filtering and the value field’s number-format settings for display. In Excel for the web, available PivotTable controls may differ from the Windows desktop app. If a particular filter or number-format control is missing, open the workbook in desktop Excel rather than changing the source just to work around a limited control.

Frequently Asked Questions

How do I hide zero values but keep the PivotTable total?
Apply a custom number format with an empty zero section through Value Field Settings > Number Format. The zero is hidden from view but remains part of the data and calculations, including totals.

How do I remove rows where the PivotTable total is zero?
Open the relevant row field’s drop-down, choose Value Filters > Does Not Equal, select the correct value field, and enter 0. This hides groups whose selected summary equals zero.

Will filtering out zero values delete source data?
No. A PivotTable filter hides items from the report; it does not delete the source records. To exclude source records from the analysis, change the source data or its filtering and refresh the PivotTable.

Why does a zero still show after I apply a value filter?
The item may not equal exactly zero, the filter may reference a different value field, or the zero may be a row label rather than a summarized result. Increase decimal precision and verify the field and filter type.

Can I hide zeros in just one PivotTable value field?
Yes. Set the number format through that value field’s Value Field Settings. If the same field appears more than once with different summaries, check each instance, because each displayed value field may need its own format.

Related

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.