Suppress Zeros in a Pivot Table

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.

Simon Shaw wrote:
> 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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks,

This was what I ended up doing... I just hoped there was an easier way.


"Debra Dalgleish" wrote:

> 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.
>
> Simon Shaw wrote:
> > 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
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>