This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Copy a Chart Detached from the Data
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.
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.
Both methods are covered here.
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.
Figure 999 This key is often to the right of the spacebar.
Ctrl+C, Right-Click, U is the Excel 2010 shortcut. In Excel 2007, you would copy, then open the Paste dropdown and choose Paste as Picture.
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.
Figure 1000 Click on a data point to show the SERIES formula.
4. Press the F9 key to convert the formula to an array.
5. Press Enter.
Figure 1001 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.
Figure 1002 You can still format this chart.
Gotcha: If you have three series in the chart, you have to repeat steps 1 through 5 for each series in the chart.