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 to Another Sheet in Excel**

### 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 Exact Formula 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.

**Similar Readings**

**VBA to Copy Formula from Cell Above in Excel****How to Copy a Formula Across Multiple Rows in Excel****How to Copy SUM Formula 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 and Paste as Text 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: How to Copy Formula to Entire Column in Excel**

### 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**

**How to Copy Formula Down with Shortcut in Excel****How to Copy Formula in Excel Without Dragging****How to Copy a Formula in Excel with Changing Cell References****How to Copy Formula Down Without Incrementing in Excel****Creating and Copying Formula Using Relative References in Excel****How to Copy Formula in Excel to Change One Cell Reference Only****How to Copy a Formula in Excel Without Changing Cell References****[Fixed] Excel Not Copying Formulas, Only Values****Excel VBA to Copy Formula with Relative Reference**