How to Break Links in Excel and Keep Values (4 Easy Ways)

When working with Excel workbooks that contain external links to other files, sometimes you need to break those links to make the workbook independent. Breaking links removes the connection to the source file but can cause the linked data to disappear or become formulas referencing unavailable files. To avoid this, it’s important to break links while keeping the current values intact.

This article walks you through several reliable methods to break links in Excel and retain the existing values. Whether you want to do it manually, with built-in tools, or via VBA, we’ve got you covered.

This is the most straightforward method and ideal when you’re working with a smaller dataset or don’t want to go deep into Excel’s link management tools. It involves simply copying and pasting values over formulas, ensuring you preserve the existing numbers and text while removing external references entirely.

Steps:

➤ Open your Excel workbook containing linked formulas (e.g., formulas like =[SourceWorkbook.xlsx]Sheet1!A1).

➤ Select the entire range with linked formulas or press  Ctrl  +  A  to select all cells.
➤ Press  Ctrl  +  C  to copy.

➤ Right-click the selection and go to a cell like A8 where you want your output to appear, then choose Paste Special >> Values.

➤ This replaces all formulas with their current calculated values, effectively breaking the links.

➤ Your data will be visible without any previous formatting. In this case, you can format again manually or you can copy the original data range and select the new range.

➤ Then right-click and choose to paste Formatting under Paste Options which will transfer the formatting to your new range.

Now, connection with the source workbook has been removed.

If you’re dealing with large spreadsheets or want to selectively break specific links, the Find and Replace method is perfect. It allows you to locate only the cells referencing a certain workbook, then replace them with static values.

Steps:

➤ Press  Ctrl  +  F  to open the Find dialog.
➤ Click Options >> Within Workbook.
➤ In the Find what field, enter the workbook name or part of the link, e.g., [SourceWorkbook.xlsx].
➤ Click Find All to display all cells containing the link.

➤ Press  Ctrl  +  A  in the results to select all.
➤ Close the Find dialog, then press  Ctrl  +  C  to copy all found cells.

➤ Right-click and choose Paste Special >> Values to replace formulas with their values.

➤ Your data will be visible without any previous formatting. In this case, you can format again manually or you can copy the original data range and select the new range.

➤Then right-click and choose to paste Formatting under Paste Options which will transfer the formatting to your new range.

Now you have broken connection with the source workbook.

Excel’s built-in Edit Links tool is designed specifically for managing and removing external connections. This method is best when you need a centralized view of all linked sources, making it easy to break or update multiple links in one go.

Steps:

➤ Go to the Data tab on the ribbon.
➤ Click on Edit Links under the Connections group.
➤ Select links you want to break and click Break Link.

➤ You can also do this by clicking Workbook Links in the “Queries & Connections” group.

➤ The dialog box will list all external links.
➤ Select the link(s) you want to break and click on three dot option.
➤ Click Break Links.

➤ Excel will warn you this action cannot be undone. Confirm by clicking Break Links.

➤ The linked formulas will convert to their current values, keeping the data visible but removing the connection.

If you’re managing complex workbooks or need to break links across multiple sheets or files at once, using a simple VBA macro can save you a ton of time. This automated approach ensures all links are removed programmatically while preserving the existing values.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Click on  Insert >> Module.
➤ Paste this code into the blank box that appears:

Sub BreakLinksKeepValues()
    Dim Links As Variant
    Dim i As Integer
    Links = ThisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If Not IsEmpty(Links) Then
        For i = LBound(Links) To UBound(Links)
            ThisWorkbook.BreakLink Name:=Links(i), Type:=xlLinkTypeExcelLinks
        Next i
        MsgBox "All links broken and values kept.", vbInformation
    Else
        MsgBox "No external links found.", vbInformation
    End If
End Sub

➤ Press  F5  or run the macro from Run >> Run Sub/Userform.

➤ Click OK on the system message that pops up.

The macro breaks all external workbook links and keeps their last calculated values intact.

Frequently Asked Questions

Will breaking links remove all formulas?

Breaking links will only convert formulas that reference external workbooks into static values. Other internal formulas referencing the same workbook will remain functional and unchanged. Always review before breaking links.

Can I undo breaking links?

Unfortunately, breaking links in Excel is a permanent action. Once links are removed and replaced with values, you cannot revert the change. It’s strongly recommended to save a backup copy beforehand.

Why do links sometimes still appear after breaking?

Even after breaking links, Excel may retain connections in hidden areas like named ranges, chart sources, shapes, or conditional formatting. Use tools like Find Links or inspect each object manually.

Does breaking links affect pivot tables?

Yes, if your PivotTables pull data from an external workbook or data model, breaking links might disconnect or freeze the dataset. You may need to refresh data or rebuild the connection.

Wrapping Up

In this tutorial, we learned how to break links in Excel while keeping the values intact. From simple copy-paste techniques to using the Edit Links dialog and even automating the process with VBA, you now have multiple ways to remove external workbook connections safely. These methods help you clean up your files, avoid broken references, and maintain accurate data. Feel free to download the practice file and share your feedback.