How to Copy a Formula in Excel to Another Sheet (4+ Solutions)

Not knowing the proper method to copy a complex formula is very common among Microsoft Excel users. In this blog post, I will briefly discuss how to copy a formula in Excel to another sheet in different ways.

Introduction to the Dataset

The dataset that I am using for this article has two columns. One column shows the raw data with some country names in detail. the other column has the clean-up data refined with a MID function and a LEN function.

Dataset to Copy a Formula in Excel to Another Sheet

Quickest Way to Copy a Formula in Excel to Another Sheet

The easiest and quickest method to copy a formula with its cell values intact is to use the Move or Copy dialog box.

Suppose, I have a sheet called Quickest Way and I want to copy the sheet along with its formulas.

Now Follow The Guide 
Step_1: Put your mouse cursor on the Quickest Way sheet tab.

Step_2: Right-click on the Quickest Way sheet tab.

Right-click on the Quickest Way sheet tab to Copy a Formula in Excel to Another Sheet

Step_3: Select the Move or Copy command.

The Move or Copy dialog box will pop up on the sheet.

The Move or Copy dialoge Box

Step_4: Inside the Before sheet box, select the Move to end option.

Step_5: Give a tick mark in the Create a copy checkbox.

Step_6: Hit the OK button.

Select Create a Copy to Copy a Formula in Excel to Another Sheet

Final Result

Now, the Quick way sheet has a duplicate copy of it, Quick way (2). If you click on any of the cells you used in any formula in the previous sheet, you will see the formula is also present in the new sheet.

🔗 9 ways to Copy Formula Down in Excel without Dragging

🔗 6+ Ways to Copy a Formula in Excel with Changing Cell References

Alternative Way #1: Use COPY and PASTE Commands to Copy a Formula in Excel to Another Sheet

Another fast way to have the formula from one sheet to another sheet is to copy the whole sheet.

Now Follow The Guide 

Step_1: Put your mouse cursor on the triangle button on the top-left corner of your dataset.

Step_2: Click on the triangle button to select the entire dataset (or press CTRL+A).

Select all cells to Copy a Formula in Excel to Another Sheet

Step_3: Right-click on the triangle button and select Copy (or press CTRL+C).

Use COPY Command to Copy a Formula in Excel to Another Sheet

Step_4: Now click on the ‘+’ icon beside the sheet tabs.

A new sheet will open up. The new Excel sheet of my workbook is named Sheet1.

Step_5: Right-click on any of the cells of the new sheet to open the context menu.

Step_6: From the Paste options, select the first icon from the Paste (CTRL+V) command.

Use PASTE Commands to Copy a Formula in Excel to Another Sheet

Final Result 

The Sheet1 has the same dataset with the same formulas in the table now.

The Sheet1 has the same dataset with the same formulas in the table

🔗 3 Ways to Copy a Formula in Excel without Changing Cell References

🔗 4 Ways to Fill Formula Down to Specific Row in Excel

Alternative Way #2: View Side by Side in Two Excel sheets and Copy a Formula

In the previous methods, we have seen how can we copy a formula in the same worksheet. In this method, I will brief how can you copy a formula from a worksheet to another distinct worksheet.

Now Follow The Guide 

Step_1: Open the two individual Excel sheets.

I am using the same sheet I used in the previous methods named Alternative Method 2 and a new worksheet called Sheet1.

Step_2: Click on the View tab.

Step_3: Under the Window group, select View Side by Side command.

Click on View Side by Side in Two Excel sheets and Copy a Formula

You will see both of the worksheets will be displayed vertically.

Step_4: Now put the mouse cursor on the Alternative Method 2 sheet tab, press CTRL and drag it beside the Sheet1 sheet tab.

Use View Side by Side command to Copy a Formula to another sheet in Excel

Final Result 

The other workbook (Book1) has the targeted sheet with formulas now.

Copy a Formula to another sheet using the view side by side feature

🔴Note: You can do the process without pressing the CTRL-key. But in that case, the sheet will be lost from the source Excel workbook. Without pressing the CTRL key does the Cut-Paste job.

🔗 5+ Proven Methods to Copy a Formula to Entire Column in Excel

🔗 4+ Ways to Repeat Formula Pattern in Excel

Alternative Way #3: Copy a Formula in Excel to Another Sheet in a Different Workbook

At first, you have seen the use of the Move or Copy option for copying formulas in several sheets of a worksheet. But this time, learn to copy a formula from one Excel sheet to another Excel sheet.

Suppose you want to transfer a formula from Alternative Way 3 (Book1) to another sheet named Sheet1 of Book4.xlsx.

Now Follow The Guide 

Step_1: Right-click with your cursor on the sheet tab’s Alternative Way 3 (source sheet for copying formula).

Step_2: Click on the Move or Copy option.  

Step_3: Change the workbook name from the To Book drop-down list.

Book4.xlsx is my destination Excel sheet.

Step_4: Select the Move to End option from the Before Sheet section.

Step_5: Give a checkmark inside Create a Copy checkbox.

Step_6: Hit the OK button.

Selection of Create a copy command to Copy a Formula in Excel to Another Sheet in a Different Workbook

Final Result 

You will find the Alternative Way 3 sheet in Book4.xlsx with the formulas undamaged.

🔗Apply Same Formula to Multiple Cells in Excel (5+ Solutions)

How to Copy multiple sheets with formulas from one Excel Sheet to Another Excel Sheet

This method will help you to work on the big picture. Previously we’ve shown you to copy a single sheet with formulas, but in this process, see how you can copy several sheets with formulas from one Excel sheet to another Excel sheet.

For instance, I have a workbook Book4 containing two sheets inside it, Alternative Way3 and Alternative Way4.

I want to copy the formulas of Alternative Way3 and Alternative Way4 into a different workbook named Book5.

Now Follow The Guide 

Step_1: Open one worksheet (Book4) and minimize it. Then open another worksheet (Book5).

Step_2: Click on the View tab from the main ribbon.

Step_3: Unter the Window group, select View Side by Side command.

You will see the two worksheets will come along in a vertical style.

Step_4: Put your cursor on the sheet tabs from the source workbook (where you have the formulas, Book4).

Step_5: Press the CTRL key and select the sheet tabs you want to copy as per your choice.

select multiple sheets to copy a formula from one Excel Sheet to Another Excel Sheet

Step_6: Drag the selected sheet tabs to the other workbook (Book5) and put them on the Book5 sheet tab.

Copy multiple sheets with formulas from one Excel Sheet to Another Excel Sheet

Final Result

This way you can copy multiple sheets with formulas from one workbook to another.

🔗 Copy Excel Sheet to Another Sheet with Same Format and Formulas

Conclusion

I have tried to cover all the possible methods to copy a sheet with formulas to another sheet in Microsoft Excel. Let us know if you have any other interesting methods of copying a formula from sheet to sheet.

(Visited 1,098 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *