Copying Data and Graphs - Graph data is staying linked to the copy source rather than the paste data

[Mark McInerney]

Hi All,

I have a series of data that is linked dynamically to a cell in a drop down - Cell A1 is the drop down cell. The resultant data is then used to create a series of Excel Graphs. Both the data and graphs run from Row 1 thru 40.

I copy this data, from column 1 thru 40, to row 41 thru 80. My drop down list is now copied into cell A41. As I select a different value in this cell, all the data in rows 41 thru 80 updates, but the graphs that are contained in the same rows, and still linked to the data in rows 1 thru 40?

When I select data in the graph, I have tried removing the $ signs, as I thought this was what was linking the graghs in row 41 thru 80? When i do this nothing happens?

Any advice greatly appreciated.

Best - Mark.

 

[GraH]

The chart does not know it needs to refer to the copied range. You need to update it manually. Look at the coloured rectangles when you select the chart. Move them to the new (copied) range.

 

[Mark McInerney]

Hi - Thanks for taking time to review this - appreciated.

Sometimes when I click the graph, the data that creates the graph is highlighted, and other times not - how do I change this to allow me to do exactly what you suggest above?

 

[GraH]

As far I know it always does highlight the dataseries source. It may depend on where you click on the chart or perhaps on the the chart type used?

 

[Mark McInerney]

Hi - Thanks - Will give it a go.

 

[Jon Peltier]

If you select the chart, it will highlight the source data range, if that range is "simple". Otherwise, it won't highlight anything.

If you select the chart series, it will highlight the source data range, if it's in the same worksheet, whether the range is "simple" or not.

I describe this here: Excel Charting Dos and Don’ts. Scroll down a couple screens to "Highlighting a Chart’s Data".

Also more verbosely in Chart Source Data Highlighting and Highlighted Chart Source Data.