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.