Hide Series Data Label if Value is Zero

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 ….

Comment from Juan Carlos
Time: Monday, November 8, 2010, 8:24 pm

Hi Jon, I have learned a lot of things from your page, since I was school, and never had a question about the procedures you do, but a few days ago, in my work (civil engineering) I did a worksheet to calculate the bearing capacity of the soil, and use the tip you gave about using check boxes for graphing, every works great, but a (I suposse) simple question arrive to me, because I have could not solve.

If I have several check boxes and I try to plot some of them, not all, always the data labels appear and I dont know how to blank them, and make apear just when they are used.

Is there a way to do it?
Don´t know how to attach the file, but i suppose this is a simple and mortal question for you, thanks a lot for your knowledge. Sorry for my English.

Comment from Colin Bowern
Time: Wednesday, August 3, 2011, 2:12 pm

This proved to be super useful on an area chart to stop the drop off to zero.

=VALUE(TEXT(formula, “0;-0;-9999999;”))

Then set the vertical axis minimum to 0.

Comment from Sam
Time: Friday, September 30, 2011, 9:07 am

Fantastic thanks ever so much for this REALLY helpful!!!

Cheets

Comment from Maria
Time: Thursday, November 24, 2011, 3:50 pm

Wow you r a geniuos, thank u , this is all new for me

Comment from Jessie
Time: Thursday, March 8, 2012, 11:16 pm

Hi, is there a way of doing this dynamically? I.e. instead of hard coding in the series name, link to the cell of the series name and only show this when non-zero? (it’s important that this series name is dynamic, as data inputs/data refreshes might not be in the same order each time). We are looking at a chart with over 50 series, and many data points. Many of these series will be zero, but will be updated and changed on a weekly basis, so the least manual input (weekly) the better. Also, is there a way of having a similar dynamic legend, which only shows the series that are non-zero. Unfortunately, I am unable to install external applications to my machine…. Thanks for your help.