Hide values of zero in pie charts

[pagrender]

Hello Mighty Excel Masters,

Can anyone tell me how to hide data with a value of zero in a pie chart?

I have included a very simplified summary of my spreadsheet. My actual file contains many more data values and many more zeroes.

My data looks like this, pasted in cells A1:B6 ("Strategies" is in A1, 2,000.00 is in B4):
Column A Column B
<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=165 border=0 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 77pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=102 height=17>Strategies</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 47pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=63 x:str="Amount"> Amount </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num="7200"> 7,200.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num="0"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num="2000"> 2,000.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>4</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num="0"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num="2400"> 2,400.00 </TD></TR></TBODY></TABLE>

Can someone tell me how to have the pie chart ignore the zeros in Strategy 2 and Strategy 4? I'd like to make the zeros disappear if that is possible.

I thought it would work if I substitute NA() for the value, but I was not able to make it work. I had put this formula in C2: =IF(B2=0,NA(),B2) but was not successful. Do you have any suggestions?

Thanks,
Pete

 

[Smitty]

Try:

=IF(B2,B2,NA())

HTH,

 

[pagrender]

It still leaves the zero values when I display percentages. In addition, Strategy #2 and #4 are in the legend.

I had the chart reference column C and ignore column B. Is that correct?

 

[Smitty]

If you want the strategies hidden as well, I'd try auto filtering on column to not show blanks instead. That way you'll hide the entire row, not just the zero values.

 

[pagrender]

I'll give that a shot. Thanks for the info.

I do have one more question. Is there a way to incorporate updating the autofilter in a macro? I'm trying to prepare something for my boss and I want it as simple to use as possible. I already have some macros in this file so if we can have the autofilter update through VB, I'll just attach it to the end of another macro.

Thanks!

 

[tusharm]

You may also want to check Andy Pope's http://www.andypope.info/charts/piezeros.htm