How to Break Links in Excel (3 Quick Methods)

In this article, we will present some quick methods to Break Links in Excel. To Break Links means to end the connection to the external references in our workbook, helping to avoid important data loss and minimize avoidable errors during calculation.

Download Practice Workbook

Break Links.xlsm

3 Suitable Methods to Break Links in Excel

Method 1 – Breaking Links by Copying and Pasting as Values

Suppose we have a dataset of some company employees with their assigned Project names & working Hours. The column containing the data of their working Hours is linked with a sheet named Dataset. We can break these links by copying the data and pasting them as values.

Steps:

Select the cell range D5:D9.
Right-click on the mouse.
Select the Copy option from the Context Menu.

Select the cell range D5:D9.
Right-click on the mouse to open the Context Menu again.
Click on the Paste Special option.

In the Paste Special window that opens, select Values from the Paste group.
Click OK.

The links are broken, and only the values of the cells are showing.

Read More: Find Broken Links in Excel

Method 2 – Using Excel’s Edit Links Feature to Break External Links

Suppose we have a dataset (Workbook) of a company with its employees’ information on working projects and total working hours. We’ve been through the process to copy this information to a new workbook and link between them like in the dataset below. Now we’ll break these links.

Steps:

Go to the Data tab.
Select the option Edit Links from the Queries & Connections section.

An Edit Links dialog box will pop up.

Select the required source (Workbook).
Click on the Break Link option.

A warning message will appear.

Click on the Break Links option.

The links are no longer there, only the values.

Method 3 – Breaking External Links with Excel VBA

VBA (Visual Basic for Application) can be used to break any external links. To demonstrate this method, we’ll use the same dataset as Method 2 (see the screenshot).

Steps:

Go to the Data tab.
Select the Visual Basic option from the Code section.

This will open a VBA window. We can also open it by using the keyboard shortcut ‘Alt + F11’.

Go to Insert > Module.

Or, click on the sheet bar and select View Code to open the VBA Module.

In the Module window, enter the below code:

Sub ExcelLinksBreak()

Dim wbook As Workbook
Set wbook = Application.ActiveWorkbook
If Not IsEmpty(wbook.LinkSources(xlExcelLinks)) Then
For Each link In wbook.LinkSources(xlExcelLinks)
wbook.BreakLink link, xlLinkTypeExcelLinks
Next link
End If
End Sub

To run the code, click on the Run Sub/UserForm option, or simply press the F5 key.

The links are gone from the entire workbook.

Read More: Excel VBA to Break Links

Enable External Links to Break Links in Excel

If the Excel workbook is connected to any external references, it will show a SECURITY WARNING after opening the file, informing us that the links are not updated. To solve this problem, do as follows:

Steps:

Click on the Enable Content option.

Click on the Update option displayed in the next window.

The new information is updated from the linked workbook.

Search for Cells with External Links to Break

Suppose we have a dataset like the one in the above methods, which contains links to external files. We’ll search for these links and break them using the Find & Replace tool.

Steps:

Select the ‘Find & Select’ drop-down from the Home tab.
Click on the Find option.

The Find and Replace window will open.

In the Find tab, enter ‘.xl’ in the Find what blank box.
Select Workbook from the Within drop-down.
From the Look in drop-down, select Formulas.
Click on the Find All button.

5 cells that contain the external links are returned.

To break these links, follow any of the above procedures.

The Break Link Option Is Not Working in Excel

Sometimes, the Break Link option is grayed out. This happens if the Excel sheet is protected with a password. Let’s re-enable the Break Link option.