How to Copy Chart Without Source Data and Retain Formatting in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

excel copy chart without source data and retain formatting

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.

Using Paste Special Option to copy chart without source data and retain formatting in Excel

As a result, you will have the following chart on your worksheet.

Final output of step 1 of method 1 to copy chart without source data and retain formatting in Excel

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.

Create Chart for 2nd Term to copy chart without source data and retain formatting in Excel

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.

Using Paste Special option to copy chart without source data and retain formatting in Excel

  • 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.

Final output of method 1 to copy chart without source data and retain formatting in Excel


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:

Saving Chart as Template to copy chart without source data and retain formatting in Excel

  • 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.

Inserting 2nd chart to copy chart without source data and retain formatting in Excel

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.

Final output of method 2 to copy chart without source data and retain formatting in Excel

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:

How to Copy Chart Without Linking Data in Excel

  • 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.

Final output of method 3 to Copy Chart Without Linking Data in Excel


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:

How to Duplicate Chart with Different Data in Excel

  • 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.

Changing Data Source to Duplicate Chart with Different Data in Excel 

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.

Final output to Duplicate Chart with Different Data in Excel 

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.

practice section to copy chart without source data and retain formatting in Excel


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 ExcelExcel ChartsLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo