Suppressing Zero Values in PivotTables

There are ways to format the 0 so that you do not see it in excel, it's already been mentioned. However sometimes you want to copy and paste the report or print it. The formatting doesn't seem to take care of that and will still show "0" or "0.00".

One workaround I discovered was to use a calculated field in the pivot table ( Won't work if you do add the pivot to the data model ) and have the calculated field produce an "error" on your 0 values using divide by zero.

For example say you are doing a pivot and trying to get Sales from Column1 but omit "0" values:

=( SALES / SALES ) - 1 + SALES

What this does is take that value when calculating the pivot:

Since 0/0 = DIV/0 error it errors out and since you checked next to "For Error Values Show:" and left the field bank, it will give you a blank result.

This should leave your other values alone, for example if you had "2" in Sales column:

( 2 / 2 ) - 1 + 2 = 2

@Ed, what you say is partly true.

To work around this issue, you need check an option in the field settings to "Include new items in manual filter".

Unfortunately, this option is unchecked by default for all 'non-value' Pivot Table fields, where you might want to apply a filter.

Of course, a small piece of VBA code can fix this problem :)

Be careful when using the filter to exclude zeros!

When the data change, with this method the filter will only show values equal to those that had been checked in the filter prior to the data-refresh.
So for example, if the original data did not have anything equal to three, then the filter-refresh will not bring any "3" values into the new table.

This applies to both pivot-table filters and to regular data-table filters (inc. quick-filters). In tables' filters, it is better to exclude by using the "Number Filters" selection from the filter button's pull-down menu, then select "Does Not Equal"....

Pivot tables don't seem to have that flexibility. If you refresh the table with new data you must recreate the excluding filter; open the filter, re-check "(All)", then de-select the value you want excluded.

Full disclosure: I don't use pivot-tables much, so I could be wrong, but this is what I noticed when I tried this. Perhaps a pivot-table maven could confirm or correct ?

I used to deal with this issue in the old Fortran days when we'd print reams of paper with only a few non-zero values hidden among acres of zeros.

I eventually learned to use formatting to alter values of zero to be a single
decimal point. Users could see there was a numeric value (as opposed to a blank), but the zeros no longer obscured the non-zero numbers.

You can do the same with Excel formats, eg: '0.00;-0.00;"."_0_0;@'

@Carlos, I too wondered about how this particular option would work.
I may be wrong it, but about it, but I think what was meant was:
"Identify non-zero records in the source data (perhaps by setting up a helper column). Then use the helper column in the PivotTable Filter area to display only those records."

My comment above, takes a similar approach, but without having to add a helper column in the source data.

"You can also use a filter on the data, before creating the PivotTable, that would show only rows that are not equal to zero. In other words, create a PivotTable using just the filtered data."

Could you please explain a little more? As far as I know, when you select the pivot table source data, besides filtered data you must include titles too, and this selection includes no filtered records in the source range. I tried selecting only visible cells before building Pivot Table but no success.

Thank you,

Carlos

Yet another way would be to add a duplicate of the Values field in the PivotTable's Filter area (at the top left), and in that field, uncheck the '0'.
This will leave only the records with non-zero values displayed in the PivotTable