Excel 2010: How to format ALL data point labels SIMULTANEOUSLY

[brianclong]

Hello. Frustrated with Microsoft here as usual.

I would like to do something REALLY SIMPLE (at least it should be). I want to change the format (I.E. enlarge, etc.) my data point labels all at the same time. But when I select "More Data Label Options" under the layout ribbon's Data Label menu, Excel automatically selects my first data point label (even if I have the whole graph selected).

Is there any way to format all data labels simultaneously in Excel 2010?

 

[gurs]

Like the OP, I was frustrated at having to change the data label font size for each series on multiple graphs, so I wrote some code to do it. It has some built-in error checking (be sure a chart is selected, validate font size entry, etc.) and the option to revert to the previous size. Of course, writing the code took about 3 times longer than just changing all the graphs! Hopefully someone else can get some value out of it and make my effort worthwhile.

Sub ChangeDataLabelFontSize()
'Will change the font size for all data labels on the active chart

Dim x As Integer
Dim SizeNew As Variant
Dim myChart As Chart
Dim Name As String
Dim SizeOrig As Integer
Dim Response As Variant
Dim ser As Series

'define chart
    Set myChart = ActiveChart

'confirm that a chart has been selected
    If myChart Is Nothing Then
        Response = MsgBox("You must select a chart before using this macro.", vbOKOnly, "No Chart Selected")
        Exit Sub
    End If
    
'define chart name
    Name = myChart.Name
    SizeOrig = myChart.SeriesCollection(1).DataLabels.Font.Size
        
'request font size
    SizeNew = InputBox("This macro will change the font size for all " _
        & "data labels on the active chart.  The current chart is:" _
        & vbNewLine & vbNewLine & Name & vbNewLine & vbNewLine _
        & "To continue, enter the font size below. " _
        & "To leave " & Name & " unchanged, click Cancel.", "Enter Font Size")
    
'confirm if user clicked cancel or didn't enter anything and clicked ok
    If SizeNew = "" Then
        MsgBox ("Exiting macro, no changes made.")
        Exit Sub
    End If

'confirm that user entered a number
    If Not IsNumeric(SizeNew) Then
        MsgBox ("Please enter only numeric values.  No changes made.")
        Exit Sub
    End If

'confirm that font size is in range
    If SizeNew < 6 Or SizeNew > 18 Then
        Response = MsgBox("You have entered a font size of " & SizeNew _
            & ".  Are you sure?  Click Ok to continue, or Cancel to leave " _
            & "your chart unchanged.", vbOKCancel, "Font Size Looks Odd")
        If Response = vbCancel Then
            MsgBox ("Exiting macro, no changes made.")
            Exit Sub
        End If
    End If

'change data label size for each series that has data labels
    For x = 1 To myChart.SeriesCollection.Count
        If myChart.SeriesCollection(x).HasDataLabels Then
            myChart.SeriesCollection(x).DataLabels.Font.Size = SizeNew
        End If
    Next x
    
'redraw chart on screen (not sure why this isn't automatic, but it isn't)
    ActiveSheet.Calculate
    ActiveSheet.Range("CA1").ClearContents 'no idea why this works, but it does
    myChart.Refresh
    
'give option to revert to original size if one exists
    If Not IsNumeric(SizeOrig) Then Exit Sub 'confirm that we stored a starting font size
    
    Response = MsgBox("I have changed the data label font size to " & _
        SizeNew & ".  To accept this change, click OK.  " & _
        "To revert to your original size of " & SizeOrig & _
        ", click Cancel.", vbOKCancel, "Confirm Change")
        
    If Response = vbCancel Then
        For x = 1 To myChart.SeriesCollection.Count
            If myChart.SeriesCollection(x).HasDataLabels Then
                myChart.SeriesCollection(x).DataLabels.Font.Size = SizeOrig
            End If
        Next x
        
        ActiveSheet.Calculate
        ActiveSheet.Range("CA1").ClearContents 'no idea why this works, but it does
        myChart.Refresh
        
        MsgBox ("I have changed the font size back to " & _
            SizeOrig & ".  Exiting macro.")
            
    End If

End Sub

 

[jcridge]

In an XY plot where I'd already put in custom text labels for all points, a non-VBA method that worked for me was:

a) select the points of interest i.e. a deft single left click might select all points depending on chart
b) right click to "Format Data Labels" menu
c) select "Number"
d) change "Category" to "Text"
e) the system presents the standard font size buttons in the main Excel tool bar
f) adjust font size at will

 

[joes2007]

I am having this issue with Pivot Charts. I have a bar chart with about 40 series. I want the Data labels Labels options to be Series name and Value, with a line separator. I can't seem to find a way to apply this to all series. When I make the change it only changes the first series and then I have to click on the next one. I dont want to do this for ever series, and every time a new series is added. Thoughts?

Also, is there a way to insert an image inline? Like, I took a capture from Snagit, and want to copy and paste it in my post... Am I just a dummy?

 

[jcridge]

The (multiple) tasks here do not relate to the original post

 

[SavageHenry]

Hello. Frustrated with Microsoft here as usual.

Is there any way to format all data labels simultaneously in Excel 2010?

Unfortunately, I haven't been able to figure out a way to modify all series labels at once but if you right click a data label and select Format Data Labels DON'T close the pop up window after you make your adjustments. Instead, left click a data label in another series while the pop up window is still open. You'll be able to make adjustments at a quicker pace because the Format Data Labels window will stay open.

 

[Fantrasp12]

If you want to format all data labels for more than one series, here is one example of a VBA solution:

Sub x()    
Dim objSeries As Series         
   With ActiveChart        
     For Each objSeries In .SeriesCollection            
        With objSeries.Format.Line                
                           .Transparency = 0                
                           .Weight = 0.75                
                           .ForeColor.RGB = 0            
        End With        
     Next    
  End With
End Sub

this didnt do anything at all, no effect...any reason it might behave this way?