Copying Formats, Part 3: Charts – Regular Charts

Have you ever had a situation where you needed to change the format of a bunch of charts? Turns out Excel makes that an easy task by allowing us to copy only a chart’s format and apply it to other charts. To demonstrate this, I’ve assembled temperature data for Philadelphia, Pennsylvania and Myrtle Beach, South Carolina… where I am, and where I’m considering moving to eventually. In order to stay focused on the topic of copying formats, I’ve already prepared regular charts and PivotCharts comparing average temperatures, average high temperatures, and average low temperatures, but I’ve used different chart formats. In this session we’re going to make all three regular charts look the same. Next week, we’ll focus on PivotCharts. To learn how to prepare regular charts and PivotCharts, please see Chapter 8, “Using Charts to Present Data” in the book “Simply Learning Excel 2007” available at Amazon.com.

Please click this link to open an Excel 2007 file (zipped, no macros) named “Temperature-Comparison-BEFORE.xlsx.” If not already selected, click the sheet named “Charts-Regular.” We’ll first copy the format of the chart at the top to the chart just underneath it.

  1. In the chart entitled “Average Temperatures,” let’s click somewhere in the white space within the chart. If you’re not sure what I mean, try clicking in the white space to the left of the words “Average Temperatures” as shown here:
    Copy Formats-Part 3-05c-click white area of chart
  2. In the Home tab, in the Clipboard group, click the Copy icon.
  3. In the chart entitled “Average High Temperatures,” click somewhere in the white space within the chart. If you’re not sure what I mean, try clicking in the white space to the right of the words “Average High Temperatures.”
  4. In the Home tab, in the Clipboard group, click the dropdown box under the Paste icon.
  5. In the menu that appears, click Paste Special.
  6. In the box that appears labeled Paste Special, click the button to the left of the word Formats, as shown here:
     Copy Formats-Part 3-10-Paste Special box
  7. Click OK.
  8. The formats of the top two charts in this sheet should now be identical.

Now, we’ll apply that format to the chart at the bottom of this sheet. Note that we don’t need to copy the format of the first chart again.

  1. In the chart entitled “Average Low Temperatures,” click somewhere in the white space within the chart.”
  2. In the Home tab, in the Clipboard group, click the dropdown box under the Paste icon.
  3. In the menu that appears, click Paste Special.
  4. In the box that appears labeled Paste Special, click the button to the left of the word Formats.
  5. Click OK.
  6. The formats of all three charts in this sheet should now be identical.

Please click this link to open an Excel 2007 file (zipped, no macros) named “Temperature-Comparison-AFTER.xlsx” and compare it to your results.

Next week, we’ll demonstrate copying chart formats between PivotCharts. Until then, I hope you all have an enjoyable week.

This entry was posted in Excel Copy Formats and tagged , , , . Bookmark the permalink.

4 Responses to Copying Formats, Part 3: Charts – Regular Charts

  1. The shortcut Ctrl + Alt + V is good for Keyboard freaks

    • Bob Ryan says:

      Deependra – Thanks for sharing that. For those who don’t know what that does (including me until I just tested it!), it’s another way to Paste Special.

  2. I seriously love your blog.. Great colors & theme.
    Did you build this amazing site yourself? Please reply back as
    I’m trying to create my own personal blog and want to learn where you got this from or exactly what the theme is named. Cheers!

    • Bob Ryan says:

      While I contributed the content and a basic idea of the colors and design I was looking for, a professional (Microsoft MVP) put this site together. I wish you the best with your personal blog.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>