Excel graphs: How do I apply a graph template format?

Question

I've created a format that I like. I now need to apply this format to over 900 graphs! I've seen we can save a format as a template for future use, however, I can't find the place where I have the option of applying such a template.

What is the process for applying a user made template in Excel 2007?

Answer 1

You can copy and paste chart formats. In 2003 and previous versions you copy the graph and then use Edit>Paste Special to paste only the formats. I've no idea of the equivalent ribbon control in 2007/2010 I'm afraid, but I'm reasonably sure it should be there (MS did remove some charting functionality in 2007).

However, with 900 charts even this may a bit of a pain. You could use a bit of VBA to achieve the same though.

Edit: Had a bit of a play about and this will copy the format of Chart1 to all other charts in the worksheet:

Sub ReplicateChartFormats()
    Dim c As ChartObject

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Copy

    For Each c In ActiveSheet.ChartObjects
        With c.Chart
            .Paste Type:=xlFormats
        End With
    Next c
End Sub

Again, be aware that this has only been tested in Excel 2003 and the object model in 2007 is missing a few things compared to earlier versions, but this is pretty fundamental stuff so should work.