Using existing charts in another workbook

[flightjock]

Hi,

Many times it's convenient to use an existing chart in another workbook. It's easy to copy the chart to the new workbook, however, the chart then will reference to the original workbook/cells.
Especially when there are several series in the chart it is a little user unfriendly to change all the series reference to the new data in the new workbook. Is there an easy way of changing all references in one step?

Anthony

 

[Mark W.]

Why not just save your chart's format as a custom chart type, and use that chart type when you create a chart in the new workbook?

 

[Jon Peltier]

If the chart is embedded in the worksheet that contains all its data, and the new data has the same structure (i.e., all the same information is in the same cells), you can copy the entire sheet, including the embedded chart, then copy the new data, and use paste special-values to put it into the chart's source range.

Another option is a routine I posted on my web site:

http://www.geocities.com/jonpeltier/Excel/Charts/ChgSrsFmla.html

which is a string search & replace utility for chart series formulas. You can replace one sheet name with another, or change rows or columns, etc. This page tells how it works, and it gives a link to a free downloadable form-based utility that does it for you.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[flightjock]

Thank you guys!

John, I regularly scan your site for useful things (there are many!), but I missed this one. This is exactly what I want.

Anthony

 

[Jon Peltier]

Anthony -

One day, I promise to make a decent index. When I get a minute, right.

- Jon