To hide rows whose PivotTable result is zero in Excel for Microsoft 365 on Windows, open the drop-down for the field in the Rows area, choose Value Filters > Does Not Equal, select the value field to check, and enter 0. The filter hides matching PivotTable items without deleting or changing the source data.
How a zero-value filter works
A PivotTable summarizes source records into groups, such as sales by product or hours by employee. A value filter checks the summary for each item, rather than checking each individual source record. If the selected summary equals zero, Excel excludes that item from the displayed PivotTable; if it is nonzero, the item remains.
For example, suppose a PivotTable lists departments in Rows and Sum of Amount in Values. A Value Filter on the department field can hide departments whose summed amount is 0. The same principle applies when the summary is a count, average, or another value field: choose the specific summary you want Excel to evaluate.
This is different from manually hiding a worksheet row. The item is filtered out of the PivotTable results, and Excel can show it again if you remove the filter or change the data so its summary no longer meets the condition. The original records remain in the source range or table.
Steps to hide zero rows in Excel for Windows
- Click inside the PivotTable so its contextual ribbon tabs appear.
- Find the drop-down arrow beside the row field you want to filter. Depending on the layout, it may appear beside the field name or beside Row Labels.
- Choose Value Filters, then choose Does Not Equal.
- In the filter dialog, choose the value field to test, such as Sum of Amount, and enter 0 as the comparison value.
- Select OK. Items whose selected summary equals zero should disappear from the visible results.
Use the drop-down for the field in the Rows area, not a text label filter. Label Filters look at the item names—for example, whether a product name begins with “A”—whereas Value Filters evaluate the summarized numbers. If the PivotTable has several row fields, apply the filter to the field whose items you want to hide.
A concrete example: sales by product
Imagine source records with Product and Amount columns. Product A has sales of 80 and 20, Product B has sales of 40 and a return of -40, and Product C has sales of 15. A PivotTable using Product in Rows and Sum of Amount in Values shows totals of 100, 0, and 15. Applying Value Filters > Does Not Equal to Sum of Amount hides Product B. It does not remove the sale and return records; it filters the grouped result.
This example also shows why a zero total does not necessarily mean there are no records. Positive and negative amounts can cancel out. If your intention is to hide items with no source records, or to hide items with a count of zero, filter the relevant count field instead of the sum. Choose the measure that represents the question you actually want to answer.
Choosing the right value field
A PivotTable may contain more than one measure, such as Sum of Amount, Count of Orders, and Average of Amount. The filter dialog lets you select which value field governs the test. That choice matters: a department could have a zero net amount but a nonzero order count, or an average of zero even though its total is not zero.
For a filter based on sales totals, choose the sum field. For a filter based on whether records exist, a count may be more relevant. If the PivotTable displays multiple value fields, the filter still needs a particular field as its criterion; it does not automatically hide an item just because any one displayed measure is zero. Consider the intended result before applying the filter, and check the field selected in the dialog if the outcome looks unexpected.
Zero, blank, and items with no data are different
A numeric zero is a value. A blank is an empty or missing result, and an item with no data may have no matching source records at all. These cases can look similar in a report, especially if number formats display zeros as dashes or blanks. A filter for Does Not Equal 0 is specifically a test against zero; it is not a general “hide anything empty” command.
Check the underlying displayed value and the number format before diagnosing a blank-looking cell. A custom number format can make zero appear as a dash or suppress its display, even though the PivotTable value remains zero. Also note that Show items with no data, where available for a field, concerns items without data in the PivotTable; it is not the same setting as filtering out items whose summarized value is zero.
If you need to treat blanks and zeros alike, first decide whether the PivotTable should exclude both, and test the relevant filter options with a small sample. The treatment of empty source cells can depend on the field and summary operation. Do not assume that changing a display format removes an item: formatting changes what appears in a cell, not which PivotTable items pass a value filter.
Filtering more than one row or column field
Value filters can be useful in a PivotTable with several grouping fields. For example, if Region and Product are both in Rows, you can filter the Product field by the selected value measure. The filter operates on the summary in the context of the PivotTable’s grouping and other active filters. Changing a report filter, slicer, or another field can therefore change which items qualify.
Column fields can also have their own field filters. If your goal is to remove zero-valued column items rather than row items, use the drop-down for the relevant column field and apply the value filter there. Take care when filtering a hierarchy: applying a filter to a parent-level field is not necessarily equivalent to filtering a lower-level field. Check the resulting groups, subtotals, and grand total to confirm the filter is acting at the intended level.
Refreshes and changing source data
A value filter is a condition on the PivotTable results, so it can remain in place when the PivotTable is refreshed. The displayed items may change after a refresh because the underlying totals have changed: a previously nonzero item can become zero and be hidden, while a previously zero item can become nonzero and reappear. To refresh, right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh on the Windows ribbon.
If the source range has grown, a refresh only includes the new records if they are within the PivotTable’s data source. Using an Excel Table as the source is often helpful because a table expands as new rows are added. If new records are not included, check the PivotTable data source rather than assuming the value filter is broken. After refreshing, verify the summary values and the filter criterion.
Common problems and how to fix them
- Value Filters is unavailable: Confirm that you opened the menu for a row or column field and that the PivotTable has a value field to evaluate. A label filter is not the right menu for testing a summarized number.
- The wrong items disappear: Reopen the filter dialog and verify both the selected value field and the comparison. A filter on Count of Records produces a different result from one on Sum of Amount.
- A zero-looking value remains: Check whether the displayed dash or blank is created by number formatting, and inspect the actual PivotTable value. Also confirm that the filter is applied to the field containing the item you intend to hide.
- A row with nonzero source records is hidden: The records may aggregate to zero, as when positive and negative amounts offset. Review the grouped total rather than assuming the filter examines each source row separately.
- Results change after using a slicer or report filter: Those controls change the data included in the summary, which can change whether an item meets the value-filter condition. Review the active filters and the current totals.
Alternatives and when to use them
For a standard PivotTable report, a value filter is usually the clearest way to hide items based on a summarized zero. If you only want zeros to be less prominent while keeping all categories visible, use number formatting or conditional formatting instead. Those approaches change the appearance of cells; they do not remove the zero-valued items from the PivotTable.
Another option is to filter the source data before building the PivotTable, but that changes which records are available to summarize and may not fit a report where the source must remain complete. Manually hiding worksheet rows is also a poor substitute: PivotTables can rearrange or recreate rows after refresh, and manual hiding does not express a reusable rule. Choose based on the goal: exclude grouped results, visually de-emphasize zeros, or exclude source records. These are distinct operations.
Frequently Asked Questions
Can I hide zero rows without deleting the source data?
Yes. Apply a Value Filter to the row field using the desired value field and Does Not Equal 0. The filter changes what the PivotTable displays; it does not delete the underlying records.
Why does my PivotTable still show a row with zero?
Check that the filter is applied to the correct row field, uses the correct value field, and compares against 0. A number format may also make a nonzero value look like zero, or the visible row may be a subtotal or grand total rather than an item filtered in the same way.
Does “Show items with no data” hide zero values?
No. That setting relates to displaying items with no matching data. A zero is a numeric summary, so use a value filter to exclude zero-valued results.
Can I hide rows where any of several value fields is zero?
A value filter uses a selected value field as its criterion; it does not automatically mean “hide this item if any displayed measure is zero.” You may need to choose the measure that defines the rule, or redesign the summary or source data to express a combined condition clearly.
Will the filter still work after I refresh the PivotTable?
Normally the filter condition remains, but the visible items can change when refreshed totals change. Also confirm the source range includes any newly added records, especially if the PivotTable is based on a fixed range rather than an expanding Excel Table.
Related
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
- Excel Data Validation: Can You Select Multiple Items?
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pie Chart With Breakdown of Slices: How to Make OneTo show the detail behind one slice in Excel, create a Pie of Pie or Bar of Pie…
- Excel Histogram by Group: A Practical Comparison GuideTo compare an Excel histogram by group, use the same bin boundaries for every group, count each group’s…
- Build an Org Chart From Excel Data: Steps and OptionsYes. You can build an org chart in Excel by choosing Insert > SmartArt > Hierarchy > Organization…
- Power Pivot SUM Function: Syntax, Examples, and TipsIn Power Pivot, the DAX SUM function adds the numeric values in one column, returning a total that…
- Add a Trendline in Excel for the Web: Step-by-StepTo add a trendline in Excel for the web, select a supported chart, open the Chart tab, choose…
- Timeline Chart in Google Sheets: Create and Customize OneTo make a timeline in Google Sheets, use Insert > Timeline if you want an interactive date-based view,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.