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?

 

[MaryN]

I know I'm way late to this conversation, but I had the same question and finally figured out how to do it! Go to the Format tab under Chart Tools. On the far left side of the ribbon bar, there's a drop-down box that by default has "Chart Area" selected. Pull down to "Series xyz Data Labels." Then you can change the formatting of all the data labels in the chart.

 

[mmorri21]

I am also very late, but you may have to use the macro. Try this code:

For x = 1 to ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(x).Font.Size=12
Next x

 

[Thrice1738]

The code below will create data labels and change them to the series name in Excel 2010, just click on the chart you want and run the code. Remember click outside the plot area but inside the chart. Hope this helps.

[I]Sub DataLabelsToSeriesNames()</SPAN></SPAN>[/I]
[I]Dim objSeries As Series</SPAN></SPAN>[/I]
 
[I]   With ActiveChart</SPAN></SPAN>[/I]
[I]     For Each objSeries In .SeriesCollection</SPAN></SPAN>[/I]
[I]        With objSeries.ApplyDataLabels(xlDataLabelsShowLabel, False, True, False, True, False, False, False, False, False)</SPAN></SPAN>[/I]
       
[I]        End With</SPAN></SPAN>[/I]
[I]     Next</SPAN></SPAN>[/I]
[I]  End With</SPAN></SPAN>[/I]
[I]End Sub</SPAN></SPAN>[/I]

 

[Hippy]

I have found the format painter tool does the trick as well; double click the format painter to get the sticky menu and left click on the series labels that you want to change.

 

[Benjamin Swift]

You've probably gotten past this now, by just in case: I find highlighting the whole chart (i.e. by left-clicking on the chart background) and then specifying the font size, etc. for the whole chart is easiest. This obviously also changes any titles, axes labels, etc., but editing them may be quicker than formatting data labels individually by series.

 

[sophie619]

You've probably gotten past this now, by just in case: I find highlighting the whole chart (i.e. by left-clicking on the chart background) and then specifying the font size, etc. for the whole chart is easiest. This obviously also changes any titles, axes labels, etc., but editing them may be quicker than formatting data labels individually by series.

This was the fastest way I've found (I have a long briefing with a ton of multiple-series charts). Thank you!

PS - although a trip to Redmond might be in order.... I mean, come on, it can't be that hard to include some of these things.

 

[cardguys3]

This just seems like exceptional negligent programming. I face the same issue and I could write a VBA script that would perform the tasks I want but why can't one simply adjust all data label concurrently. C'mon Billy Gates... get it together.

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

 

[rpilgrim]

You have AXIS(Categories) and LEGENDS(series) the reason you can not change all the labels at once is because you got your data in the wrong place. Your categories are in the series. Simply move them to AXIS(Catagories), problem solved.... i just did it and can edit all my labels just fine.

 

[cardguys3]

You have AXIS(Categories) and LEGENDS(series) the reason you can not change all the labels at once is because you got your data in the wrong place. Your categories are in the series. Simply move them to AXIS(Catagories), problem solved.... i just did it and can edit all my labels just fine.

I believe I understand you, but moving data affects the layout, their associated graphs, and so on. If data presents on a graph axially then the data labels should be able to be adjusted concurrently. Don't you think?

 

[rpilgrim]

I believe I understand you, but moving data affects the layout, their associated graphs, and so on. If data presents on a graph axially then the data labels should be able to be adjusted concurrently. Don't you think?

Let me clarify, when you create a chart (either by selecting the data or a pivot table) you have the choice of deciding what data goes where (AXIS or LEGEND), when i said move data around i should have said make sure you determine what data is assigned to what part of the chart. If you at using a pivot table then you can "move data around" by dragging and dropping the variable name into the correct part of the chart. I was manually having to label things just like others in this thread. Just try changing what data is applied to what part of the graphic and that should fix this issue, no need to alter the layout of the data in the actual sheet.