How to Copy and Paste Formulas from One Workbook to Another in Excel

Steps:

Go to Book2 and right-click on cell E5. The below menu will appear. Then click the ‘fx’ (Formulas) icon box from the Paste Options.

⏩ Note:

Remember, while doing this copy-paste method, you must keep both the workbooks open.
To perform the copy-paste operation, you can use simple keyboard shortcuts (Ctrl + C, Ctrl + V).
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.

Steps:

Open Book1.
From the Excel Ribbon, go to Formulas > Show Formulas. Or you can type Ctrl + ` to show formulas.

Go to Book2 and paste the formulas in Cell E5 (using Ctrl +V). We will get the below result in Book2.

Steps:

Go to Book1, select the range (E5:E10), and press Ctrl + H to bring the Find and Replace dialog.
Type ‘=’ in the field: Find what. Enter a space in the field: Replace with.
Click Replace All.

6 replacements will occur, and the dataset will look like below. Press OK to close the window.

We have to convert the above text to formulas.
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.

Steps:

Open both Book1 and Book2.
Go to Book1, right-click on the sheet name of Sheet1, and click Move or Copy.

The Move or Copy dialog will appear.
Choose Book2 from the To book field. We have selected Book2 as the destination workbook, all the sheets of the book appear in the before sheet field.
Tick Create a copy and click OK.

See the selected sheet from Book1 is copied to Book2 along with formulas.

Steps:

Drag Sheet2 from Book 1 to Book 2. Remember while dragging, press the Ctrl key to copy the sheet to Book 2.

Here is the result.

Steps:

In Book1, add the sheet reference before the cell reference of the summation formula. The formula becomes:

=SUM(Sheet3!B5:Sheet3!D5)

Copy the data range (E5:E10) from Book1 and paste it into Cell E5 of Book 2 (using Ctrl + V).
When we copy 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)

If you change any sales data in Book1, the summation of sales changes both in Book1 and Book2.

Related Articles