Apply Chart Formatting to Other Charts

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.

paste special dialog for charts

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.

original four charts

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).

formats copied onto the charts

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.

formats copied onto the charts and titles restored

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.

Peltier Tech Chart Utility

Comments

  1. Ray –

    Yep, everyone has their own requirements, I guess.

    To do just the activesheet, replace this:

    For Each Sht In ActiveWorkbook.Worksheets

    with this:

    Set Sht = ActiveSheet

    and remove this:

    Next Sht

    Other options need similar changes. I imagine choosing both an active chart as the master chart and choosing only some charts to reformat would be a challenge, requiring some kind of dialog system.

  2. Hi Jon –

    Great tip, VERY useful … but what if you don’t want to change *every* chart in the *whole* workbook? Maybe just the selected charts or just the ones the active-sheet? A small pop-up box to solicit user-preferences perhaps?

    Thanks alot!

    //ray

  3. “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.”

    :)

    I wonder if a response or followup from the “OP” is forthcoming.

  4. Nice one Jon. You should make this part of the dashboarding bootcamp! VBA day.

  5. Nice piece of code – as you say, this could be a jumping-off point for people with their own specific requirements.
    For example, you could change the name of one chart to “Master” or somesuch, then find this one to copy formats from, and iterate through all the selected charts to apply the formats to.
    This does seem to be one of the places where F4 (or CTRL-Y) seems to work just fine, as the last action is the paste special, so you are not plagued with the issues which beset the non-modal formatting dialog boxes.

  6. Very nice Jon. I tend to use worksheet cells for titles and totally missed that one.

    I didn’t think I wrote the procedure very well to begin with and am glad you were able to improve it.

    Thanks again.

  7. Great code, but in xl2007 sp2 it copies the format AND the data of the charts. Can’t figure out why yet.

  8. Patricio –

    This happens because Excel 2007 SP2 apparently has a bug. It doesn’t properly recognize xlFormats or xlPasteFormats. This bug is also present in Excel 2007 SP1 and presumably all Excel 2007 versions.

  9. Bruce Glanville says:

    Trying to use this in Excel 2010 but I keep getting a run time error 1004 on the .Paste Type = xlFormats statement.
    I created a ~myuserpath\xlstart\personal.xlsm file and copied the VBA into a macro in that file, saved then hid the window per MS article here:

    http://office.microsoft.com/en-us/excel-help/deploy-your-excel-macros-from-a-central-file-HA001087296.aspx?redir=0

    In the workbook with the charts I want to format, I select the chart I want to copy the format from (a two data point pie chart) and run the macro. Then I get the run time error. I am a complete noob to VBA so having a tough time figuring this out. I looked up the chart.paste method and saw that valid paste types are xlPasteXxxxx so I changed xlFormats to xlPasteFormats but that was apparently not the issue.

    Any comments or suggestions welcome

  10. What is the error message? The error number doesn’t help much, since error number 1004 is applied to half the run time errors you’ll encounter.

    In Excel 2010 I don’t get an error, but I still get the data pasted as well as the formats. And the data becomes new series in front of the existing series in the target chart. So we have to transfer the data from the pre-existing series to the inserted, then remove the extraneous series. This does it for uncomplicated data series (tested minimally), though if the target chart has more series than the original chart, the series strangely end up in the wrong order.

    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
      
      Dim iSource As Long
      Dim iTarget As Long
      Dim iTotal As Long
      Dim iSeries As Long
      Dim vSource As Variant
      Dim vTarget As Variant
    
      Application.ScreenUpdating = False
    
      Set chtMaster = ActiveChart
      iSource = chtMaster.SeriesCollection.Count
    
      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
              ' count series
              iTarget = .SeriesCollection.Count
              ' 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 data (2007 and 2010 bug:
              ' paste-special-formats treated as paste-special-all)
              iTotal = .SeriesCollection.Count
              If iTotal = iSource + iTarget Then
                For iSeries = 1 To iTarget
                  vSource = Split(.SeriesCollection(iSeries).Formula, ",")
                  vTarget = Split(.SeriesCollection(iSeries + iSource).Formula, ",")
                  vTarget(UBound(vTarget)) = vSource(UBound(vSource))
                  .SeriesCollection(iSeries).Formula = Join(vTarget, ",")
                Next
                For iSeries = iTotal To iTarget + 1 Step -1
                  .SeriesCollection(iSeries).Delete
                Next
              End If
              
              ' 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
  11. A lot of scientist uses a graph type available in software like Graph Pad Prism but not in Excel as far as i know. The graph type depicts Y-values grouped in categories spread on the x-axis. Contrary to bar diagrams of the average of the Y-values (possible with error-bars), it will however depict each Y-value of the dataset. To avoid that points are placed on top of each other the points are automatically scattered a long the vertical axis when multiple points are present around the same y value. An example is figure 2E in the following manuscript http://www.plosone.org/article/info%3Adoi%2F10.1371%2Fjournal.pone.0042403.

    I have thought of depicting data as an x-y scatter plot, representing each category as an x-value (1, 2, 3 etc.). Then changing the x-value with a delta-value (plus/minus), when multiple Y-values are similar. However, even doing this would not render the data categorical, and one would not be able to easily change the order of the categories. And more importantly it could be perfect to overlay this type of figures with a categorical plot showing the median and the standard deviation. However, overlaying two plots where one has a numerical x-axis and the other a categorical is not feasible I believe.

    I would be very interested in any ideas that could solve this problem.

    Kind regards

    Martin

  12. Martin –

    This can certainly be done. Look for it in an upcoming blog post.

  13. Many thanks, I have found this very useful. Is it possible to modify this code so that it works on charts that are on separate worksheets (i.e. not embedded)?

  14. Sorry for not responding earlier. I ended up simply copying and pasting to get what I needed in the short term.

    The error message was Method ‘Paste’ of object ‘_Chart’ failed

    Still getting the error. I also find the execution of the macro is slow. It runs for several seconds before I get the error.

  15. I couldn’t get this working either in 2010. I get the same issue with xlFormats not being valid in 2010. I gave up on Google after a while and instead of Copy and Paste I created a VBA which applies a Chart Template to all Charts in a Workbook. Works flawlessly.

  16. Anybody got ideas how to copy just certain series formats between two or within one chart.
    ie. Not just Color Index like this, but the whole Fomatting: (markes, line style, fore/back color, etc)
    .SeriesCollection(i).Interior.ColorIndex = MasterSeries.Interior.ColorIndex

    tx

  17. Hi Chris –
    There isn’t any built-in way to copy and paste all formats of a chart element. I built a small function as part of my latest Chart Utility that copies a series formats and pastes it onto another series, but subject to inconsistencies and omissions in the Excel object model, it often doesn’t render the pasted formats very closely to the copied formats. I’ve thought about doing the same for axes.

  18. Messed up my entire workbook…

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.