There might be a situation when you need to copy an existing sheet and have it on another workbook. This is a common situation. But, the problem arises when some formulas are linked with another sheet. When you copy those sheets from that workbook and paste that on another workbook, it is automatically linked with that source workbook or it already has some formulas in it. In this tutorial, we will show you 2 ways to copy a worksheet to another workbook without reference in Excel.
Download Practice Workbook
What is a Worksheet With Reference?
Now, we have to first understand what a worksheet is with references. First, take a look at this dataset:
Here, our data is in the workbook “Original.xlsx”. And our worksheet is Sheet1.
Let’s add those numbers using SUM.
Now, make a copy of that sheet and paste it into another workbook.
Here, our new worksheet in the “Copied.xlsx” workbook. But, one thing to notice is that our new workbook already has the SUM formula in it which we don’t want.
2 Ways to Copy Worksheet to Another Workbook Without Reference in Excel
We are going to show you two ways you can copy that worksheet to another workbook without references or formulas.
1. Using Paste Special Values to Copy Worksheet to Another Workbook Without Reference in Excel
We are working with the first dataset. First, Copy the whole dataset.
Then. go to the “Copied.xlsx” workbook. Select any cell. Then Right Click.
You will see the option “Paste Special”. Select the option from “Paste Values”. Click it.
Now, you can see our data is copied. But in the selected cell, there is no formula. It is copied just as a value.
- How to Copy Multiple Sheets in Excel to New Workbook
- Copy Excel Sheet to Another Sheet with Same Format
- How to Copy a Worksheet in Excel (5 Smart Ways)
2. Move or Copy Option to Copy Worksheet to Another Workbook without Reference in Excel
Now, look at this dataset:
Here are some employees’ names and their total sales amounts of a company.
Here is another sheet in this workbook that calculates the total amount of each month :
But look at this sheet. This “Sales In Every Month” sheet is linked with the previous sheet “Sales Data”.
Now, we want to copy that sheet into a new workbook but without any links or formulas. Now, create a new workbook named “Copied Without Reference”. Keep it open. Go to the original workbook.
First, select the “Sales In Every Month” sheet and right-click.
Then, select the “Move or Copy” option. Select the sheet you want to copy.
In the dropdown menu, you will find the new workbook.
Select that and also select Create a Copy option.
Select an option. Then Click.
Now, our data is copied to a new workbook.
But, you can see the sheet is linked to the Source.
To remove that, click Edit Links in the Data Tab.
You will see this prompt.
Click the Break Link Option.
Now, you will see, your data is no longer linked with the source workbook. It has only the values.
I hope this tutorial helped you with that particular problem. By using these 2 ways will help you in Excel copy the worksheet to another workbook without reference. Make sure you download our practice workbook and give this a try. Also, check out our other articles in Exceldemy for various Excel-related problems.