How to Copy Excel Sheet with Formulas to Another Workbook (5 Ways)

Copying the Whole Worksheet

While working in Excel, it is one of the most common problems that we face to copy Excel sheet with formulas from one workbook to another. Today I am going to show you how to copy one or more sheets with formulas from one workbook to another with proper illustrations.


How to Copy Excel Sheet with Formulas to Another Workbook

1. Copy Excel Sheet with Formulas to Another Workbook by Dragging

We have a worksheet called Sheet1 in a Workbook called Workbook_1.

Data Set to Copy Excel Sheet with Formulas to Another Workbook

Now we want to copy this Sheet1 from Workbook_1 to Workbook_2.

Step 1: Open the two workbooks on your computer.

The workbook you want to copy and the workbook to which you want to copy.

Step 2: On the Excel Toolbar, go to the View Side by Side option under the View tab.

View Side by Side Option to Copy Excel Sheet with Formulas to Another Workbook

Step 3: Press the View Side by Side option. It will arrange the two workbooks vertically like this.

Step 4: Now press Ctrl on your keyboard and drag the Sheet1 from Workbook_1 to Workbook_2.

It will be renamed as the same name on the source workbook to the destination workbook. Like in my case, it has been renamed as Sheet1(2) in Workbook_2.

Dragging the Sheet from One Workbook to Another to Copy Excel Sheet with Formulas to Another Workbook

Note: If you do not press Ctrl and still drag, the Sheet will be copied to the destination workbook but it will be lost from the original workbook. Like the Cut and Paste thing we do on our computers. So be careful.

See, you have successfully copied one Excel Sheet from one workbook to another workbook. Everything including the formulas of Workbook_1 has been copied to Workbook_2.


2. Copy Excel Sheet with Formulas to Another Workbook by Clicking Ctrl + C and Ctrl + V

If you do not want to follow the steps above, you can use these steps and easily copy an Excel Sheet from one notebook to another.

Step 1: Open the Workbook from where you want to copy the Sheet.

Here, I open Workbook_1.

Step 2: Click the small triangle in the upper left corner of the Worksheet, or press Ctrl + A. You will have the whole Worksheet selected like this:

Selecting the Whole Worksheet to Copy Excel Sheet with Formulas to Another Workbook

Step 3: Press Ctrl + C on your keyboard.

OR

Right-click on your mouse and select Copy.

OR

Click on the Copy option under the Home tab from Excel Toolbar. See the Figure.

Step 4: You will find the border of the Sheet highlighted like this. This means you have successfully copied the Worksheet.

Border of the Sheet Highlighted after Being Copied to Copy Excel Sheet with Formulas to Another Workbook

Step 5: Now open the second workbook (The workbook in which you want to copy the Sheet) and select the upper-most left cell in a Sheet of that workbook.

Here I opened Sheet1 from Workbook_2 and selected the uppermost left cell.

Second Workbook Opened to Copy Excel Sheet with Formulas to Another Workbook

Step 6: Now press Ctrl + V on your keyboard.

OR

Right-click on your mouse and select Paste.

OR

Select the Paste option from the leftmost corner of the Home tab of Excel Toolbar. See the figure.

Step 7: You will find everything from Sheet1 of Workbook_1 copied to Sheet1 of Workbook_2.

Copied to Second Workbook to Copy Excel Sheet with Formulas to Another Workbook

So, you have successfully copied one Sheet from a workbook to a Sheet of another workbook. Everything including the formulas of Workbook_1 has been copied to Workbook_2.


3. Copy Excel Sheet with Formulas to Another Workbook by Move or Copy Dialogue Box

If you do not want to follow the above two methods, you can use this method to copy one Sheet from an Excel workbook to a Sheet of another workbook.

Step 1: Bring your mouse cursor to the Sheet1 of Workbook_1.

Bringing Mouse Cursor to Sheet1 to Copy Excel Sheet with Formulas to Another Workbook

Step 2: Right-click on your mouse.

Then select the Move or Copy option.

Step 3: You will get a small box called Move or Copy.

Image 13.Move or Copy Dialogue Box to Copy Excel Sheet with Formulas to Another Workbook

Step 4: In the To books option, select the workbook to which you want to copy. I want to copy to workbook_2, so I choose workbook_2.

And in the Before Sheet option, choose where you want to place the Sheet in Workbook_1.

I want to place it before Sheet1, so I choose Sheet1.

And must check the create a copy option. (If you do not check the create a copy option, the sheet will be lost from the source workbook. So be careful.)

So my box now looks like this:

Move or Copy Dialogue Box Filled to Copy Excel Sheet with Formulas to Another Workbook

Note: Your source workbook (Workbook_1) and destination workbook (Workbook_2) must remain in the same folder on your computer. Otherwise, you will not find the destination workbook in the above box.

Step 5: After successfully following the above steps, you will find that a copy of the Sheet from the original workbook has been created in your destination workbook.

Here, in my case, a copy of Sheet 1 from Workbook_1 has been created in Workbook_2 (Sheet1(2))


4. Copy Multiple Excel Sheets with Formulas to Another Workbook

All the above methods can be used to copy one sheet from one workbook to another workbook at a time.

If you want to copy multiple Sheets from one workbook to another at a time, follow these steps.

Before this, let us have a workbook, Workbook_1 which is composed of two Worksheets this time.

Sheet1 and Sheet2.

Two Worksheets in a Workbook to Copy Excel Sheet with Formulas to Another Workbook

We will copy both the sheets to Workbook_2 this time.

Step 1: Open the two workbooks and press the View Side by Side option under the View tab. It will arrange the two workbooks vertically like this.

Vertically Arranged Workbooks with Multiple Sheets to Copy Excel Sheet with Formulas to Another Workbook

Step 2: Double click on any one sheet on the bottom left corner of the source workbook. Then press Ctrl and click on the other sheets.

Here I have clicked on Sheet1 of Workbook_1 first, then on Sheet2.

If you can successfully do this, you will find Workbook_1 [Group] written on the uppermost left corner.

Step 3: Press Ctrl and drag the Worksheets to your destination workbook (Workbook_2).

Do not forget to press Ctrl. Otherwise, it will be copied to the destination workbook but lost from the source workbook. Like the Cut and Paste thing on the computer.

Here, in my case, Sheet1 and Sheet2 have been copied from Workbook_1 to Workbook_2.

Multiple Worksheets Copied to Another Workbookto Copy Excel Sheet with Formulas to Another Workbook


5. Copy Excel Sheet with Formulas to Another Workbook by Keeping Link

Let’s add a new column to our data set.

Column E, which contains the average of the marks in Physics and Chemistry.

Therefore, it contains a formula:

=(C5+D5)/2

Data Set with Formula to Copy Excel Sheet with Formulas to Another Workbook

Now if I copy this worksheet to another workbook by any method, the formulas will be copied.

But any change in Workbook_1 will not make the same change in Workbook_2.

For example, if I change the marks of the first student in Physics from 75 to 77 in Workbook_1, the average mark will change from 77 to 78 in Workbook_1.

But will not change in Workbook_2.

Then how to fix it?

Simple. In the formula bar in Workbook_1, just insert the name of the sheet (Sheet_1) before the cell reference.

=(Sheet1!C5+Sheet1!D5)/2

Formula with Sheet Name to Copy Excel Sheet with Formulas to Another Workbook

Now I have copied the Sheet from this workbook to another workbook, Workbook_2, by any of the methods mentioned above.

Now carefully look at the function bar of the copied Sheet in Workbook_2.

Excel has automatically put the name of the original workbook before the Sheet name. In my case, it is [Workbook_1.xlsx].

So now it means that the cell reference of the cell is now of Workbook_1, though it is in Workbook_2.

Now if we change anything in Workbook_1, the equivalent change will be made in Workbook_2.

Thus you can create a link between the original Worksheet and copied Worksheet in Excel. Just by putting [Name of the Sheet!] before the cell reference in the original Sheet.


Conclusion

So using these methods, you can copy one or more Worksheets from one workbook to another workbook. Do you know any other method? Let us know in the comments section.


Further Readings

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo