Are you getting the break links error when opening your workbook? Learn how to break links in Excel to resolve the issue right away!

Often you work with Excel workbooks containing data reference links to other workbooks on your computer or data sources on a server. In this scenario, if there’s any issue with the referenced workbooks or databases, you get an error message every time you open that workbook.

Because, whenever you try to open a workbook in Excel with external links, Excel tries to update its data by fetching the latest references from the external resources. Since the external references might be broken, you’ll see a Microsoft Excel dialog box to update the reference links.

Here comes the Break Links feature of Excel. It enables you to sever all the external data source links to the workbook and replace the formulas with hard-coded values. Read this guide until the end to learn various ways of breaking Excel links.

What Is the Break Link Error in Excel?

In Excel, a Break Link error typically occurs when there are external links within a workbook that Excel itself cannot update or resolve. These external links are often references to cells or ranges in other workbooks, workbook tables, or online databases. The error arises when the linked workbook is unavailable, moved, or deleted, causing Excel to lose the connection.

When attempting to update or recalculate the workbook, Excel encounters the break link error and notifies you by displaying a pop-up as shown above. This error can disrupt the integrity of formulas and data that rely on external links, leading to inaccuracies or missing information.

What Happens When You Break Lines in Excel

Here’s how you change the entire workbook by breaking all links:

• The value displayed in your cell won’t automatically update anymore when the source changes. It becomes a static snapshot as of the moment you broke the link.
• All the formulas referencing the external data get replaced with the actual value it contained at the time of breaking the link. This can disrupt calculations in complex spreadsheets.
• If the source file becomes unavailable, your linked cells might display error messages like #REF!
• Breaking links can slightly decrease file size and improve performance as Excel no longer needs to manage the connection.
• You lose the ability to easily update your data from the source by simply refreshing the link. You must manually input the data or copy-paste it.
• Once you break links in Excel, the workbook will forget all the reference links to external sources. There won’t be any history of the formulas used before breaking links.

The breaking link action is irreversible. So think again before doing it! Consider copying the relevant data from the source instead if you just need it statically. Alternatively, make a backup copy of your workbook before breaking links in case you need to revert.

Reasons to Break Links in Excel

Find below when you might need to break links in your Excel workbook:

• If you want to make your Excel workbook self-contained and free from external sources, you can break links.
• Breaking links is particularly helpful when you’re sharing an Excel workbook with others. If the sources are on a server or computer that the recipients can access, the workbook will malfunction on their system.
• Third parties receiving your workbooks with external sources can easily access those references and cause harm. Instead, you can break links and then send the workbook.
• In cases where external data sources are prone to change, breaking links ensures that the data at the time of breaking remains unchanged, preventing unintended updates.
• External links can contribute to larger file sizes; breaking links can help optimize file size for better performance.
• Managing numerous external links can be complex. Breaking links simplifies workbook management, especially when collaborating with others.

Now that you’ve developed a fundamental idea for breaking Excel links, let’s find out what methods you can follow to break links.

Break Links on Excel From File Info

1. Attempt to open the Excel workbook in question.
2. Close the dialog that pops up.
3. Click on the File tab on the Excel ribbon menu.

4. Choose the Info option on the left-side navigation panel.
5. Find the Related Documents section on the right-side panel.
6. Below the Related Documents section, click on the Edit Links to Files option.

7. You should now see the Workbook Links panel on the right side of the Excel worksheet.
8. Click Break all to break all of the links in the workbook.

9. If there is more than one external link in the workbook, you should see a list of references below the Workbook Links section.
10. Suppose, you want to keep a link to one workbook but not the other, you can selectively break links.
11. Click on the horizontal three-dot menu to the right of the specific reference and choose Break links.

Break Links on Excel Using the Manage Workbook Links Tool

1. As you try to open an Excel workbook with external data sources, you’ll see the Update dialog box.
2. Click the Update button.

3. Now, look above the Formula bar on the worksheet to find the Manage Workbook Links button.
4. Click on Manage Workbook Links.
5. Now, you can follow the steps mentioned in the previous method to break links.

Break Links on Excel From Queries & Connections

Suppose, there is no external source referencing error in your worksheet. You want to remove connections to external sources manually before sending the workbook to someone.

Here’s what you can do:

1. Open your workbook and go to any worksheet.
2. Go to the Data tab of the Excel ribbon menu.
3. There, you should find the Workbook Links button inside the Queries & Connections commands block. Click on that.

4. The Workbook Links menu will show up on the right side of the workbook.
5. Here, you can click Break all to sever all the external links.
6. For selective link removal, click on the ellipsis icon beside the external workbook or source and click Break links.

Break Links on Excel Using the Paste Special Tool

If you’re unable to use the Break links option on Excel, you can use Paste Special. This tool will let you convert calculated fields on the Excel worksheet to hard-coded values. Paste Special also lets you keep the source cell formatting. Here’s how it’s done:

1. Highlight the cell range that contains broken links or external links you don’t want to share with anyone.
2. Press Ctrl + C to copy the content.

3. Go to the cell where you want to paste on the worksheet and press Ctrl + Alt + V.
4. The Paste Special dialog will show up.
5. Under the Paste section, choose Values and number formats.
6. Click OK to save the copied data without the formulas.

If you check each copied cell you’ll only see the values. There won’t be any formulas linking to external sources.

Stop Break Links Error Using Calculation Options

This method doesn’t break links but allows you to prevent the Break Links error from showing up whenever you open an Excel workbook with broken external data sources.

By default, Excel always calculates the calculated fields when you re-open a workbook. By following these steps you can disable this auto calculation of workbooks:

1. Open the workbook and go to the Formulas tab.
2. Navigate to the Calculation block and click on the Calculation Options drop-down arrow.
3. Select Manual on the context menu.

The next time you or someone else opens this workbook, Excel won’t auto-calculate the formula fields. Hence, the Break Links error won’t show up.

Stop Break Links Error by Disabling Automatic Updates

Another way to prevent the break links error is preventing Automatic Updates from Excel Options. Here are the steps you can follow:

1. Click the File tab on the workbook and choose Options on the left-side navigation panel.

2. The Excel Options dialog will appear.
3. Click the Advanced option on the left-side panel.
4. On the right, scroll down to the General section.
5. Uncheck the checkbox for the Ask to update automatic links option.
6. Click OK to close Excel Options and save the changes.

Break Links on Excel Using Excel VBA

Find below the steps to create an Excel VBA macro to break links in Excel:

1. Bring up the Excel VBA Editor by pressing the Alt + F11 keys.
2. Click on the Insert button and select Module.
3. A blank module will show up on the Excel VBA Editor. There, copy and paste this script:

Sub BreakAllLinks()
    Dim link As Variant
    
    ' Loop through all links in the workbook
    For Each link In ThisWorkbook.LinkSources
        ' Break each link
        ThisWorkbook.BreakLink link, xlLinkTypeExcelLinks
    Next link
    
    ' Display a message box indicating that links have been broken
    MsgBox "All links have been successfully broken.", vbInformation
End Sub

4. Click the Save button.

5. Click No on the dialog box that shows up.
6. On the Save As dialog, click the Save as type drop-down.
7. Choose the Excel Macro-Enabled Workbook (XLSM) option.
8. Click Save to close the dialog and save the Excel VBA macro.

To run the macro, follow these steps:

1. Press Alt + F8.
2. The Macro dialog will show up.
3. Select the BreakAllLinks macro in the menu.
4. Click Run to execute the VBA script.

The above script will remove all external workbooks and online database references. You won’t be able to undo this step. So, do it after backing up the original workbook.

Suppose, you don’t want to delete all the external references in the workbook. You only want to delete links to a few specific links. In this scenario, you can use the following VBA script:

Sub BreakSelectiveLinks()
    Dim link As Variant
    Dim response As VbMsgBoxResult
    
    ' Loop through all links in the workbook
    For Each link In ThisWorkbook.LinkSources
        ' Ask the user if they want to break each link
        response = MsgBox("Do you want to break the link to " & link & "?", vbYesNo + vbQuestion, "Break Link")
        
        ' Check the user's response
        If response = vbYes Then
            ' Break the link
            ThisWorkbook.BreakLink link, xlLinkTypeExcelLinks
            MsgBox "Link to " & link & " has been successfully broken.", vbInformation
        End If
    Next link
End Sub

When you execute the above script, a dialog will show up. The message box will cycle all the discovered links in the workbook. If you click Yes, Excel will delete that external reference link.

Conclusions

These are the proven and easy ways to break links in Excel. Give any of the above methods a try that suits your working style on Excel.

As you navigate the various techniques outlined, always remember to exercise caution, back up your data, and carefully evaluate the consequences of breaking links. With these tools at your disposal, you’ll be well-equipped to streamline your Excel experience and ensure the integrity and organization of your data.

Comment below if the article helped you in your Excel learning journey.