Skip to main content

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

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.

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!


ad combine sheets books 1

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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Copy 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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Copy 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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Demo: copy formulas from one workbook to another without link


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download 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!


ad copy multiple ranges 2


Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Bonjour,
est-ce qu'il y a une façon de copier une feuille d'un fichier excel dans un fichier en ligne (google) sans perdre les formules?

merci :)
This comment was minimized by the moderator on the site
Hi Nancy, you can convert the formulas to text first, and then copy them to, say, google sheets.
To convert formulas to text, please see the tutorial: https://www.extendoffice.com/documents/excel/822-excel-convert-formula-to-text-string.html

Amanda
This comment was minimized by the moderator on the site
Hi, they auto text feature doesn't work if there's a named range : it keeps the reference to the old workbook :/
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Ctrl + ~ - shows formulas
Copy to notepad
Copy to target
Voaila
This comment was minimized by the moderator on the site
Thank you sooo much
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
May the Lord bless you
This comment was minimized by the moderator on the site
Kiss: "Keep It Simple & Stupid"


This saved me so much time!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations