Hiding data labels with zero values

[nuneatonandy]

Hi

I have a stacked column chart with the data source being a pivot table (note the chart is not a pivot chart it s just linked to the data source). I want to show the data label for each stacked column where the value is >0. I only seem to have the option of show all data labels or hide all data lables. I can do them one by one but I have a lot and would be so much quicker if there was a way to do it auomatically.

Help!

THanks
Andy

 

[AlphaFrog]

Here's a macro that may do it for you. It displays only the data labels if the value is greater than 0

Sub Show_Labels_Greater_Than_Zero()

    Dim srs As Series, i As Long, cht As Chart
    
    On Error Resume Next
        Set cht = ActiveChart
    On Error GoTo 0
    
    If cht Is Nothing Then
        MsgBox "Please select a chart first.", vbInformation, "No Chart Selected"
        
    Else
        For Each srs In cht.SeriesCollection
            For i = 1 To UBound(srs.Values)
                srs.Points(i).HasDataLabel = srs.Values(i) > 0
            Next i
        Next srs
    End If
        
End Sub

 

[nuneatonandy]

Thanks AlphaFrog
Macro works well but the end product is that Data Values are shown instead of Data Labels. Can it be tweaked for Data Labels only?
nunA

 

[AlphaFrog]

I guess I don't understand the distinction between Data Values and Data Labels.

By Data Labels, are you referring to the x-axis category labels? If yes, what are those labels set to?

 

[nuneatonandy]

No the data labels are on the Y axis. Example of what I am trying to do is below:

Data set
<TABLE style="WIDTH: 293pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=391 x:str><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4736" width=111><COL style="WIDTH: 42pt" span=5 width=56><TBODY><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 11.25pt; BORDER-TOP: #032043 0.5pt solid; BORDER-RIGHT: #032043 0.5pt solid" class=xl22 height=15 width=111>project</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #032043 0.5pt solid; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 width=56>Une</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #032043 0.5pt solid; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 width=56>Deux</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #032043 0.5pt solid; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 width=56>Trois</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #032043 0.5pt solid; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 width=56>Quatre</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #032043 0.5pt solid; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 width=56>Cinq</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl22 height=15>Alpha</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>169</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>227</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>10</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>87</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl22 height=15>Beta</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>65</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>20</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24> </TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl22 height=15>Gamma</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>30</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>75</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>20</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl22 height=15>Delta</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>150</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24 x:num>90</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl24> </TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl22 height=15>Grand Total</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 x:num x:fmla="=SUM(B2:B5)">245</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 x:num x:fmla="=SUM(C2:C5)">244</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 x:num x:fmla="=SUM(D2:D5)">227</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 x:num x:fmla="=SUM(E2:E5)">120</TD><TD style="BORDER-BOTTOM: #032043 0.5pt solid; BORDER-LEFT: #032043; BACKGROUND-COLOR: transparent; BORDER-TOP: #032043; BORDER-RIGHT: #032043 0.5pt solid" class=xl23 x:num x:fmla="=SUM(F2:F5)">107</TD></TR></TBODY></TABLE>

If you do a simple stack column chart on this data set and then add Data Labels (under Chart Options) you can either have them on or off. I only want them showing for the cells (i.e. stacks) with >0 value.

 

[AlphaFrog]

I guess I'm an idiot 'cuz I still don''t get it. I make a stacked column chart using your example data (I put zero values in where it has blanks).

I run the macro on the chart and all the values (Chart Options\ Data Labels: Values) greater than zero are displayed on the chart.

 

[nuneatonandy]

It's not the 'data label values' I want displayed on the chart it's the y axis labels. I.e Alpha where alpha has a stack Beta where Beta has a stack etc. But not Delta if delta doesn't have a stack.
Sorry for not being very clear.
NUnA
Ps if it makes a difference I'm using excel 2003

 

[AlphaFrog]

This displays the Series Labels for each stack if the value is greater than 0.

Sub Series_Labels_Greater_Than_Zero()

    Dim srs As Series, i As Long, cht As Chart
    
    On Error Resume Next
        Set cht = ActiveChart
    On Error GoTo 0
    
    If cht Is Nothing Then
        MsgBox "Please select a chart first.", vbInformation, "No Chart Selected"
        
    Else
        cht.ApplyDataLabels AutoText:=True, LegendKey:=False, _
            HasLeaderLines:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
            ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
        
        For Each srs In cht.SeriesCollection
            For i = 1 To UBound(srs.Values)
                If srs.Values(i) <= 0 Then srs.Points(i).DataLabel.Delete
            Next i
        Next srs
    End If
        
End Sub

 

[nuneatonandy]

Great. Thanks Alpha Frog and thanks for being patient with me.
NunA

 

[Stepper]

nuneatonandy - did this work for you? It seems very complicated, but I don't work with Pivot Tables, so maybe this simple method isn't right for Pivot tables;

Right click on a data label on the chart (which should select all of them in the series), select Format Data Labels, Number, Custom, then enter 0;;; in the Format Code box and click on Add.

If your labels are percentages, enter 0%;;;
or whatever format you want, with ;;; after it.

With stacked column charts, you have to do this for each series, but you only have to do it once and the chart will remember your selections.