Comment from DMurphy
Time: Tuesday, May 12, 2009, 7:36 am

It never ceases to amaze me how Excel can be twisted to do the (near) impossible! I have always said that programming (and I include spreadsheet manipulation in this definition) is more of an art than a science.
That was well worth a coffee! Enjoy with my thanks as this solves yet another of my users’ complaints!

Comment from ron
Time: Friday, January 8, 2010, 2:43 pm

in a similar vein, i’m using Excel data in a Powerpoint BAR chart (not stacked). however if the value of a series is ZERO i would like bar chart to ignore it. no label, no bar, no empty space where bar would be. can i do that?

Comment from Paul Makulski
Time: Tuesday, May 4, 2010, 3:22 pm

I’ve been doing the same battle with a donut chart. The inner ring includes all of the outer ring labels stacked on top of each other at the zero position, and the outer ring has all of the inner ring labels stacked up. Really gross.
Your solution would work for this, except I’d need a custom number format for each cell (and there are going to be a lot of them).
There’s no way to set these formats using a formula, ist there? Or a conditional format (which for some reason doesn’t include number formatting.)
I also tried diddling the series formula, but it seems the “x” range is the same for all series, even when you specificly change them in the series formula.
I’m boxed.

Comment from Paul Makulski
Time: Wednesday, May 5, 2010, 9:00 am

Hi Jon,
I have never used a donut before, but it was exactly what the user wanted.
To apply your number format solution, you need one custom format for each value cell to make it look like a label. There will be about 80 data points.
I have written a macro to do the custom formats for me. It works, but it really says “kludge”. (email me if you’d like to see what I’m doing.)
It would certainly be easier if Excel just provided for an option for a label range for each series. Do you know if that is a feature in Excel 7? (I’m using 2003)

Comment from Rishu
Time: Friday, May 7, 2010, 3:25 am

Hi,

I am showing both the label and the value in my chart. For example, in my chart it would appear (A,3), (B,0) & (C,3) for Beta.

However, I want only (A,3) & (C,3) to appear.

Is it possible to do that? Basically for ZERO value, I neither want the label nor the value to appear.

Please help

Rishu

Comment from Rishu
Time: Sunday, May 9, 2010, 7:52 pm

Hi Jon,

Yea currently I am doing that but because I have a lot of series and for a particular data points, only a few have values. This causes all the labels with zero values to overlap each other and hence the figure looks clumsy.

Is there any way to get rid of the label also as in I dont want the “B” to appear?

Thanks
Rishu

Comment from Amber Criner
Time: Thursday, July 8, 2010, 11:21 am

I put in the custom number format as #;;; and it worked like a charm to get rid of the zeros.

Comment from Leo Franklin
Time: Monday, August 16, 2010, 6:43 am

hi its not complicated ,

just go to .. data labels in charts ….select format data labels … in that select the option numbers … select custom .. give the format as “#,###;-#,###” then click add .. all the zeros will be ignored in the barchart……..It Works ….