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?

 

[gehusi]

Instead of selecting the entire chart, right click one of the data points and select "Format Data Labels".

 

[brianclong]

This still only formats one set of data series labels. Maybe there's a bug? I have Office 64 bit.

 

[gehusi]

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

 

[brianclong]

Thank you. That's awesome. But if I need to resort to VBA to change the font size of all my data point labels I'm going to visit Redmond right now.

Do I really have to click on first data label -> change font size to 10, click on second data label -> change font size to 10, repeat ad nauseam???

 

[gehusi]

No, VBA isn't necessary within a single series (the "Format Data Labels" option should work just fine). For more than one series, buy your plane ticket to Redmond and let us know how it goes.

 

[brianclong]

My wording may have been wrong here. Just to clarify, I have a single bar chart. I want to enlarge the data point labels all at the same time as opposed to clicking on them one bar at a time. But I see no way of doing that because when I select them via drop down menu as you suggest above, Excel automatically selects only the labels on the first bar.

This is either a bug or someone at MS really needs to be fired.

 

[gehusi]

Try this: click somewhere in the white space of the plot area. Then right click one of the data labels and select "Format Data Labels". Report back.

 

[brianclong]

Still selects only the bar I'm clicking (it's a clustered bar - three brands on the x axis and three bars per brand (for example, dollars, units, volume)). As soon as I right click on one of the labels (for example, dollars), it only highlights the dollars labels. There's no option to for example, right click and select "all data point labels."

Now if I just select the whole graph and then change the font size, it changes everything (of course) title, axes labels, etc. I'd like to just increase the size of all data point labels on the graph.

Since I switched to Excel 2010, I've had to do this one series (for example, dollars or if brands were clustered, brand a) and then enlarge, brand b, then enlarge, brand c, and so on... TEDIOUS

 

[gehusi]

Gotta be one series at a time I'm afraid.