Excel Pivot Table: How to Exclude Zero Values?

Charts & pivot tables · Updated November 18, 2026

Yes. In an Excel PivotTable, use a value filter such as Does Not Equal 0 to hide row or column items whose summarized value is zero. If you only want zeroes to disappear visually while keeping those items and totals, apply a number format instead; those two approaches produce different results.

How to filter zero-valued items

For Microsoft Excel for Microsoft 365 on Windows, apply a value filter to the row or column field whose items you want to remove. The filter evaluates the PivotTable’s summarized measure—for example, total sales—not whether an individual source-data row contains zero.

  1. Click the dropdown arrow next to the row field, such as Row Labels or a category name. For a column field, use that field’s dropdown instead.
  2. Choose Value Filters, then Does Not Equal….
  3. In the filter dialog, select the value field to evaluate, such as Sum of Amount, and enter 0.
  4. Click OK. The PivotTable hides items whose selected value equals zero.

For example, imagine a PivotTable showing total sales by product. If Product A totals $250, Product B totals $0, and Product C totals -$15, a filter set to Does Not Equal 0 retains A and C and hides B. Choose Greater Than 0 only if you also intend to exclude negative totals. A negative amount is not zero, so the two filters are not interchangeable.

What the value filter actually evaluates

A PivotTable groups source records and summarizes a value field using a calculation such as Sum, Count, or Average. A value filter acts on the resulting summary for each item in the selected row or column field. It does not automatically delete source records, nor does it necessarily hide every individual zero shown elsewhere in the report.

That distinction matters when a zero is produced by several records. If one category has a sale of 40 and a return of -40, its sum is zero, so Does Not Equal 0 hides the category even though neither source record was zero. Conversely, a category with a zero-valued transaction and a separate sale of 40 has a total of 40 and remains visible. The filter is based on the aggregate, not a search for zeroes in the underlying rows.

The selected value field also matters. If the PivotTable contains both Sum of Sales and Count of Orders, choose the one that represents the question you are asking. An item can have a nonzero sales total but no qualifying count under a particular setup, or the reverse.

Choose the right kind of zero removal

Before filtering, decide what “exclude zero” means for the report. Common goals include hiding categories with a zero total, excluding individual transactions whose amount is zero, and keeping the layout intact while making zeroes less noticeable. Each calls for a different method.

  • Hide categories with a zero summary: Use a PivotTable value filter, such as Does Not Equal 0.
  • Exclude source records that have a zero amount: Add a field that identifies eligible records, then filter that field in the PivotTable.
  • Keep all categories but make zeroes appear blank: Apply a number format that displays no characters for zero.
  • Hide empty cells: Change the PivotTable setting for empty cells. This affects blanks, not actual numeric zeroes.

These choices can change both what readers see and what contributes to the report. In particular, hiding a category with a value filter does not mean its source transactions have been removed from the workbook.

Exclude source transactions that equal zero

If the goal is to prevent zero-amount records from contributing to the PivotTable, a value filter on the final total is not enough. A category can still have a nonzero total after including zero transactions, and a zero total can result from positive and negative records cancelling each other out.

A reliable approach is to add a helper column to the source data, include that column in the PivotTable source, and filter it to keep only eligible records. For example, with each transaction amount in B2 and a helper column in C, enter =AND(ISNUMBER(B2),B2<>0) in C2 and fill it down. The formula returns TRUE for numeric amounts other than zero, and FALSE for zero, blanks, or nonnumeric entries. Name the helper column something clear, such as Keep transaction.

After refreshing the PivotTable, place Keep transaction in the Filters area or use its field dropdown, then select TRUE. This excludes the flagged records before the remaining transactions are summarized. If negative transactions should also be excluded, this test does not do that: negative numbers are nonzero and return TRUE. Adjust the eligibility rule to match the reporting requirement rather than assuming that all negative values are errors.

Another option is to filter the source data and build a PivotTable from a separately prepared range, but simply hiding rows in a worksheet is not a dependable way to exclude them. PivotTables generally include records from their source even when those rows are hidden or filtered on the source sheet. A helper field makes the inclusion rule explicit in the PivotTable itself.

Hide zeroes without removing categories

Use formatting when the report should retain every category, its position, and its contribution to totals, but readers do not need to see a zero in each value cell. Select a value cell, open Value Field Settings, choose Number Format, and set a custom format with an empty third section, such as 0;-0;;@. In a custom number format, the sections are for positive numbers, negative numbers, zero, and text; the empty zero section displays no characters for a numeric zero.

Applying the format through Value Field Settings is preferable to formatting a few individual cells because the setting belongs to the value field and is more likely to remain consistent when the PivotTable changes. The exact dialog wording can vary slightly by Excel build. The format changes presentation only: the zero is still present in the PivotTable, and calculations and grand totals still use it.

Do not confuse a blank-looking cell with a value that has been excluded. A displayed blank might be a formatted zero, a genuinely empty result, or a blank source value. If readers need to distinguish those cases, use a visible label or a separate status field rather than relying on appearance alone.

What happens after refresh or when filters change

A PivotTable value filter is tied to the field and measure it evaluates. When you refresh the PivotTable, Excel recalculates the summaries and applies the filter to the updated values. An item that was nonzero before a refresh can disappear if its new total becomes zero; an item can reappear if its total becomes nonzero, provided the report’s other filters allow it.

Other report filters can affect the result because they change which records contribute to each summary. For instance, a product might total 20 across all months but zero for the month selected in a report filter. The value filter evaluates the currently displayed calculation in that filtered context. If the outcome seems unexpected, check the selected measure, the active report filters, and whether the value field is summarized as Sum, Count, or another calculation.

If you use a helper column, refresh after changing or adding source records so the PivotTable can use the updated eligibility values. When practical, keep the source data in an Excel Table and use that table as the PivotTable source; tables expand as records are added, although the PivotTable still needs to be refreshed to reflect new rows.

Common problems and how to fix them

  • Value Filters is missing or unavailable: Click the dropdown for a row or column field, not a value cell. Value filters are applied to the items in a field and evaluate a selected value field.
  • The wrong items disappear: Reopen the filter and confirm the measure selected in the dialog. A filter based on Count of Records can behave differently from one based on Sum of Amount.
  • Negative values vanish too: Replace Greater Than 0 with Does Not Equal 0 if negatives should remain.
  • Zeroes are still visible in the source: A PivotTable filter changes the report, not the underlying worksheet records. Use a helper field if those records must not contribute to the summary.
  • Blank cells still appear: A numeric zero and an empty cell are different. Check the source data and the PivotTable’s empty-cell display setting; a zero value filter does not necessarily remove blanks.
  • Totals seem unchanged after hiding an item: Review the report carefully. A value filter controls which items are displayed, while a number format only changes their appearance. If a total must be recalculated using only selected source records, filter the records using an appropriate source field or helper field.

Value filter versus formatting

The main comparison is whether the item should remain part of the report. A value filter hides items whose evaluated summary meets the condition. Formatting leaves items and calculations intact but suppresses the printed or displayed zero. A helper field is appropriate when the rule applies to individual source records before they are summarized.

For a management report where categories with no activity should not take up space, a value filter is usually the clearest choice. For a fixed comparison where every category must stay in the same position from period to period, formatting may be preferable. For transaction analysis where zero-value rows must not be counted or summed, make the record-level rule explicit in the source data and filter on it. There is no universal “hide zero” switch that gives all three results at once.

On Excel for Mac, the same general PivotTable approach is available, though the placement of controls can differ by release. In Excel for the web, PivotTable editing options may be more limited than in the Windows desktop app; if a value-filter command is not available in the current interface, open the workbook in desktop Excel to apply it.

Frequently Asked Questions

Can I exclude zero values from a PivotTable without changing the source data?
Yes, if you want to hide items whose summarized value is zero: apply a value filter to the relevant row or column field. To exclude individual zero-valued records from the calculation, use a record-level filter such as a helper field; hiding zero-summary items is not the same operation.

Does Does Not Equal 0 remove negative values too?
No. It excludes values equal to zero and keeps negative values, assuming they are the selected field’s summarized results. Greater Than 0 excludes both zero and negative results.

Why does my PivotTable show zero when the source cell is blank?
A blank source entry can be treated differently depending on the value field and summary calculation, and a formula or calculation can also produce a numeric zero. Check the underlying records and the field’s summary method. The setting for text shown in empty cells affects empty results; it does not turn actual zeroes into blanks.

Can I hide zeroes but keep the grand total?
Yes. Use a zero-suppressing number format if you want the zero-valued cells to look blank while preserving the layout and calculations. A value filter can hide items from the displayed report, so verify that its visible subtotal and total behavior matches the report you need.

Will the zero filter stay after I refresh?
Normally, the PivotTable retains its filter and reevaluates it against refreshed summaries. The items shown can change because their values or other active filters changed. If the result is unexpected, verify the filter condition, chosen measure, and source range.

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.