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.
Written by Allen Wyatt (last updated January 6, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
If you create a PivotTable in Excel 2007, you may have problems editing or updating that PivotTable in Excel 2003. The ...
Discover MorePivotTables are often used to aggregate lots of information, and they do it beautifully. What do you do if Excel starts ...
Discover MoreExcel allows you to link to values in other workbooks, even if those values are in PivotTables. However, Excel may ...
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 © 2022 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