Hiding 0 value data labels in chart

[septimus]

I have an Excel column chart with data labels that show values which
are numbers between 1.0 and 5.0 (rounded to one decimal point). There
are a number of blank values in the data table used to produce the
chart, and they are being charted as "0.0". Apart from the fact that
this is ugly, it doesn't really even make sense with this data.

So how can I keep the desired data labels (1.0 - 5.0) and hide the
0.0s? It's been suggested to me that I could force #N/As and then
Excel wouldn't graph those values. But when I try that, I either end
up with stubborn 0.0s or, worse yet, data labels that say "#N/A".

Is there VBA solution here? A way to loop through the labels and hide
the values, maybe?

Thanks for your help.

 

[ABS]

I have an Excel column chart with data labels that show values which
are numbers between 1.0 and 5.0 (rounded to one decimal point). There
are a number of blank values in the data table used to produce the
chart, and they are being charted as "0.0". Apart from the fact that
this is ugly, it doesn't really even make sense with this data.

So how can I keep the desired data labels (1.0 - 5.0) and hide the
0.0s? It's been suggested to me that I could force #N/As and then
Excel wouldn't graph those values. But when I try that, I either end
up with stubborn 0.0s or, worse yet, data labels that say "#N/A".

Is there VBA solution here? A way to loop through the labels and hide
the values, maybe?

Thanks for your help.

sept,
Try pasting this code into a code module in your workbook, go back to
the worksheet, make sure you select the chart and take
macro>vanishzerolabels>run.

Sub VanishZeroLabels()
For x = 1 To ActiveChart.SeriesCollection(1).Points.Count
If
ActiveChart.SeriesCollection(1).Points(x).DataLabel.Text = "0.0" Then

ActiveChart.SeriesCollection(1).Points(x).DataLabel.Delete
End If
Next x
End Sub

Hope this helps,
ABS

 

[septimus]

ABS,

Hey, that's great!!

Only problem is, I need the data labels to re-appear when the values
change to something greater than 0. If I delete the labels, they're
gone when the chart repaints with new data.

I tried changing the Text property instead of deleting the label
(i.e., If 0.0 Then DataLabel.Text = ""), but same problem: when the
values become larger the wiped labels are still empty.

Then I tried changing the font to white...

If
c.SeriesCollection(intSeries).Points(intPoint).DataLabel.Text = "0.0"
Then

c.SeriesCollection(intSeries).Points(intPoint).DataLabel.Font.ColorIndex
= 2
Else

c.SeriesCollection(intSeries).Points(intPoint).DataLabel.Font.ColorIndex
= 0
End If

.... and that works pretty well except that you can still glimpse some
white zeros at the bottom where they overlap with chart columns.

Any better ideas? How do I get the DataLabels back when data changes
(without closing the file)?

 

[ABS]

ABS,

Hey, that's great!!

Only problem is, I need the data labels to re-appear when the values
change to something greater than 0. If I delete the labels, they're
gone when the chart repaints with new data.

I tried changing the Text property instead of deleting the label
(i.e., If 0.0 Then DataLabel.Text = ""), but same problem: when the
values become larger the wiped labels are still empty.

Then I tried changing the font to white...

            If
c.SeriesCollection(intSeries).Points(intPoint).DataLabel.Text = "0.0"
Then

c.SeriesCollection(intSeries).Points(intPoint).DataLabel.Font.ColorIndex
= 2
            Else

c.SeriesCollection(intSeries).Points(intPoint).DataLabel.Font.ColorIndex
= 0
            End If

... and that works pretty well except that you can still glimpse some
white zeros at the bottom where they overlap with chart columns.

Any better ideas? How do I get the DataLabels back when data changes
(without closing the file)?

Septie,
Yah...woke up this morning and went 'aaaaaugh, what if the data
changes?'.
Tried some experiments and came up with this, force each column to
have a label, then if it's blank, delete it, if it's not, do nothing.
So, one line of code in addition to yesterdays effort.
Sub VanishZeroLabels()
For x = 1 To ActiveChart.SeriesCollection(1).Points.Count
ActiveChart.SeriesCollection(1).Points(x).HasDataLabel = True
If ActiveChart.SeriesCollection(1).Points(x).DataLabel.Text =
"0" Then
ActiveChart.SeriesCollection(1).Points(x).DataLabel.Delete
End If
Next x
End Sub
So you have to remember to run it each time...also, not sure about the
'0' versus '0.0', I'm fuzzy on that.
Also wondering if running this code is more trouble than it would be
to just delete the offending labels by hand.
Bemused,
ABS

 

[septimus]

Eureka!

In my case anyway it's definitely easier to run the code than to
delete manually. My charts only change when one particular cell is
changed, so I just plugged your code into the Worksheet_Change event
for that worksheet.

Thanks!!!

 

[Jon Peltier]

No need for all of this code...

Use a custom number format like this:

0.0;;;

This format says to use "0.0 for positive numbers, and omit negative
numbers, zero values, and text when displaying the labels.

- Jon

 

[septimus]

Now you tell me.
Jon FTW!

 

[ABS]

No need for all of this code...

Use a custom number format like this:

0.0;;;

This format says to use "0.0 for positive numbers, and omit negative
numbers, zero values, and text when displaying the labels.

- Jon

**Snip**
No need? No need?
You mean there is a simple, direct, and effective way to do something
for which I've devised an over-complicated but clever work-around?
Well I never...really...where's the fun in that?
ABS

 

[connacht]

Hi there,

I've tried your suggestion (using XL 2007) and the annoying #n/a still remain using custom 0.0;;; - this is a clustered bar graph.

It is so infurating, something that only Excel can do to you

Any help much appreciated.

Connacht