 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2005Zero Values in a Pie Chart
Hi I currently have some data as shown below: Item 1 0 Item 2 3 Item 3 6 Item 4 5 Item 5 0 Item 6 9 How can I exclude the zero items automatically, the data shown below is going to change regulary and i do not want to have to keep change the source data manually. D You can use AutoFilter to hide the rows with 0s as Chart does not display data that's hidden. HTH > Hi > [quoted text clipped - 12 lines] > > D Andy Pope shows how to do this via te use of dynamic range names here . . . http://www.andypope.info/charts/piezeros.htm ---- Regards, John Mansfield http://www.pdbook.com > Hi > [quoted text clipped - 12 lines] > > D In a pie chart no slice is shown for a zero value, so unlike other chart types you don't really need to cater for it. Or, do you mean not display Legend Entries with zero values. That could be done with a macro, though it would need to be rerun each time values change. Regards, Peter T > I currently have some data as shown below: > [quoted text clipped - 10 lines] > > D To add to Peter's response, you can make a separate column for the pie labels (presuming you're using labels, not the legend, for the slices) that yields an empty string for those values that are zero. This way, you get no labels for zero-value items: Label Chart label Value Tom =IF(C2<>0, A2, "") 1 Sally =IF(C3<>0, A3, "") 0 etc. Use the Chart Label column for the chart. You can hide that column.  SignatureEarl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- > In a pie chart no slice is shown for a zero value, so unlike other chart > types you don't really need to cater for it. Or, do you mean not display [quoted text clipped - 20 lines] >> >> D Alternatively, you can use a custom number format that excludes zero displays, like one of these: 0;;; 0%;;; - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ > To add to Peter's response, you can make a separate column for the pie > labels (presuming you're using labels, not the legend, for the slices) that [quoted text clipped - 7 lines] > > Use the Chart Label column for the chart. You can hide that column. Jon, I don't think I follow. The idea is to make the label disappear when the value is zero. The label and the value are in separate cells. SignatureEarl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- > Alternatively, you can use a custom number format that excludes zero > displays, like one of these: [quoted text clipped - 21 lines] >> >> Use the Chart Label column for the chart. You can hide that column. Earl, Jon's neat idea will display no value in the label, and the label "apparently" disappear providing labels are formatted with Fill and Border "None". In Format labels..., try custom number format like this: General;General; "General" can be changed as required but nothing after the second ";", the zero value format. This also assume that Labels are set to "show value" rather than "show label" in Chart Options > Data Labels. We never did learn what the OP didn't want to show, so we don't know if his Labels show values or labels, if indeed his problem concerns labels ! Regards, Peter T PS your idea is very clever ! > Jon, > [quoted text clipped - 31 lines] > >> > >> Use the Chart Label column for the chart. You can hide that column. Peter, Yeah. I presumed the OP wanted category names for labels, not values. I think Jon's solution was for showing values. SignatureEarl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- > Earl, > [quoted text clipped - 56 lines] >> >> >> >> Use the Chart Label column for the chart. You can hide that column. Earl - That's right, I was thinking of values, and I replied to your post without noticing that the formulas pointed to the labels. - Jon > Peter, > > Yeah. I presumed the OP wanted category names for labels, not values. I > think Jon's solution was for showing values. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.