Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding 0 value data labels in chart
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding 0 value data labels in chart
On Apr 15, 11:41*am, septimus wrote:
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 macrovanishzerolabelsrun. Sub VanishZeroLabels() For x = 1 To ActiveChart.SeriesCollection(1).Points.Count If ActiveChart.SeriesCollection(1).Points(x).DataLabe l.Text = "0.0" Then ActiveChart.SeriesCollection(1).Points(x).DataLabe l.Delete End If Next x End Sub Hope this helps, ABS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding 0 value data labels in chart
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).Dat aLabel.Text = "0.0" Then c.SeriesCollection(intSeries).Points(intPoint).Dat aLabel.Font.ColorIndex = 2 Else c.SeriesCollection(intSeries).Points(intPoint).Dat aLabel.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)? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding 0 value data labels in chart
On Apr 15, 8:45*pm, septimus wrote:
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).Dat aLabel.Text = "0.0" Then c.SeriesCollection(intSeries).Points(intPoint).Dat aLabel.Font.ColorIndex = 2 * * * * * * Else c.SeriesCollection(intSeries).Points(intPoint).Dat aLabel.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).HasDataL abel = True If ActiveChart.SeriesCollection(1).Points(x).DataLabe l.Text = "0" Then ActiveChart.SeriesCollection(1).Points(x).DataLabe l.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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding 0 value data labels in chart
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!!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding 0 value data labels in chart
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 ------- Peltier Technical Services, Inc. http://peltiertech.com _______ On Apr 15, 1:41*pm, septimus wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding 0 value data labels in chart
Now you tell me. :)
Jon FTW! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding 0 value data labels in chart
On Apr 16, 2:50*pm, Jon Peltier wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pie Chart Data Labels | Charts and Charting in Excel | |||
Pie Chart Data Labels | Charts and Charting in Excel | |||
Data labels in Chart | New Users to Excel | |||
Pie Chart Data Labels | Charts and Charting in Excel | |||
Data Labels in a Chart | Excel Programming |