How to hide zero values in a pivot table chart

[johnpaul]

I have been researching this for a while, but haven't found out how to do.

I want to hide values in a column that are zero.

One way to do it is to use the Visual Cues to set the color to be white when the value is zero or below. The problem with this is that if the value is negative, then it won't be visible.

Any ideas? Thanks, Jp.

[jonathandienst]

Hi

The header line in a pivot table takes its display cues from the first line, so if the first line is set to display white, so will the header. You may be able to get around the problem using cell custom formats.

Or if you will allow the first row to display the zero, then use

If( [Non Bill] = 0 And RowNo() > 0, White( ) )

Not quite what you want, but perhaps the best possible if you don't want to use the space approach.

Hope this helps

Jonathan

[its_anandrjs]

Hi,

Ok i got it for this you want in header the Sum value that is 4 is display in pivot table if so then put

Sum( HrsBill ) in pivot chart only because pivot chart works differently because in pivot chart the Sum value depend on dimension that you put so after putting Sum( ) function

Go to presentation tab -> Select Dimension and Expression -> that is ColA in example if you have working application do it same there -> and then check mark Show Partial Sums -> Subtotals on Top / Subtotals at Bottom then you see the Column Heading.

See the attached sample file with different variation.

HTH

Rgds

Anand

[#4]

Hi,

You can set the expression like below to hide "0" values.

if(Hours=0,'',Hours)

if(HrsBill=0,'',HrsBill)

if([Non Bill]=0,'',[Non Bill])

Hope you were looking for the above expression.

Thanks,

Sajeevan

[#5]

thanks you

[#6]

Thanks a lot, it can solve !!