Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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.

Excluding Zero Values from a PivotTable

by Allen Wyatt
(last updated March 22, 2014)

5

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, and 2013. You can find a version of this tip for the older menu interface of Excel here: Excluding Zero Values from a PivotTable.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Saving Changes in the Personal Workbook

The Personal workbook is a special place used to store information and macros that you can access from all the other ...

Discover More

Disabling Shift Key Use when Opening a Workbook

Open up a workbook, and Excel normally runs the macros associated with that workbook. You can disable the automatic running ...

Discover More

Adding Common Line Spacing Options

Sometimes the options presented by Word's tools don't exactly meet our formatting needs. Rather than changing the tools ...

Discover More

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!

More ExcelTips (ribbon)

Default Formatting for PivotTables

Wish there was a way to define how you want PivotTables formatted before you actually create the PivotTable? You may be out ...

Discover More

Displaying a PivotTable's Name in the PivotTable

When you create a PivotTable, it can have a name. You may want this name to appear within the PivotTable itself. There is no ...

Discover More

Easy Filtering Specifications for a PivotTable

When you want to include specific records from a source table into a PivotTable, you need to employ some sort of filtering. ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five less than 5?

2016-07-15 12:58:06

Mary

Thank you... This was a perfect solution!


2015-06-15 12:35:12

bmr

Well, this option just doesn't seem to work in Mac Excel 2008. What a bunch of junk. they always just show as zero.


2014-03-24 09:35:15

Bryan

I don't know that I agree with changing your source data. Often times a null (blank) value is distinctly different from a 0. Excel doesn't generally treat them differently, but that's because it's a spreadsheet program and not a database program. It depends on the circumstances and your specific data structure, of course.


2014-03-22 15:16:35

EC

Another way to achieve a "clean" report is to remove 0 values in the data table with "Ctrl H" and replace 0 with delete. Then go to the pivot table and right click in the pivot table - choose "PivotTable Options". In the tab "Layout and Format" check the box "For empty cells show:" and leave the specified cell blank.


2014-03-22 05:44:55

Carlo

Why do not you translate your products into other languages ​​(eg Italian) so that I can buy them? (excuse the errors in the English language).


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.