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 30-day!
ad combine sheets books 1

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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.

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 tools for Excel. Full feature free trial 30-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.

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 tools for Excel. Full feature free trial 30-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.

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 tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Demo: copy formulas from one workbook to another without link

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

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 30-day!
ad copy multiple ranges 2



Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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.
    Thomas CHARLES · 3 months ago
    Hi, they auto text feature doesn't work if there's a named range : it keeps the reference to the old workbook :/
    • To post as a guest, your comment is unpublished.
      kellytte · 3 months ago
      Hi Thomas CHARLES,
      AutoText will not change the formula. However, you can change the named range to reference cells in the formula before saving it as an AutoText entry.
      FYI, Kutools for Excel’s Convert Name to Reference Range feature can help you easily convert all named ranges to corresponding ranges easily.
      • To post as a guest, your comment is unpublished.
        Thomas CHARLES · 3 months ago
        Thank you, but I have several versions of the same document, and I wanted to paste the formula keeping the naming ranges (as they are consistent across the files). Thanks anyway.
  • To post as a guest, your comment is unpublished.
    Shada · 1 years 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 · 2 years ago
    Thank you sooo much
  • To post as a guest, your comment is unpublished.
    Femi · 2 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 · 2 years ago
    Kiss: "Keep It Simple & Stupid"


    This saved me so much time!