Assume we have created a chart based on a data set. However, we may have another dataset and wish to create a chart similar to the previous one. Then we don’t need to create another one; simply duplicate the chart while keeping the format and editing with different data. Excel is a fantastic tool for working with charts. Let’s see how to duplicate a chart with different data in Excel.
How to Duplicate Chart with Different Data in Excel: 2 Effective Ways
In the following dataset, we have information about a company’s sales in three US states in four consecutive months (January, February, March, and April) and a chart that represents this information. We want to duplicate the chart. But simply copying and pasting the dataset does not allow us to modify the chart according to the newly made dataset. It changes shape according to the previous dataset. Let’s show you two methods to duplicate a chart with different data in Excel.
1. Duplicate Chart with Different Data Using Chart Design Tab in Excel
The Chart Design tab in Excel assists in determining which data set will represent the chart. If we duplicate the data set with a chart, it is possible that the duplicated chart does not represent the duplicated dataset but rather the original data set. We can select which data is controlling the chart from the Chart Design tab. Let’s follow the steps below to learn the method.
STEPS:
- First of all, you need to make the dataset ready.
- To do so, insert the data in an Excel sheet and make a chart.
- Here, we have used the range B4:E8 to insert the sales information.
- Also, we have created a sales information chart using the Insert tab.
- Secondly, select the range that covers the dataset and the chart.
- In our case, we have selected the B2:N13 range as these cells contain our data.
- After that, press CTRL+C to copy.
- You can also right-click on your mouse and the following menu will pop up.
- Then, click Copy to copy the data.
- In the following step, select a blank space in the same sheet where you want to put your data and chart.
- Press CTRL+V to paste.
- We can also paste after selecting the desired space and right-clicking on the mouse.
- Then the following window will be shown up and we will click paste.
- Therefore, the data we have copied is shown in our desired place.
- But the problem here is the chart will not change according to the newly formed data set.
- The chart will only change according to the previous Data Set which is not our desire.
- Now to solve the problem we will click on the chart.
- Then, from the Chart Design tab in the ribbon click on Select Data.
- After that, we can see that the following window has shown up.
- Here Chart data range indicates which Data Set is controlling the chart.
- A trembling dotted line can be seen on the sheet indicating the chart’s information. It is a representation of the B4:E8 range.
- But we want our chart will represent the newly formed dataset.
- So, follow the steps below to adjust the chart’s data set.
- In the following step, select your newly formed Data Set.
- As the B25:E29 range indicates our newly formed duplicated Data Set, we will select the range B25:E29.
- Automatically, the Chart data range will be set from the previous to the present Data Set.
- Now press OK.
- Therefore our dataset and chart are ready.
- This duplicated chart represents the duplicated newly formed data.
- To check we change sales of Florida state from $5000 to $3000.
- Automatically the chart also shows the changed value of $3000 from $5000 in Florida in January.
Read More: How to Copy a Chart to Another Sheet in Excel
2. Utilize Move or Copy Option to Duplicate Chart on Same Worksheet with Different Data in Excel
Rather than duplicating the dataset and chart in the same worksheet, we can use another worksheet to successfully duplicate the chart with different data in Excel. Let’s observe the steps below to learn the whole method.
STEPS:
- In the beginning, you need to make the dataset ready.
- For that purpose, insert the data in an Excel sheet and make a chart.
- Here, we have used the range B4:E8 to insert the sales information.
- Also, we have created a sales information chart using the Insert tab.
- Next, select the sheet from where you want to copy the chart and right-click on it.
- It will open the Context Menu.
- Now, select Move or Copy from the Context Menu.
- After that, a new selection dialog box will pop up and we can select where a new sheet will be created.
- We have selected Move or Copy as we want the new sheet to be created before our main sheet.
- You can choose this as per your wish where you want to place your new sheet.
- Click on Create a Copy and click OK.
- A new sheet will be created which will be a copied version of our Data Set and Chart.
- Then, we will select the dataset and chart.
- In our case, we will select the range B2:N13.
- Now, press CTRL + X to cut the range.
- We can also cut the range by right-clicking on the mouse and, after that selecting cut from the dialog box.
- Now, paste the dataset and chart into the Move or Copy sheet which is our original sheet.
- Under the original dataset select a place where you want to duplicate your information.
- We have selected cell B18 and pressed CTRL + V.
- You can also paste by right-clicking on the mouse and selecting “paste” from the dialog box.
- Ultimately our desired dataset and chart are pasted.
- To check if the chart represents the data, we can change a value from duplicated data set if watch if the chart is also changing.
- We have changed the sales of January month in Texas state from $6000 to $2000.
- We can notice that the chart also shows the trend that January month’s Texas sales changed from $6000 to $2000.
- So, we have successfully duplicated the chart with different data.
Read More: How to Copy Chart Without Source Data and Retain Formatting in Excel
Download Practice Workbook
To practice by yourself, download the following workbook.
Conclusion
In this article, we have demonstrated how to duplicate charts with different data in Excel in two methods. There is a practice workbook at the beginning of the article. Go ahead and give it a try. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.
Related Articles
<< Go Back To Copy Chart in Excel |Â Excel Charts |Â Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
