Excel: Copy a Chart Detached from the Data

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: I want to make a copy of the current chart, detached from the data. I have to chart 100 customers, and I want a quick way to move to the next customer, copy the chart, move to the next customer, copy the chart, and so on.

Strategy: There are two different ways to go. Both methods are covered here.

Is the chart perfectly formatted the way that you want it to be? Will you never have to be changed? If this is true, then you can very quickly copy the chart and paste as a picture.

If you need to be able to edit the colors used in the chart, add labels, and so on, then you will want to convert the series formula to values.

To convert a chart to a picture, use these steps:

  1. Click on the chart.
  2. Ctrl+C to copy
  3. Click in a new location.
  4. Press the Right-Click key. Type U.

  1. This key is to the right of the spacebar.

    Gotcha: With the pictures, you can never use the chart tools to change the formatting of the chart. If you need to do that, use the following method.

    1. Click on one series in the chart. Click on one of the columns, bar, or markers in the chart to select the series.
    2. You will see a =SERIES formula in the formula bar.
    3. Click in the formula bar and select the entire formula.

  2. Click on a data point to show the SERIES formula.
    1. Press the F9 key to convert the formula to an array.
    2. Press Enter.

  3. F9 converts the formula to values.

    You now have a chart based on static values, but you can still use all the charting tools to format the chart.

    Gotcha: If you have three series in the chart, you have to repeat steps 1 through 5 for each series in the chart.