Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to copy formulas from one workbook to another without link?

For example, you need to copy formulas in selected range from current workbook to another workbook without link, how to solve it easily in Excel? Here we will share three methods with you.

Copy formulas from one workbook to another without link by changing formulas (6 steps)

Copy formulas from one workbook to another without link by changing formulas to text (3 steps)

Copy formulas from one workbook to another without link by Exact Copy (3 steps)

Copy formulas from one workbook to another without link by Auto Text

Easily combine multiple worksheets/workbooks into single worksheet/workbook

It may be tedious to combine dozens of sheets from different workbooks into one sheet. But with Kutools for Excel’s Combine (worksheets and workbooks) utility, you can get it done with just several clicks! Full Feature Free Trial 60-day!
ad combine sheets books 1


arrow blue right bubbleCopy formulas from one workbook to another without link by changing formulas

To prevent formula references from changing during copying, we can slightly modify formulas and then copy. You can do as follows:

1. Select the range in which you will copy the formulas, and select Range H1:H6 in our case, and then click Home > Find & Select > Replace. See below screen shot:

Note: You can also open the Find and Replace dialog box with pressing the Ctrl + H keys simultaneously.

2. In the opening Find and Replace dialog box, type = into Find what box, type a space into Replace with box, and then click the Replace All button.
doc copy formulas between books 3
Now a Microsoft Excel dialog box comes out and tells how many replacements it has done. Just click the OK button to close it. And close the Find and Replace dialog box.

3. Keep selecting the range, copy them and paste into the destination workbook.

4. Keep selecting the pasted formulas, and open the Find and Replace dialog box with clicking Home > Find & Select > Replace.

5. In the Find and Replace dialog box, type a space into the Find what box, type = into Replace with box, and then click the Replace All button.

6. Close popping up Microsoft Excel dialog box and Find and Replace dialog box. Now you will see all formulas in original workbook are copied exactly into the destination workbook. See below screen shots:

Notes:
(1) This method requires opening both workbook with formulas you will copy from and destination workbook you will paste to.
(2) This methods will modify the formulas in original workbook. You can restore the formulas in original workbook with selecting them and repeating the Step 6 and Step 7 above.


arrow blue right bubble Copy formulas from one workbook to another without link by changing formulas to text

Actually, we can quickly convert formula to text by Kutools for Excel's Convert Formula to Text feature with only one click. And then copy the text of formula to another workbook, and at last convert the text of formula to real formula by Kutools for Excel's Convert Text to Formula feature.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the formula cells you will copy, and click Kutools > Content > Convert Formula to Text. See screenshot below:

2. Now the selected formulas are converted to text. Copy them and then paste into the destination workbook.

3. Keep selecting the pasted text, and then click Kutools > Content > Convert Text to Formula to convert the pasted text to formula.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


arrow blue right bubbleCopy formulas from one workbook to another without link by Exact Copy

This method will introduce the Exact Copy utility of Kutools for Excel, which can help you exactly copy multiple formulas to a new workbook with ease.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the range in which you will copy the formulas. In our case, we select the Range H1:H6, and then click the Kutools > Exact Copy. See screen shot:

2. In the first opening Exact Formula Copy dialog box, click the OK button.

3. Now the second Exact Formula Copy dialog box opens, go to the destination workbook and select a blank cell, and click the OK button. See screenshot above.

Notes:
(1) If you can’t switch to the destination workbook, please enter the destination address such as [Book1]Sheet1!$H$2 into the above dialog box. (Book1 is destination workbook name, Sheet1 is destination worksheet name, $H$2 is the destination cell);
(2) If you have installed Office Tab (Have a free trial), you can switch to the destination workbook easily with clicking the tab.
(3) This method requires opening both workbook with formulas you will copy from and destination workbook you will paste to.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


arrow blue right bubbleCopy formulas from one workbook to another without link by Auto Text

Sometimes, you may just want to copy and save a complex formula in current workbook, and paste it to other workbooks in future. Kutools for Excel’s Auto Text utility enables you to copy a formula as auto text entry, and let you reuse it in other workbooks with only one clicking easily.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click the cell in which you will copy the formula, and then select the formula in the formula bar. See below screen shot:
doc copy formulas between books 10

2. In the Navigation Pane, click  in the far left of Navigation pane to switch to Auto Text pane, click to select the Formulas group, and then click the Add button  at the top. See below screen shot:
       

3. In the opening New Auto Text dialog box, type a name for the auto text entry of formula, and click the Add button. See screenshot above.

4. Open or switch to the destination workbook, select a cell, and then click the auto text of formula, the formula will be pasted to the select cell at once.

The auto text of formula can be reused at any time in any workbook with only one clicking.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Demo: copy formulas from one workbook to another without link

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


Simultaneously copy and paste column width and row height only between ranges/sheets in Excel

If you have set custom row height and column width for a range, how could you quickly apply this range's row height and column width to other ranges/sheets in Excel? Kutools for Excel's Copy Ranges utility can help you get it done easily! Full Feature Free Trial 60-day!
ad copy multiple ranges 2



Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Shada · 7 months ago
    For method one don't replace with a space as lots of formulas use spaces. Replace with a character that is not used in formulas. I find _ works quite well. Also check the number of replacements made in the first workbook and ensure it is the same in the second.
  • To post as a guest, your comment is unpublished.
    Lukas Vrabec · 1 years ago
    Ctrl + ~ - shows formulas
    Copy to notepad
    Copy to target
    Voaila
  • To post as a guest, your comment is unpublished.
    seshagiri · 1 years ago
    Thank you sooo much
  • To post as a guest, your comment is unpublished.
    Femi · 1 years ago
    Just go to Data -- Edit Links ---- and change source. Change the source to the current workbook where you want the formulas to point to. This works like magic.
  • To post as a guest, your comment is unpublished.
    Koen · 1 years ago
    Kiss: "Keep It Simple & Stupid"


    This saved me so much time!