We frequently need to utilize a large number of charts in Excel for a variety of purposes. When working with such a large number of charts, it becomes rather challenging to format each one individually. Excel provides multiple options to copy charts without source data and retain formatting. In this article, we will explore 2 convenient methods to copy chart without source data and retain formatting in Excel.
How to Copy Chart Without Source Data and Retain Formatting in Excel: 2 Simple Methods
In this section of the article, we will learn 2 simple methods to copy chart without source data and retain formatting in Excel. Let’s assume the Marks from the 1st Term to 2nd Term of 6th Grade Students at a school as our dataset. Our goal is to copy the chart for the Marks of 1st Term and paste it into the chart for Marks of the 2nd Term without the source data while retaining the formatting of the 1st chart.
Not to mention that we have used the Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
1. Using Paste Special Option
Using the Paste Special option is one of the easiest ways to copy chart without source data and retain formatting in Excel. Let’s follow the steps mentioned below.
Step 01: Create Chart for 1st Term
- Firstly, select the dataset and go to the Insert tab from Ribbon.
- After that, choose the Insert Column or Bar Chart option.
- Then, click on the Clustered Column option from the drop-down.
As a result, you will have the following chart on your worksheet.
- Now, you can modify and format the chart to get the following output.
Step 02: Create Chart for 2nd Term
- Firstly, select the dataset for 2nd Term and go to the Insert tab from Ribbon.
- Following that, click on the Insert Column or Bar Chart option
- Then, select the Clustered Column option from the drop-down.
Consequently, you will have the following chart in your worksheet as shown in the following image.
- Now, click on the chart for 1st Term and press the keyboard shortcut CTRL + C to copy the chart.
- Following that, click on the chart for 2nd Term and go to the Home tab from Ribbon.
- Then, click on the Paste option from the Clipboard group.
- Subsequently, select the Paste Special option from the drop-down.
- After that, choose the Format option from the Paste Special dialogue box.
- Finally, click OK.
Congratulations! You have successfully copied a chart without source data and retained formatting in Excel. And your final output of the chart should look like the following picture.
2. Saving Chart as Template
Saving chart as Template is another smart way to copy chart without source data and retain formatting in Excel. It allows us to repeatedly use the same formatting without any hassle. Let’s use the instructions outlined below to do this.
Steps:
- Firstly, use the steps mentioned in Step 01 of the 1st Method to obtain the following output on your worksheet.
- After that, right-click on any portion of the chart area.
- Now, select the Save as Template option.
- Then, rename the template file according to your preference and click on Save. Here, we have used Chart Template as our file name.
Note: Don’t change the location while saving the file. Just use the default location that is suggested by Excel. If you change the location, you will not be able to see your template inside Excel.
- Next, select the data for the 2nd Term and go to the Insert tab from Ribbon.
- Then, click on the Recommended Charts option from the Charts group.
As a result, the Insert Chart dialogue box will open on your worksheet as shown in the image below.
- Following that, go to the All Charts tab in the Insert Chart dialogue box.
- Then, click on the Templates option.
- After that, select your created template as marked in the following picture.
- Finally, click OK.
Subsequently, you will have the formatting of the chart of the 1st Term applied to the newly created chart.
- Finally, change the title of the chart. Here we used 2nd Term Marks of the 6th Grade Students as our chart title.
Read More: How to Copy a Chart to Another Sheet in Excel
How to Copy Chart Without Linking Data in Excel
In Excel, when we copy a chart and paste it into a worksheet, the new chart gets pasted with the associated data of the original chart. We can copy chart without linking data in Excel by following some simple steps. Let’s follow the steps outlined below.
Steps:
- Firstly, use the steps mentioned in Step 01 of Method 1 to get the following output.
- After that, right-click on the worksheet name and choose the Move or Copy option.
- Then, from the Move or Copy dialogue box, select the (move to end) option.
- Next, check the field of Create a copy.
- Subsequently, click OK.
As a result, an identical worksheet will be created as shown in the image given below.
- Now, select the dataset and the chart of the newly created worksheet.
- After that, press the keyboard shortcut CTRL + X.
- Following that, come back to the original worksheet and press CTRL + V to paste the copied dataset and the chart.
- At this stage, change the copied dataset as you want and your chart will be changed accordingly.
Here, we inserted the Marks of the 2nd Term and the chart has adjusted automatically according to our change in the dataset.
How to Duplicate Chart with Different Data in Excel
In Excel, when we duplicate a chart, then the data of the original chart gets also duplicated. Let’s use the instructions mentioned below to duplicate a chart with different data in Excel.
Steps:
- Firstly, follow the procedure discussed in Step 01 of the 1st Method to obtain the following output.
- After that, click on the chart and use the keyboard shortcut CTRL + C to copy the chart.
- Then, select any cell of the worksheet and press CTRL + V to paste the copied chart.
- Now, right-click on any portion of the pasted chart and choose the Select Data option.
Subsequently, the Select Data Source dialogue box will open on your worksheet.
- Next, click on the Chart data range field in the Select Data Source dialogue box.
- After that, select the cells of the Marks of 6th Grade Students (2nd Term) table as the new chart data range.
- Finally, click OK.
Consequently, you have the following output as demonstrated in the following picture.
- Lastly, add a new chart title. Here we used 2nd Term Marks of 6th Grade Students as our new chart title.
Read More: How to Copy Chart Format in Excel
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
Download Practice Workbook
Conclusion
So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to copy chart without source data and retain formatting in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below.
<< Go Back To Copy Chart in Excel |Â Excel Charts |Â Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
