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

Many users in Excel spend time creating an awesome chart on a specific data set, then they want to use that chart on another data set.  Perhaps it was for the month of April data and now May is starting and you want to reuse the same chart.  How can you do this quickly and keep it in the same Excel Worksheet?

The Problem with Copy / Paste

Now you may think that you can just highlight and then copy the data and the chart using standard Excel copy functionality.  Then find another cell in the worksheet and paste the results from the clip board, but here is what you get.

The new chart is “STILL” linked to the original data reference.  Check out this image of a copy of the chart and data.  If you click on a series in the duplicate chart, you will notice that it still references the original data.

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

Copy to a New Worksheet

Previously, I showed you how to copy the chart to a new worksheet.  If you want to know more about this topic, check out this post.  This tutorial will be similar but the change is that we want the chart to be placed on the same worksheet, not a new Excel worksheet tab.

How-to Copy Charts and Change References to New Worksheet

Now if you are looking to put the copy of the chart on the SAME worksheet, you will need to follow these modified steps.

 

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

The Breakdown

This solution will take a few minor steps but will give you what you desire.

1) Copy Worksheet Tab to a New Worksheet Tab in the Excel Workbook

2) Select and Cut Data and Chart

3) Paste Data and Chart in Original Worksheet

 

Step-by-Step

This solution will take a few minor steps but will give you what you desire to have a copy of the chart on the same worksheet that references a new data range.

For this sample, we will start with the chart on Sheet1 and the Excel file will look like this:

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

1) Copy Worksheet Tab to a New Worksheet Tab in the Excel Workbook

To copy our chart and make it reference a different data range, we first need to make a copy the Worksheet from the Worksheet tab.  This will not work if you just copy the range of data including the chart.  It must be done by copying the entire worksheet.

First right Click on the sheet where the original chart and data series are stored.

Excel Worksheet Tab
Excel Worksheet Tab

Then Click on the Move or Copy… choice in the pop-up menu:

Move or Copy Worksheet Right Click Menu
Move or Copy Worksheet Right Click Menu

Next ‘Check’ the “Create a copy” checkbox.  Then change the “To book:” to the other workbook that you want this Excel Dashboard Chart to be copied.

Move or Copy Dialog Box
Move or Copy Dialog Box

You will now have a copy of the chart AND data range on the

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

2) Select and Cut Data and Chart

Now that we have a copy of the original chart and it is linked to a new data range reference we can use to cut and paste back on to the original worksheet tab.

In order to do this, you must select both the chart and the chart data reference range in the worksheet with your mouse.

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

Make sure you highlight the data and include the entire chart in your highlighted range.

Then “Cut” the highlighted range using your favorite method (either press CTRL+X or right click on the highlighted range and select Cut from the pop-up menu or go to the Home ribbon and press the Cut button in the Clipboard group).  Your data and associated chart are now in the Clipboard and ready to be pasted where ever you like.

 

3) Paste Data and Chart in Original Worksheet

Now that the data and associated chart are cut and in the clipboard, we can paste them anywhere we like.  Since we wanted to copy an Excel chart on same worksheet with a new data reference, we should go back to the original worksheet (in our case Sheet1).

Then simply use any paste feature of Excel that you like.  Either press CTRL+V or right click on the highlighted range and select Paste option that you desire from the pop-up menu or go to the Home ribbon and press the Paste button in the Clipboard group.  In my case, I simply went below the original chart on Sheet1 and put the newly copied chart there.

Your final Sheet1 worksheet tab will now look like this:

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

IMPORTANT NOTE

You MUST use the CUT function for the data references to change.  If you COPY the data and associated chart, then your final pasted chart will STILL reference the data from the new chart sheet.  So please remember to use the CUT function, not Copy.

Copy an Excel Chart on Same Worksheet Keeps Same Data Reference Final Charts using Copy not CUT
Copy an Excel Chart on Same Worksheet Keeps Same Data Reference Final Charts using Copy not CUT

 

Check out this short video demonstration on this quick and easy Excel technique that shows you all the tips and tricks about it.

 

Video Demonstration


Do you have any cool techniques to share?  Let me know in the comments below!

Steve=True