Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How to format multiple charts quickly

format multiple charts quickly

Today I would like to share with you the best tip I learned last year.  Why has this had never occurred to me previously? I have no idea.  But I can honestly say that this has already saved hours of my life.  Given the long hours we are expected to work these days, I know that these are hours I spent at home with my family, hence why I’m so excited to share this with you.

Let me start by explaining the problem, and then the solution.

The problem of formatting lots of charts

If you have to create presentations or reports as part of your job you will no doubt be creating a lot of charts.  Depending on the size of the presentation or report there could easily be over 20 charts.  To ensure the presentation looks professional you have made sure all of the charts look the same.

Whether it is in a meeting, or via e-mail you received the terrible news that somebody wants to change the presentation.  Or more specifically, they want to change the format of the charts.  Not just one change, but a lot of changes.  If you would have known at the start you would have created them that way, it would have been clean and simple.  But now you’re having to go back through and consistently make all the formatting changes to all the charts.  Firstly, you breathe in and out to calm your nerves, then you make a start on formatting all the charts.  Secretly in your mind you’re saying the words “I could be doing something so much more important than this”.

The solution to formatting lots of charts

If like me, you didn’t know this trick you would still be sat at your desk complaining to yourself.

Here is the trick: you can copy a chart’s formats and paste that format onto another chart.  You only need to format one chart correctly, then copy and paste that format to all the other charts.  Now a 30-minute task has just been reduced to a 2-minute task.

How to format multiple charts quickly

Here is the chart format we wish to copy:

how to format multiple charts quickly

We can click anywhere on the chart.  Then click Home -> Copy (or Ctrl + C)

Excel Ribbon Home Copy

Now click on the chart you want to format.  Then click Home -> Paste Special.

Excel Ribbon Home Paste Special

From the Paste Special window select “Formats”, then click OK.

Excel Charts Paste Special Formats

Ta-dah!  With just a few click you can quickly change the format of a chart.

how to format multiple charts quickly

Just carry out this simple copy and paste on all the charts in your workbook.


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

9 thoughts on “How to format multiple charts quickly

  1. Hans says:

    Yeah, but when you change the data source on the 2nd chart, the formatting is gone. How do you maintain the format when you change the data source?

    • Excel Off The Grid says:

      I don’t think you can (though I’d be happy to be proved wrong). Change the data source first, then apply the formats.

      Maybe I’ll look into a macro solution at some point.

  2. Souvick says:

    This Works fine for my 150 graphs. But I had to do it my selecting each graph individually and then paste as format. Is there any trick to change all 150 graphs at the same time.
    Thank you so much in advance.

    • Excel Off The Grid says:

      There is no trick, just use a macro to loop through all the charts and apply the format to each item.

      Something like the following would do it in a few seconds. Get the name of the chart to copy from the Name Box, then enter that into the input box when running the macro.

      Sub CopyFormatToAllCharts()
      
      Dim chtObj As ChartObject
      Dim chtName As String
      
      chtName = Application.InputBox(Prompt:="What is the name of the chart to copy?", Title:="Chart Name")
      
      ActiveSheet.ChartObjects("Chart 2").Chart.ChartArea.Copy
      
      For Each chtObj In ActiveSheet.ChartObjects
      
      chtObj.Activate
      ActiveSheet.PasteSpecial Format:=2
      
      Next chtObj
      
      End Sub
      

Leave a Reply

Your email address will not be published. Required fields are marked *