How-to Copy a Chart and Change the Data Series Range References

Last week we learned how to copy a chart in Excel quickly and have it reference a new chart data range on the same worksheet.  If you missed it, you can check it out here:

How-To Copy an Excel Chart on Same Worksheet with a New Data Reference

The above technique works well if you have many data points or data series in the chart.  But what if you only have a few?  This post describes a few other techniques that you can use to easily Copy a Chart and Change the Data Series Range References.

Copy an Excel Chart on Same Worksheet Keeps Same Data Reference Final Copied Charts
Copy an Excel Chart on Same Worksheet Keeps Same Data Reference Final Copied Charts

The Breakdown

1) Highlight and Copy Chart and Chart Data Range

2) Paste Chart and Chart Data Range

3) Modify Chart Data Series one of 3 ways

a) Chart Series Dialog Box

b) Chart Series Dialog Box Data Series

c) Drag and Drop Data Series in Worksheet

Step-by-Step

1) Highlight and Copy Chart and Chart Data Range

This is a pretty simples step to copy a chart.  Do like you always do, highlight the Data for the Chart and also the Chart.  Then press CTRL+C to copy or right click and select copy or go to the home ribbon and click on the copy button.

Hightlight Excel Chart and Data in the Worksheet
Hightlight Excel Chart and Data in the Worksheet

2) Paste Chart and Chart Data Range

Now go to the area that you want the new chart and data and select the top left cell where you want the new data and chart.  Then press CTRL+V to paste or right click and select paste or go to the Home ribbon and choose paste.  You will now have a copy of the chart and data but it will not be linked yet to the new data set.  To change the references, follow 1 of the 3 next steps.

Copy Paste an Excel Chart on Same Worksheet Keeps Same Data Reference
Copy Paste an Excel Chart on Same Worksheet Keeps Same Data Reference

3) Modify Chart Data Series one of 3 ways

a) Chart Series Dialog Box

The first way you can change your data set to a new data range reference is to click on the chart you want to change.  Then click on the Design ribbon and then click on the Select Data button.

Next, from the Select Data Source dialog box, highlight the Chart Data Range at the top of the dialog box

Modify Chart Data range from Select Data Source Dialog Box
Modify Chart Data range from Select Data Source Dialog Box

Then select the new data range in your Excel Spreadsheet and then press okay.

Update Chart Data range from Select Data Source Dialog Box
Update Chart Data range from Select Data Source Dialog Box

Your copied chart will now reference the new chart data range

Updated Chart via Data range from Select Data Source Dialog Box
Updated Chart via Data range from Select Data Source Dialog Box

b) Chart Series Dialog Box Data Series

The second way to change your data set is to follow the same steps above, however, instead of changing the entire Chart Data Range, you can select a specific Legend Entries (Series) on the left hand side and press the Edit button:

Edit Chart Series via Data range from Select Data Source Dialog Box
Edit Chart Series via Data range from Select Data Source Dialog Box

Then from the Edit Series dialog box, change your Series Name and Series values by selecting the new ranges in the spreadsheet.

Edit Series Dialog Box
Edit Series Dialog Box

Now this technique takes a LOT longer than the first one, so why would I do it?  Well some chart types like XY Charts break up the data series and you may not be able to simply move the entire chart data range.  So you may have to move each series individually.

c) Drag and Drop Data Series in Worksheet

Another way to modify the Chart Data Series is to drag and drop the series from the old range in the spreadsheet to a new range.  To do this, first select your Chart.  Next, select a series in the chart.  Then when you see the data series highlighted in the spreadsheet, simply click on the highlighted range and drag/drop it to the new data set.

Modify Chart Data Range with Drag and Drop in Excel Worksheet
Modify Chart Data Range with Drag and Drop in Excel Worksheet

Like the previous example, this can take lot longer and should only be used for charts with small data sets or ones that require you to move individual series.  Pick which process you like best as it is all personal preference.

 

Quicker Technique for Charts with Many Data Sets

Check out this post for charts with a large amount of chart series: How-To Copy an Excel Chart on Same Worksheet with a New Data Reference

 

Video Demonstration

This video shows you all 3 Copy a Chart Excel techniques so that you can see exactly how they work:

 

Did I miss any other ways to copy a chart in Excel and modify the data series references in this or the related post?   Let me know in the comments below.

Steve=True