Excel – Hide Records with Zeroes in Pivot Table Calculations

One of the most asked questions of beginning, and sometimes seasoned, pivot table users is “How do I hide the entries in a pivot table whose totals equal zero?” On first blush, this seems like an easy feat, but users quickly discover that it’s not as easy as predicted. There are ways to sort the source data and then exclude the entries with zero values, but that task of sorting and filtering the source data would have to be performed each time the source data is updated. This is not an appealing prospect.

There is actually a very easy way to not display pivot table records that equal zero.

Excel – Mixed Pivot Table Layout

Microsoft Excel Pivot Tables is one of the greatest inventions known to man; second only to those buttery mints they bring you in fancy restaurants. Even the greatest of ideas are not without their issues. Take for example the Report Layout feature in Pivot Tables. Excel gives the user three options with which to display hierarchically (wow; that’s a hard word to spell) related data.

Compact Form
Outline Form
Tabular Form

Let’s examine what makes each layout unique.

Compact Form

All row-based data is combined into a single column, one row per line item, and indented to reflect the position within the hierarchy.