Copying Formats, Part 4: Charts – PivotCharts

In last week’s blog, we demonstrated copying chart formats between regular charts. But, what about copying chart formats between PivotCharts? And, what about copying chart formats between regular charts and PivotCharts? These two questions are the subject of this week’s blog.

We’re going to use the same file we started with last week, but it is below this week’s blog and last week’s blog. The file contains temperature data from weather.com for Philadelphia, Pennsylvania and Myrtle Beach, South Carolinas and is charted using variously formatted regular charts and 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.

We’ll start by copying chart formats between PivotCharts. 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 “Pivot-Tables & Charts.”

  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 4-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 Paste icon.
  5. That’s it! The formats of the top two charts in this sheet should now be identical

Now, we’ll apply that same 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 the Paste icon.
  4. The formats of all three charts in this sheet should now be identical.

Copying formats between regular charts and PivotCharts is not as straightforward as perhaps it could be, so here are some guidelines:

  • To copy the format of a regular chart to another chart, use Copy and Paste Special > Formats.
  • To copy the format of a regular chart to a PivotChart, use Copy and Paste (not Paste Special).
  • To copy the format of a PivotChart to another PivotChart, use Copy and Paste.
  • To copy the format of a PivotChart to a regular chart… DON’T. Despite using Copy and Paste Special > Formats, Excel will copy the data series as well as the formats. Worse, using the Undo feature will only undo the format change, and change the data series to a formula with fixed data points.

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 talk about copying page layouts. Until then, I hope you all have an enjoyable week.

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

One Response to Copying Formats, Part 4: Charts – PivotCharts

  1. KimC says:

    So easy when you know!!

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>