Sometimes you have a whole workbook full of charts. You made some last week, and others yesterday, but the one you made today has just the right look to it. You’d like to apply the formatting to the rest of the charts, but the thought of reformatting all of those charts makes your brain ache.

The good news is that you don’t need to reformat every little element of every last chart. When you copy a chart, you can select another chart and go to Paste Special. The options in the Paste Special dialog are to paste the formulas (the data in the copied chart), the formats (from the copied chart), or both data and formatting.

Not only can you paste formats from one chart to another, you can write a little VBA to automate the process.

John Mansfield did just that in VBA to Copy Embedded Chart Formatting in his cellMatrix.net blog. I punched up John’s original code to get this procedure:

Sub Copy_Chart_Formats()

    Dim Sht As Worksheet
    Dim Cht As ChartObject

    Application.ScreenUpdating = False

    ActiveChart.ChartArea.Copy

    For Each Sht In ActiveWorkbook.Worksheets
        For Each Cht In Sht.ChartObjects
            Cht.Chart.Paste Type:=xlFormats
        Next Cht
    Next Sht

    Application.ScreenUpdating = True

End Sub
 

Let’s see how it works. Here are four charts, with different data and with different original chart types and formatting.

With the top left chart selected, running the program changes none of the data but all of the formatting of the charts (including the top left chart).

Closer inspection reveals the bad news, which is that not only are the formats changed to match the master chart, but so are the chart and axis titles. This would be horrible, to get the formats you wanted in all 200 charts in your workbook, but to also have 200 sets of titles to change back.

Fortunately we’re all expert programmers, with a thorough knowledge of Excel’s object model, so we recognize before even trashing all of our charts what steps we need to take.

First we’ll do a little check before reformatting a chart, to save us the fraction of a second it takes to redundantly reformat the original chart. Then we’ll extract the chart and axis titles, apply the master format to the chart (which hoses the titles), and finally reapply the titles.

Sub Copy_Chart_Formats_Not_Titles()

  Dim Sht As Worksheet
  Dim Cht As ChartObject
  Dim chtMaster As Chart
  Dim bTitle As Boolean
  Dim bXTitle As Boolean
  Dim bYTitle As Boolean
  Dim sTitle As String
  Dim sXTitle As String
  Dim sYTitle As String

  Application.ScreenUpdating = False

  Set chtMaster = ActiveChart

  For Each Sht In ActiveWorkbook.Worksheets
    For Each Cht In Sht.ChartObjects
      If Sht.Name = chtMaster.Parent.Parent.Name And _
          Cht.Name = chtMaster.Parent.Name Then
        ' don't waste time on chtMaster
      Else
        With Cht.Chart
          ' get titles
          bTitle = .HasTitle
          If bTitle Then
            ' chart title exists
            sTitle = .ChartTitle.Characters.Text
          End If
          If .HasAxis(xlCategory) Then
            bXTitle = .Axes(xlCategory).HasTitle
            If bXTitle Then
              ' axis title exists
              sXTitle = .Axes(xlCategory).AxisTitle.Characters.Text
            End If
          End If
          If .HasAxis(xlValue) Then
            bYTitle = .Axes(xlValue).HasTitle
            If bYTitle Then
              ' axis title exists
              sYTitle = .Axes(xlValue).AxisTitle.Characters.Text
            End If
          End If

          ' apply formats
          chtMaster.ChartArea.Copy
          .Paste Type:=xlFormats

          ' restore titles
          If bTitle Then
            .HasTitle = True
            .ChartTitle.Characters.Text = sTitle
          End If
          If bXTitle Then
            .Axes(xlCategory).HasTitle = True
            .Axes(xlCategory).AxisTitle.Characters.Text = sXTitle
          End If
          If bYTitle Then
            .Axes(xlValue).HasTitle = True
            .Axes(xlValue).AxisTitle.Characters.Text = sYTitle
          End If
        End With
      End If
    Next Cht
  Next Sht

  Application.ScreenUpdating = True

End Sub

As before, the master chart is selected, then its formatting is applied to all of the other charts in the workbook.

Nice, we’ve changed all of the formats while retaining the axis and chart titles. Instead of three minutes to reformat each chart (longer in 2007 because the F4 shortcut key for Repeat Last Action is no longer an effective timesaver), the whole process took seconds. Maybe a minute to copy and paste this code.

Related Posts:

• Quick VBA Routine: XY Chart with Axis Titles
• Creating Charts in a Grid
• Build an Excel Add-In 3 – Auxiliary Modular Functions
• Chart Event to Highlight a Series
• Hide Series Data Label if Value is Zero
• 9 Steps to Simpler Chart Formatting
• Legend Entry Tricks in Excel Charts
• Link Chart Text to a Cell
• Get Open or Save-As Filename
• A Retrospective on Charting