Most of the time, in Excel, when we copy data from one workbook and paste it into another workbook, it is expected that data will be copied along with formulas. Unfortunately, this does not happen all the time. To illustrate, when we copy-paste data between workbooks, if the source workbook is not open, data will not be copied with formulas to the destination workbook. In this tutorial, I will guide you on how to copy data along with formulas, regardless of whether the source workbook is open or closed.
How to Copy and Paste Formulas in Excel from One Workbook to Another: 6 Ways
Let’s assume, in Book1, we have a dataset containing several sales data. Here, I have calculated total sales using Excel functions.
Now, consider another workbook (Book2), which contains only sales data, not the summation of sales.
So, I will show you several methods on how to copy total sales data from Book1 to Book2 along with formulas.
1. Use Paste Option to Copy Formulas from One Workbook And Paste to Another
First of all, I will use Excel’s one of the paste options to copy and paste data with formulas.
Steps:
- Go to Book1 and copy the data (E5:E10) by using Ctrl + C from the keyboard.
- Next, go to Book2 and right-click on cell E5. The below menu will appear. Then click on the ‘fx’ (Formulas) icon box from the Paste Options.
- As a result, the formulas were copied to Book2 and we got the total sales.
⏩ Note:
- Remember, while doing this copy-paste method, you have to keep both the workbooks open.
- You can use simple keyboard shortcuts (Ctrl + C, Ctrl + V) to perform the copy-paste operation.
- Paste Special Command can also be used to copy-paste formulas between worksheets. To do that, copy the data from Book1, go to Book2, select the paste location, and press Ctrl + Alt + V to bring the Paste Special dialog. Later, click on Formulas and press OK. Eventually, data will be copied with formulas.
Read More: How to Copy Formula in Excel Without DraggingÂ
2. Copy Formulas Using ‘Show Formulas’ Option from One Workbook
Sometimes if the source workbook is not open, you cannot copy data with formulas. In such cases, you can apply the ‘Show Formulas’ option of Excel to copy data with formulas.
Steps:
- Open Book1, now from Excel Ribbon, go to Formulas > Show Formulas. Or you can type Ctrl + ` to show formulas.
- Consequently, the dataset will look like below. Now copy the formulas from the range E5:E10.
- Then close go to Book2, paste the formulas in Cell E5 (using Ctrl +V). Ultimately, we will get the below result in Book2.
Read More: How to Copy Formula Down Without Incrementing in Excel
3. Find and Replace Option to Change Formulas Then Paste to Another Workbook
This time I will use the Excel Find and Replace feature to copy formulas from one workbook to another. Similar to Method 2, this method will copy data with formulas if the source workbook is not open.
Steps:
- First, go to Book1, select the range (E5:E10) and press Ctrl + H from the keyboard to bring the Find and Replace dialog. Next type ‘=’ in the field: Find what. Enter a space in the field: Replace with. Then click Replace All.
- As a result, 6 replacements will happen and the dataset will look like below. Press OK to close the window.
- Again press Close to disappear the Find and Replace dialog.
- After that copy the range (E5:E10) from Book1.
- Now, open Book2, paste the copied formulas to Cell E5.
- However, now we have to convert the above text to formulas. To do that, again press Ctrl + H to bring the Find and Replace dialog box. This time type a space in the Find what field and ‘=’ in the Replace with field. Click Replace All.
- As a result, again six replacements will be done and formulas will give total sales as below. Finally, press OK > Close to close Microsoft Excel and the Find and Replace window.
Read More: How to Copy Formula and Paste as Text in Excel
4. ‘Move or Copy’ Option to Copy Paste Formulas from One Workbook
Till now, I have discussed how to copy a range of data with formulas between workbooks. Luckily, you can copy an Excel sheet from one workbook to another while keeping the formulas. In this method, I will use the Move or Copy Option of Excel to copy an entire Excel sheet. Here, I will copy Sheet1 from Book1 before Sheet1 of Book2.
Steps:
- Open both Book1 and Book2. Then go to Book1, right-click on the sheet name of Sheet1, and click Move or Copy.
- As a result, the Move or Copy dialog will appear. Now choose Book2 from the To book field. As I have selected Book2 as the destination workbook, all the sheets of the book appear in the before sheet field. I want to copy my sheet before Sheet1. After that, put a tick mark on Create a copy and click OK.
- In the end, see the selected sheet from Book1 is copied to Book2 along with formulas.
Read More: How to Copy Formula to Another Sheet in Excel
5. Copy Formulas from One Excel Workbook to Another by Dragging Worksheets
Likewise Method 4, I will show you how to copy an entire Excel sheet by dragging it from one workbook to another; keeping the formulas intact.
Steps:
- Firstly, open Book1 and Book2 both. Then from Book1 go to View > View Side by Side (Window group).
- Consequently, you will be able to scroll both Book1 and Book2 as below. From here, I will copy Sheet2 from Book1 to Sheet2 of Book2.
- To get my expected result, I have dragged Sheet2 from Book 1 to Book 2. Remember while dragging, press the Ctrl key from the keyboard to copy the sheet to Book 2. Finally, here is the result I have received.
Read More: [Fixed] Excel Not Copying Formulas, Only Values
6. Paste Formulas to Another by Keeping the Link
Let’s assume you have copied data from one workbook to another, but what do you think happens if you change the data in the source workbook? For example, if I change any of the sales data in Book1, Total Sales changes in Book1, but it does not change in Book2. So, to link between workbooks, I will change the summation formula in the source workbook (Book1). Let’s see how we can do that.
Steps:
- In the beginning, in Book1, I added the sheet reference before the cell reference of the summation formula. Thus my formula becomes:
=SUM(Sheet3!B5:Sheet3!D5)
- Next, copy the data range (E5:E10) from Book1 and paste it into Cell E5 of Book 2 (using Ctrl + V). Look when we have copied the formulas in Book2, excel automatically adds the workbook reference to the formula, and the formula becomes:
=SUM([Book1.xlsx]Sheet3!B5:[Book1.xlsx]Sheet3!D5)
- Now if you change any sales data in Book1, the summation of sales changes both in Book1 and Book2.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In the above article, I have tried to discuss several methods to copy and paste formulas in Excel from one workbook to another. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
Related Articles
<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
