Creating many copies of a graph that DON'T all reference the same data series?

[alexmuse]

Hi all,

I would greatly appreciate if someone could help me


The scenario:

Column A contains x-axis values
Column B contains y-axis values
Column C contains a line graph that plots the data range from column A and B.

I now can create multiple copies of that graph in the following two ways:

1) Left-click and select the data series and graph in columns A,B and C.
Then I click and drag the fill-handle on the bottom left in order to create a total of 5 graphs in columns A to O.

2) Left-click and select the data series and graph in columns A,B and C.
Then manually copy into the adjacent D column, then G column, and so on, until I have 5 graphs.


Now the problem:

Either method creates 5 graphs that ALL use the data series from columns A and B (see graph 5 in picture, which shows this).

What I would like however, is that each graph uses the data in the columns adjacent to it. So graph number two which is in column F, will use the data from columns D and E. Graph three in in column I, would use the data from columns G and H, and so on.

Thanks in advance!

[teylyn]

Hello,

when a chart is copied, that is exactly what happens. The chart is copied with all its properties, including the links to the source data. After copying and pasting the chart you will need to edit the chart and point it to the desired data source for the copied chart.

That's just how Excel works.

BTW, the XY charts in your sample each have two series that overlap, because they use exactly the same data. Also some of the charts have been copied multiple times and pasted exactly over each other. That will blow up your workbook size and may even slow it down.

cheers, teylyn

[alexmuse]

I will need to create graphs for over 600 different data series; manually editing each chart will take me a very long time.

In the future, I might need to modify how the graphs look like, which again will take a very long time to manually edit each one.

I was hoping there is some sort of easy fill handle approach??!!

Excel seems good at fill-handle operation which saves tons of time doing other tasks - I can't believe they haven't though of this with regards to copying graphs!

Perhaps there are some macros that can perform this?

Cheers,
Alex

[teylyn]

You don't want to use the fill handle to copy a chart 600 times, either. You need a macro to help with that. To get you started:


Style the chart the way you want it to look. Save that styled chart as a template. Now you can create charts from that template.

- select the data for the chart
- turn on the macro recorder
- create the chart
- select the template
- size the chart
- stop the macro recorder

Now you have code that creates a chart from the selected data and makes the chart the size and style you want.

If you need to change 600 charts, again, do it once, save the chart as a template and then change the chart type of existing charts to that new template.

All this can be automated with VBA. The macro recorder will give you the bulk of the code you need. You can then fine-tune the code to be applied to all charts in the worksheet, or to the selected chart or other scenarios. If you post a sample workbook and the code you have generated, we can help with the fine-tuning.