Copy Series of Formatted Charts & easily Chg Range source

[Kohai]

I'm running XCL'02 and have to make hundreds of charts
that are all going to look the same but just have
different ranges of data feeding them (similar ones will
be printed together for comparison). For example, if I
have 5 charts lined down a Col. each with their proper
source data and format, I want to (hopefully easily!)
copy the formatted charts, paste in the adjacent col. and
ideally have some sort of cell referencing feed in the
new data source. Currently, I have to manually change
the source range to update the new data source. Not fun
when you have to repeat it lots of times.

Thank you in advance for your help.

Kohai

 

[Ed Ferrero]

Hi Kohai,

One handy trick. Build a chart based on one of the series. Format the chart
the way you like. Now, click on the series in the chart and press Delete.
You should have a perfectly blank chart. Copy this blank chart however many
times you like. Then select the cells for a new series and drag the
selection onto a blank chart. The resulting chart has all the original
formats. Repeat for as many series as you like.

Ed Ferrero
http://edferrero.m6.net

 

[Jon Peltier]

To make a bunch of charts look alike, format one the way you want it,
copy it, select the next chart, and from the Edit menu, choose Paste
Special, and select the Format options. Select the next chart, press the
F4 key. Keep selecting and pressing F4 until they're all formatted the same.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Brian Murphy]

It's been my experience that, when done from VBA at least, deleting a chart
series resets its formats to defaults.

Have you found this to be otherwise?

Brian Murphy
Austin, Texas

 

[Ed Ferrero]

Hi Brian,

You are right, deleting a chart series does reset its format to default.

Using my method (described below) keeps all original formats except for
any formatting applied to the series.

So if you want to copy formats for the series as well, you need to use
Jon Peltier's suggestion of using copy / paste special - formats.

On the other hand, Jon's method does not copy any embedded chart objects
(like a text box) that may be included in the original chart. So the
method you should use depends on the original chart.

BTW, when needing to chart many series I prefer to build just a few
charts that refer to a range of cells in the worksheet, and change
the contents of the worksheet range when I need to chart a new series.

I generally use drop-downs and some VLOOKUP or INDIRECT(ADDRESS..)
formulas to select new series and change the worksheet range.

That way, if I need to print a 20 page report with 4 charts per page,
I just need to build one worksheet with 4 charts and cycle through
the required series with a bit of VBA code. Makes for much smaller
workbooks that are a lot easier to maintain.

Ed Ferrero
http://edferrero.m6.net

 

[Jon Peltier]

Hi Brian -

That's true with series and with other chart elements (axis titles,
etc.). As soon as you delete the element, it isn't there to remember
what non-default formatting it had. When the element is then recreated,
it only knows the defaults, and that's what you are stuck with.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______