How to Continue Formulas From Sheet to Sheet in Excel
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.
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.
Step_3: Select the Move or Copy command.
The Move or Copy dialog box will pop up on the sheet.
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.
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).
Step_3: Right-click on the triangle button and select Copy (or press CTRL+C).
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.
Final Result
The Sheet1 has the same dataset with the same formulas in the table now.
🔗 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.
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.
Final Result
The other workbook (Book1) has the targeted sheet with formulas now.
🔴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.
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.
Step_6: Drag the selected sheet tabs to the other workbook (Book5) and put them on the Book5 sheet tab.
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 506 times, 1 visits today)
Source: https://excelgraduate.com/copy-a-formula-in-excel-to-another-sheet/