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.

Copy a Formula to Another Sheet.xlsx

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.

Table of Contents

Copy a Formula in Excel to Another Sheet Using Move or Copy Dialog Box

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.

Steps to copy a formula to another sheet in Excel using Move or Copy dialog box.

Put your mouse cursor on the sheet tab.

Right-click on the sheet tab.
Select the Move or Copy command.
The Move or Copy dialog box will pop up on the sheet.
Inside the Before sheet box, select the Move to end option.

Give a tick mark in the Create a copy checkbox.
Hit the OK button.

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

Copy a Formula in Excel to Another Sheet Using COPY and PASTE Commands

Another fast way to have the formula from one sheet to another sheet is to copy the whole sheet. Follow the steps below to use copy and paste commands in Excel:

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

Click on the triangle button to select the entire dataset (or press CTRL+A).
Right-click on the triangle button and select Copy (or press CTRL+C).
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.

Right-click on any of the cells of the new sheet to open the context menu.
From the Paste options, select the first icon from the Paste (CTRL+V) command.

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

Copy a Formula to View Side by Side in Two Excel sheets

In the previous methods, we have seen how can we copy a formula in the same worksheet. In this method, I will briefly how can you copy a formula from a worksheet to another distinct worksheet. To do that, follow these steps below:

Open the two individual Excel sheets.

Click on the View tab.
Under the Window group, select View Side by Side command.
You will see both of the worksheets will be displayed vertically.
Now put the mouse cursor on the sheet’s name, press CTRL, and drag it beside the Sheet1 sheet tab.

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.

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. To do that, follow these steps below:

Right-click with your cursor on the sheet’s name(source sheet for copying formula).

Click on the Move or Copy option.
Change the workbook name from the To Book drop-down list.
Book4.xlsx is my destination Excel sheet.
Select the Move to End option from the Before Sheet section.

Give a checkmark inside the Create a Copy checkbox.
Hit the OK button.

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

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 how 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 Book 4 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. Here’s how:

Open one worksheet (Book4) and minimize it. Then open another worksheet (Book5).
Click on the View tab from the main ribbon.
Unter the Window group, select the View Side by Side command.
You will see the two worksheets will come along in a vertical style.

Put your cursor on the sheet tabs from the source workbook (where you have the formulas, Book4).
Press the CTRL key and select the sheet tabs you want to copy as per your choice.
Drag the selected sheet tabs to the other workbook (Book5) and put them on the Book5 sheet tab.

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

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.

Frequently Asked Questions

How do I link a formula to another sheet in Excel?

To link a formula to another sheet in Excel, follow these steps:

Click on the sheet where the source data or formula resides.
Click on the cell containing the formula you want to link.

In the formula bar, type = to begin the formula.
Click on the target sheet tab.
Click on the cell or range you want to reference in the target sheet.

Press ENTER to complete the formula.
Example: If your source formula is in cell A1 on Sheet1 and you want to link it to cell B1 on Sheet2, the formula would be =Sheet2!B1.

By following these steps, you can easily link a formula to another sheet in Excel, allowing you to reference data or calculations across different sheets within the same workbook.

How do I copy a formula to an entire column in Excel?

To copy a formula to an entire column in Excel, follow these steps:

Click on the cell containing the formula you want to copy.
Move the cursor to the lower-right corner of the selected cell. The small square, known as the Fill Handle, will appear.
Double-click the Fill Handle, and Excel will automatically fill the formula down the entire column until it encounters an empty cell in the adjacent column.
Alternatively, you can also drag the Fill Handle down the column to cover the desired range and release the mouse button to complete the copy.

By using the Fill Handle, you can quickly copy a formula to an entire column in Excel, saving time and effort.

Can Excel formulas cross sheets?

Yes, Excel formulas can reference cells and ranges across multiple sheets. You can achieve this by using sheet references in your formulas. Here’s how:

Example: Referencing a Cell in Another Sheet
Suppose you have data in Sheet1, and you want to reference cell A1 from Sheet2.
In the formula bar, type =Sheet2!A1 and press ENTER.
This formula syntax (SheetName!CellReference) allows you to refer to cells in other sheets.

Example: Summing a Range from Another Sheet
If you want to sum a range of cells in Sheet2 from Sheet1, you can use a formula like =SUM(Sheet2!A1:A10)
In these examples, replace “Sheet2” with the actual name of the sheet you want to reference.

Excel formulas can indeed cross sheets, making it flexible and powerful for data analysis and manipulation across different parts of a workbook.

Rate this post