Thread: stacked chart labels

Originally Posted by Barry Au

is it possible to apply conditional formatting to chart labels? I have few labels that are 0 (zero), and I do not want them to show since it doesn't provide much value.

thanks.

Hi,

You could put a condition on the cell that contains the label, to be blank if the related value is blank, or see if the Chart Labeler at http://www.appspro.com/Utilities/Utilities.htm can help.

Let me know how you go.
---

Originally Posted by Barry Au

many users use #N/A to not allow zero value to impact their line graph

however in this case, it doesn't work. excel labels #N/A value as 0 on the chart.

Did your question mean to ask how to remove zero data points from the chart?

If so, this could be done by hiding the column or row that is zero and setting the chart to print visible cells only.

hth
---

Originally Posted by Barry Au

I'm trying to hide any data labels with the value 0.

It becomes distracting on a stacked bar chart to have "0" in between two bars...

again, are you trying to hide data points, or the labels that would attach to those points?

The attached shows the hiding of data points.

If you rightmouse on a data item and Format Data Series you can add one of 3 forms of Data Labels.

Does this help?
---

Originally Posted by Barry Au

hi Bryan,

sorry for the confusion. I'm trying to hide the data labels on the chart. if it requires me to hide to 0 on the spreadsheet, I don't mind, but preferably not.

i've turned on the data label for the chart you provided, and as you can see, the 0 doesn't really mean much (since you can visually see that it is zero)

aaha yes, I was confused.

You have put a zero into a cell, and asked Chart to display that zero as a label, and you do not want it displayed.

Perhaps the attached will help.

Failing this, perhaps the Chart Labeler might prove more useful.

---

Originally Posted by Barry Au

i'm trying to avoid using third party tools in case there are any problems. the spreadsheet im creating will not be maintained by myself in the future.

the latter chart you provided is exactly what I wanted, however, it seems like you just deleted any 0 that appear on the spreadsheet.

is there a way to do this using VBA or macro?

the numbers displayed were just typed in, you didn't want zeros so they were not typed in.

If you dont want zeros in your chart then don't type them in.

If your numbers are derived by formula then adjust the formula to be "" if it would otherwise be zero.

As you have not given any examples of your data it is obviously difficult to guess what you are doing.
---

Originally Posted by Barry Au

my apologies, my excel spreahsheet is too large and confusing to post

however the sample you created was an excellent example of what I'm trying to do.

my zeros are generated by a formula, so I can't perform any manual work to it.

I've replaced the 0 with "" on your sample spreadsheet, and the zero still appears..

i haven't yet found a value that can be represented as a 'blank' in excel chart labels...

I can't say that I agree, mine appears ok, but I do notice that you have, once again, failed to supply a copy of your test or a copy of the formula used.

---

Originally Posted by Barry Au

thanks Bryan,

your latest example is exactly what I wanted to do.

I appreciate your help.

Regards.

good to see, and a small sample of your data is always useful when a problem such as this is raised.
---