Suppress Zeros in a Pivot Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can I suppress zeros in a Pivot Table? Many of the results are zero, how can
I hide the rows without using a macro.

thanks

Simon Shaw
 
You could add a column of formulas in a hidden part of the pivot table
worksheet, then use it to filter the pivot table.

For example, if your pivot table is in cells A3:J100, and has two row
fields, enter the following formula in cell R2:

=AND(A2="",B2<>"",SUM(C2:P2)=0)

Copy the formula down to the last row to which the pivot table will
potentially reach

In cell R1, enter a heading, e.g. Filter
Select cell R1, and choose Data>Filter>AutoFilter
From the dropdown list, select FALSE

Make sure that your print area doesn't include this column.
Also, you'll have to reapply the filter if you refresh the pivot table,
or rearrange the pivot table.
 
Back
Top