Ignore Zeros in a Pie Chart

[tester100099]

Hello!

I'm working on creating a pie chart that draws data from a fair number of source cells. However, a good number of these cells = 0%. How can adjust the chart data range to ignore zeros.

Here is my chart data range:

='Jan-10'!$G$3:$G$18,'Jan-10'!$K$3:$K$18

Thanks!

 

[Bill Rockenbach]

I think #n/A only will work for linear and bar charts

 

[Jason Sidell]

You can change the data labels on the chart to the custom number format 0%;;;

This will hide the 0%'s.


Jason

 

[tester100099]

Jason,

Thank you! Your suggestion successfully hid the 0%'s.

However, this still leaves all of the word labels for the 0% items. I need to be able to keep the labels for the items that actually have data, so how can I now remove the word labels of the 0%'s?

 

[Jason Sidell]

You need to add a formula to your category names on your spreadsheet, similiar to the example I have posted.

Pie Chart

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 76px"><COL style="WIDTH: 44px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">Month</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">January</TD><TD style="TEXT-ALIGN: right">908</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">February</TD><TD style="TEXT-ALIGN: right">290</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">March</TD><TD style="TEXT-ALIGN: right">160</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">April</TD><TD style="TEXT-ALIGN: right">289</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">June</TD><TD style="TEXT-ALIGN: right">949</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">July</TD><TD style="TEXT-ALIGN: right">242</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">August</TD><TD style="TEXT-ALIGN: right">802</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center">October</TD><TD style="TEXT-ALIGN: right">266</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center">November</TD><TD style="TEXT-ALIGN: right">705</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center">December</TD><TD style="TEXT-ALIGN: right">239</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=IF(B2>0,"January","")</TD></TR><TR><TD>A3</TD><TD>=IF(B3>0,"February","")</TD></TR><TR><TD>A4</TD><TD>=IF(B4>0,"March","")</TD></TR><TR><TD>A5</TD><TD>=IF(B5>0,"April","")</TD></TR><TR><TD>A6</TD><TD>=IF(B6>0,"May","")</TD></TR><TR><TD>A7</TD><TD>=IF(B7>0,"June","")</TD></TR><TR><TD>A8</TD><TD>=IF(B8>0,"July","")</TD></TR><TR><TD>A9</TD><TD>=IF(B9>0,"August","")</TD></TR><TR><TD>A10</TD><TD>=IF(B10>0,"September","")</TD></TR><TR><TD>A11</TD><TD>=IF(B11>0,"October","")</TD></TR><TR><TD>A12</TD><TD>=IF(B12>0,"November","")</TD></TR><TR><TD>A13</TD><TD>=IF(B13>0,"December","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4




Jason

 

[tester100099]

Jason,

This solves everything! Thank you so much for your help.

 

[Amy at FBBS]

This solution is SO much better than what I had been seeing! I have one problem, though. I'm selecting data from $O$8:$P$13, but my chart is only showing results from lines 10 and 11.


Col O Col P Col Q
<TABLE style="WIDTH: 229pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=305><COLGROUP><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #4f6228; WIDTH: 118pt; HEIGHT: 18pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=24 width=157>Type</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #4f6228; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=84>Totals</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #4f6228; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=64>Percentage</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>Line 8 Label</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>7235000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0.563053</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>Line 10 Label</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>4095000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0.318687</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>Line 11 Label</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1519600</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0.11826</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #4f6228; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>TOTAL</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #4f6228; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>12,849,600</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #4f6228; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>100.00%</TD></TR></TBODY></TABLE>

 

[RichCowell]

You need to add a formula to your category names on your spreadsheet, similiar to the example I have posted.

Pie Chart

	A	B

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 76px"><col style="WIDTH: 44px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Month[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Data[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]January[/TD]
[TD="align: right"]908[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]February[/TD]
[TD="align: right"]290[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]March[/TD]
[TD="align: right"]160[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]April[/TD]
[TD="align: right"]289[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]June[/TD]
[TD="align: right"]949[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]July[/TD]
[TD="align: right"]242[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]August[/TD]
[TD="align: right"]802[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]October[/TD]
[TD="align: right"]266[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]November[/TD]
[TD="align: right"]705[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]December[/TD]
[TD="align: right"]239[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 =IF(B2>0,"January","") A3 =IF(B3>0,"February","") A4 =IF(B4>0,"March","") A5 =IF(B5>0,"April","") A6 =IF(B6>0,"May","") A7 =IF(B7>0,"June","") A8 =IF(B8>0,"July","") A9 =IF(B9>0,"August","") A10 =IF(B10>0,"September","") A11 =IF(B11>0,"October","") A12 =IF(B12>0,"November","") A13 =IF(B13>0,"December","") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4




Jason

I've just tried this - and it does hide the word from the table, and from the legend, but it doesn't remove the colour next to where the series name was in the legend... is there any way of hiding that too? Or making it not look at that cell at all?