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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Wonder what happened to the data behind a PivotTable? It could be in a number of places, and tracking it down could be a ...
Discover MoreConditional formatting is very powerful, and you can use it to dynamically adjust how your data looks. Excel allows you ...
Discover MoreIf you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-10-30 19:55:40
Justin
For Excel 2013 & later
A work around for excluding 0 from any calculation in a pivot table is to build a helper column and use the helper column in the pivot.
The helper column should have an IF function with NO false clause.
e.g. Suppose column B has a mix of 0 and non 0 numeric values and you do not want to include 0 in the pivot table average.
1. create a formula in an adjacent column with the following =IF(B2>0,B2) note NO false part of the formula.
cells in the new column where the adjacent cell in column B is 0 have a FALSE value
Use this new column in your pivot table.
This method also works in the data model, except instead of FALSE the result is effectively a NULL
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 © 2021 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