Hiding 0 value data labels in chart

  • Thread starter Thread starter septimus
  • Start date Start date
S

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.
 
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.
Click to expand...

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
 
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,

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)?
Click to expand...

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
 
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!!!
 
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
 
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
Click to expand...
**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
 
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
 
Last edited:
Back
Top