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 DataFilterAutoFilter
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 DataFilterAutoFilter
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

Suppress Zeros in a Pivot Table
 

go to Tools - Options - View and uncheck "Zero Values"
This is what worked for me.

"Simon Shaw" wrote:

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 DataFilterAutoFilter
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