Copy Worksheet to Another Workbook Without Reference in Excel

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:

Dataset of price to copy for excel copy worksheet to another workbook without reference

Here, our data is in the workbook “Original.xlsx”. And our worksheet is Sheet1.

Let’s add those numbers using SUM.

Sum of the dataset

Now, make a copy of that sheet and paste it into another workbook.

Paste Dataset to new 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.

Copy the dataset

Then. go to the “Copied.xlsx” workbook. Select any cell. Then Right Click.

Select Paste Special to paste the data for excel copy worksheet to another workbook without reference

You will see the option “Paste Special”. Select the option from “Paste Values”. Click it.

excel copy worksheet to another workbook without reference

Now, you can see our data is copied. But in the selected cell, there is no formula. It is copied just as a value.


Similar Readings:


2. Move or Copy Option to Copy Worksheet to Another Workbook without Reference in Excel

Now, look at this dataset:

original workbook for excel copy worksheet to another workbook without reference

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 :

another worksheet of original workbook

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.

move or copy option

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.

Create a copy of worksheet

Select an option. Then Click.

select sheet in new workbook

Now, our data is copied to a new workbook.

Copied worksheet to new workbook

But, you can see the sheet is linked to the Source.

Reference of source workbook

To remove that, click Edit Links in the Data Tab.

Edit links option from Data Tab to excel copy worksheet to another workbook without reference in excel

You will see this prompt.

prompt of edit link after clicking to copy worksheet to another workbook without reference

Click the Break Link Option.

Click Break Link

Now, you will see, your data is no longer linked with the source workbook. It has only the values.

excel copy worksheet to another workbook without reference

Conclusion

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.


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

2 Comments
  1. Is it possible to paste with formulas but without reference workbook? Currently, I’m using “find and replace” and replacing with nothing, but that trick always makes me nervous. Thank you.

  2. Hello Helen,
    You can do it without using the find and replace command. To do this follow the steps carefully.
    1. First, go to the Formula tab on the ribbon.
    2. Then, Select Show Formulas from the Formula Auditing section.
    null

    3. As a result, it will show you the formulas that you use in the workbook.
    null

    4. Now, copy the formulas and paste them into a notepad.
    null

    5. Then, press Ctrl+A to select all and after that, press Ctrl+C to copy.
    6. In a new workbook, paste it by pressing Ctrl +V. As a consequence, you will see the formulas are copied accurately without a reference workbook.
    null

Leave a reply

ExcelDemy
Logo