Copying Charts

I am creating a Dashboard for my company and have 8 charts on top each other in one Excel sheet. I have a question about copying the chart down the page.

When I copy chart 1 down 20 rows to create chart 2, the data ranges are absolute and cannot be set to relative. As such, I have to manually adjust the data ranges of every chart. I have 8 charts for 25 salesmen so I don't want to have to manually reset 200 charts. How can I copy Chart 1 down 20 rows and get the data range to also be reading the data (over to the right) 20 rows down?

This is actually very easy to do. Simply activate the chart in question, by selecting its sheet tab if it's a chart sheet or double clicking it if it's an embedded chart. Then select each data series in the chart, place your cursor in the formula bar and press the F9 key. This will turn the SERIES equations for each data series into hard-coded data. You can then copy the chart anywhere you like and you will not create links to the original workbook.

NOT MINE I FOUND IT ON THE WEB

Thanks for your reply Robert but I apparently don't understand. When I double click on my embedded chart, I get the "Format Chart Area" box and it does not have the data series anywhere. If I go to the Menu Option "Chart Data" where I can actually see the data Series, if I put my cursor in either of the "Data Range" or the "Value" box (where I see the actual absolute range it is using) & hit F9, nothing happens.

What am I doing wrong?

Thanks for your help.

Originally Posted by rrucksdashel

I am creating a Dashboard for my company and have 8 charts on top each other in one Excel sheet. I have a question about copying the chart down the page.

When I copy chart 1 down 20 rows to create chart 2, the data ranges are absolute and cannot be set to relative. As such, I have to manually adjust the data ranges of every chart. I have 8 charts for 25 salesmen so I don't want to have to manually reset 200 charts. How can I copy Chart 1 down 20 rows and get the data range to also be reading the data (over to the right) 20 rows down?

Hi rrucksdashel,

When you move chart 1 down 20 rows you should have 8 black boxes around the edge of the chart diagram. Right click on this edge of the chart and select Source Data > Data Range, you will see the data range is hightlighted on the data for the first chart. Select your range of data for the second chart (presumably 20 rows lower).

You second chart is now adjusted to reflect all the data in that range. You can now do this for each chart.

Hope this helps

oldchippy