Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Excluding Zero Values from a PivotTable.
William has a PivotTable based on parts drawn from a store for a particular piece of equipment. Some parts have not ever been drawn upon and hence the usage is zero. William wonders if there is a way to display in the PivotTable only parts with usage greater than zero.
There are a couple of ways you can handle this situation. One way, obviously, is to remove all the zero-value items from the data used to create the PivotTable. Another way is to go ahead and create the PivotTable, but then apply a filter to the PivotTable to remove those items with a zero value.
To apply an AutoFilter after the PivotTable is created, all you need to do is select the column to the immediate right of the PivotTable and then create the AutoFilter. (Create the AutoFilter as you normally would in your version of Excel.) Excel is smart enough to know that the AutoFilter should not apply to the blank column, but instead does its work on the rows that make up the PivotTable. Click the triangle to the right of the column on which you want to filter, then select Custom. You can then specify that the filter should only include items with a value greater than zero.
Another thing you can try is handy if the item you want to filter (in this case, the Parts field) is either a column field or a row field. Simply right-click the field after it is placed in the PivotTable and then choose Settings. You can then specify that you want a particular value (in this case, the value 0) omitted from the PivotTable.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3276) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Excluding Zero Values from a PivotTable.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
You can format PivotTables using either manual formatting or automatic formatting. You need to be careful, however, as ...
Discover MoreChanging the data source PivotTables go to can be a bit tricky. This tip explains what can happen when you re-point your ...
Discover MorePivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-01-27 11:45:50
Willy Vanhaelen
@Rodney
The slicer feature has been introduced in Excel 2010. This tip applies to Excel 97 ... 2003 (see the first and last paragraphs of this tip). So you posted your comment in the wrong place.
2017-01-26 18:39:47
Rodney J Plunkett
Another option is to use the Slicer feature of pivot tables.
1. Set up the slicer for the field you wish to exclude the zero values.
2. Hold the Ctrl key down and unselect the zero value.
The pivot table will now only display the records with values over zero.
The advantage of this is that you eliminate the visual distraction of the filter symbols in the header row of each field in the pivot table.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Comments