Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Excluding Zero Values from a PivotTable.
Written by Allen Wyatt (last updated June 6, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 (9638) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Excluding Zero Values from a PivotTable.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When you refresh the data in a PivotTable, Excel can play havoc with whatever formatting you applied. Here's how to ...
Discover MorePivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple ...
Discover MoreOne of the ways you can use PivotTables is to generate counts of various items in a data table. This is a great technique ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-12-14 16:14:37
Lisa Gray
Hi Allen,
What if you have a pivot table with multiple columns and you would like to eliminate rows that have zero value across all columns? Is this possible?
Here is an example of what I am talking about; I didn't want to share any identifiable work information - not sure why I thought of day of the week pants?! :)
(see Figure 1 below)
Figure 1.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments