Changing font style/size/color/etc for multiple series in a chart?

  • Thread starter Thread starter Hossdaddy
  • Start date Start date
H

Hossdaddy

I have a stacked bar chart (but note applies to all charts) that I have added
data labels to in Excel 2007. However, the data labels are the wrong font
size and color.

When I go to format data labels in the "Labels" section of the Chart
Tools/Layout ribbon, there is no apparent option to change font. I can right
click on a single series and change it, but doing that for every series in a
complex chart seems ludicrous.

Is there any way to change the font for multiple series in one step?
The same holds true for changing the number format or any other
characteristic for multiple series at once (the format data labels still only
applies to one series or label at a time - very painful).

Thanks for any help!
 
To reformat datalabels in all series in one go you'll need a macro, adapt
the following to your needs (revert back if not sure how)

Sub DataLabelFont()
Dim cht As Chart
Dim sr As Series
Dim dls As DataLabels

On Error Resume Next
Set cht = ActiveChart
If cht Is Nothing Then
MsgBox "No chart is selected"
Exit Sub
Else
If MsgBox("reformat datalabels..." & vbCr & "*no undo*" _
, vbOKCancel) <> vbOK Then
Exit Sub
End If
End If
On Error GoTo 0
For Each sr In cht.SeriesCollection
On Error Resume Next
Set dls = sr.DataLabels
On Error GoTo 0
If Not dls Is Nothing Then
dls.NumberFormat = "#,##0.000"
With dls.Font
.Bold = False
.Italic = False
.Size = 8
.Color = RGB(25, 25, 128)
End With
End If
Next

End Sub

Regards,
Peter T
 
Thanks, Peter, but I must confess I was hoping for a simpler solution that I
was just overlooking, as I am not a macro-master. I'll keep this in my back
pocket to try for a really complicated one, but for now, I may have to
maintain the brute force approach. Thanks for taking the time to reply. I
really appreciate it.
 
I have the same problem as Hossdaddy
is there any other/simpler solution that was suggested since then?
Vincent
 
Back
Top