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.
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.
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.
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:
Step 3: Press Ctrl + C on your keyboard.
Right-click on your mouse and select Copy.
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.
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.
Step 6: Now press Ctrl + V on your keyboard.
Right-click on your mouse and select Paste.
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.
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.
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.
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:
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.
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.
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.
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:
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.
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.
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.