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

In Excel, you may sometimes need to move or copy formulas from one workbook to another without having the pasted formulas link back to the original workbook. This commonly occurs when consolidating data, sharing models with others, or reusing formula logic in separate files. However, by default, copying and pasting formulas between workbooks can lead to unwanted external references, which might cause errors, slow down calculations, or break links when the source workbook is unavailable. Fortunately, there are multiple practical ways to accurately copy formulas between workbooks without carrying over those links. Below you’ll find step-by-step guides for different scenarios and user preferences, including both built-in Excel features and useful tools to automate or simplify the process.

Copy formulas from one workbook to another without link by changing formulas

To copy formulas without creating unwanted links to the original workbook, you can temporarily modify the formulas before copying and then restore them after pasting. This method is especially helpful when you only occasionally need to transfer formulas and want to use Excel's built-in features.

1. Select the range containing the formulas you want to copy. For example, select the range H1:H6. Then, go to the Home tab, click Find & Select, and choose Replace.

Tip: You can quickly open the Find and Replace dialog box by pressing Ctrl + H simultaneously.

2. In the Find and Replace dialog, enter an equal sign “=” in the Find what field and a space character in the Replace with field. Click Replace All.

Excel will display a dialog box showing how many replacements were made. Click OK, then close the Find and Replace window.

3. With the range still selected, copy (Ctrl+C) and then paste (Ctrl+V) it into your target workbook.

4. In the destination workbook, select the pasted range. Open the Find and Replace dialog again by clicking Home > Find & Select > Replace, or use Ctrl + H.

5. In the Find and Replace dialog, enter a single space in Find what and the equal sign “=” in Replace with. Click Replace All.

6. After replacing, close the pop-up message and dialog window. The pasted cells will now contain the same formulas as the original, without external links.

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!

Get a 30-day full-featured free trial now!

Copy formulas from one workbook to another without link by changing formulas to text

If you prefer a more streamlined, risk-free approach, Kutools for Excel offers a built-in feature to convert formulas to text and back again. This saves time and frustration, particularly with complex formulas or large batches that could easily be disrupted by manual text replacement. By converting formulas to text, you ensure that Excel will not turn them into links when pasting into a new workbook; you can later restore them as formulas in just a click.

1. Select the range containing the formulas to copy, then click Kutools > Content > Convert Formula to Text.

2. The selected formulas will instantly be displayed as text. Copy these cells, switch to the destination workbook, and paste the results into the desired range.

3. While the pasted cells are still selected in the destination workbook, go back to Kutools > Content > Convert Text to Formula. The cells now become live formulas again, free from external workbook links.

Copy formulas from one workbook to another without link by Exact Copy

Another efficient option is to utilize Kutools for Excel’s Exact Copy feature. This tool is particularly valuable when you want to replicate and transfer multiple formulas exactly, including formats, to a new workbook—without risking links or reference transformation. This approach helps when handling sensitive or complex spreadsheets where data integrity is important.

1. Select the range that contains the formulas you wish to copy—e.g., H1:H6—then click Kutools > Exact Copy.

2. In the first Exact Formula Copy dialog, click OK to proceed.

3. A second dialog appears for you to specify the location in the target workbook. Switch to that workbook, click into a destination cell, and press OK. The formulas and formats are pasted flawlessly, without external references.

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

When you frequently reuse the same formulas in different workbooks or want to preserve important formulas as personal shortcuts, the Auto Text feature in Kutools for Excel is especially handy. This solution allows you to store any formula as an auto text entry and reuse it instantly across Excel sessions and workbooks, streamlining repetitive tasks and reducing the risk of copy-paste errors.

1. Click the cell that contains the formula to reuse, then highlight the formula in the formula bar.

2. On the far left of the Excel Navigation pane, click to activate the Auto Text pane. Move to the Formulas group and click Add at the top to save the selection as an auto text.
       

3. In the New Auto Text dialog, assign a descriptive name and confirm by clicking Add.

4. Next time you want to use that formula in a different workbook, simply open or switch to the desired workbook, select the target cell, and click the auto text entry. The formula is inserted immediately as a working formula, not as a link or a static value.

Copy formulas from one workbook to another without link using a VBA Macro

If you often need to transfer formulas between workbooks—especially in bulk or as part of an automated workflow—this VBA macro copies formulas by temporarily converting “=” to a placeholder to avoid external links, writes them to the destination, then restores them as live formulas. It does not modify your source cells and does not require switching workbooks while a dialog is open.

1. Open both the source and destination workbooks. Press Alt + F11, click Insert > Module, and paste the code below:

Sub CopyFormulas_NoLinks_NoSwitch()
    Dim src As Range
    Dim rowsCnt As Long, colsCnt As Long
    Dim buf() As Variant
    Dim r As Long, c As Long
    Dim s As String, placeholder As String
    Dim wbName As String, shName As String, addr As String
    Dim tgtTL As Range, tgt As Range
    Dim oldCalc As XlCalculation

    placeholder = "#_EQUAL_#"

    On Error GoTo ExitHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    oldCalc = Application.Calculation
    Application.Calculation = xlCalculationManual

    ' 1) Pick contiguous source range (any workbook)
    Set src = Application.InputBox( _
        Prompt:="Select the source range that contains formulas:", _
        Title:="Copy formulas without links", Type:=8)
    If src Is Nothing Then GoTo ExitHandler
    If src.Areas.Count > 1 Then
        MsgBox "Please select a single contiguous range.", vbExclamation
        GoTo ExitHandler
    End If

    rowsCnt = src.Rows.Count
    colsCnt = src.Columns.Count

    ' 2) Get destination info WITHOUT switching windows during a modal dialog
    wbName = Application.InputBox( _
        Prompt:="Enter DESTINATION workbook name (as shown in title bar, e.g., Book2.xlsx):", _
        Title:="Destination workbook", Type:=2)
    If wbName = "" Then GoTo ExitHandler

    shName = Application.InputBox( _
        Prompt:="Enter DESTINATION sheet name (e.g., Sheet1):", _
        Title:="Destination sheet", Type:=2)
    If shName = "" Then GoTo ExitHandler

    addr = Application.InputBox( _
        Prompt:="Enter top-left DESTINATION cell address (e.g., A1):", _
        Title:="Destination top-left cell", Type:=2)
    If addr = "" Then GoTo ExitHandler

    ' 3) Resolve destination references
    Dim wb As Workbook, ws As Worksheet
    On Error Resume Next
    Set wb = Application.Workbooks(wbName)
    On Error GoTo ExitHandler
    If wb Is Nothing Then
        MsgBox "Workbook '" & wbName & "' is not open.", vbExclamation
        GoTo ExitHandler
    End If

    On Error Resume Next
    Set ws = wb.Worksheets(shName)
    On Error GoTo ExitHandler
    If ws Is Nothing Then
        MsgBox "Worksheet '" & shName & "' not found in '" & wbName & "'.", vbExclamation
        GoTo ExitHandler
    End If

    On Error Resume Next
    Set tgtTL = ws.Range(addr)
    On Error GoTo ExitHandler
    If tgtTL Is Nothing Then
        MsgBox "Invalid address '" & addr & "'.", vbExclamation
        GoTo ExitHandler
    End If

    Set tgt = tgtTL.Resize(rowsCnt, colsCnt)

    ' 4) Build a text buffer so we never touch the source cells or use the clipboard
    ReDim buf(1 To rowsCnt, 1 To colsCnt)
    For r = 1 To rowsCnt
        For c = 1 To colsCnt
            If src.Cells(r, c).HasFormula Then
                s = src.Cells(r, c).Formula
                ' Replace only the leading "=" for speed/safety
                If Left$(s, 1) = "=" Then s = placeholder & Mid$(s, 2)
                buf(r, c) = s
            Else
                buf(r, c) = src.Cells(r, c).Value
            End If
        Next c
    Next r
    tgt.Value = buf

    ' 5) Restore placeholders to live formulas in destination
    For r = 1 To rowsCnt
        For c = 1 To colsCnt
            If VarType(tgt.Cells(r, c).Value) = vbString Then
                s = CStr(tgt.Cells(r, c).Value)
                If Left$(s, Len(placeholder)) = placeholder Then
                    s = "=" & Mid$(s, Len(placeholder) + 1)
                    tgt.Cells(r, c).Formula = s
                End If
            End If
        Next c
    Next r

    MsgBox "Formulas copied and restored successfully (no external links).", vbInformation
ExitHandler:
    On Error Resume Next
    Application.Calculation = oldCalc
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

2. Press Alt + F8, run CopyFormulas_NoLinks_NoSwitch. Select the source range. Then simply type the destination workbook name, sheet name, and top-left cell address—no need to switch windows during the prompts. The macro writes placeholder text into the destination and restores it to live formulas (no links).

Demo: copy formulas from one workbook to another without link

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 Toolsets:  12 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

Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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...

More details of Kutools for Excel... Free Download...

---

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!

More details of Office Tab... Free Download...

---

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

• All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
• One installer, one license — set up in minutes (MSI-ready)
• Works better together — streamlined productivity across Office apps
• 30-day full-featured trial — no registration, no credit card
• Best value — save vs buying individual add-in

More details of Kutools for Office... Free Download...