If you have a PivotTable with data that includes zeros, you might want to hide the zeros to make the PivotTable more readable. To do this, you can use a number filter and select "Does not equal" 0 from the drop-down list. Or, you can format the cells to display a dash, blank, or other character instead of 0.
To hide zero values in a PivotTable:
- Click any cell in the PivotTable.
- On the PivotTable Tools tab, in the Analyze group, click Field Settings. If you don't see the PivotTable Tools tabs, make sure that you selected a cell in the PivotTable.
- In the Field Settings dialog box, click the Layout & Print tab.
(Optional): To apply these settings to all future PivotTables that use this data source, click Set as Default. Otherwise, these settings will only be applied to this PivotTable.
(Optional): To apply these settings just to blank cells (and not cells with other characters), select the "For empty cells show" check box, and then enter a value or character in the box.
(Optional): To apply these settings just to cells with errors (and not blank cells or cells without errors), select the "For error values show" check box, and then enter a value or character in the box.
(Optional): To apply these settings just to cells with zeros (and not blank cells or cells without zeros), select the "For zero values show" check box, and then enter a value or character in the box.
Note: If you want to apply different formatting options depending on whether a cell contains an error value, zero value, or other value, you can use conditional formatting instead of (or in addition to) number formatting. For more information about using conditional formatting with numbers, see Apply conditional formatting to cells - Excel - Office Support.
