How to Break Links in Excel (3 Quick Methods)

Sometimes we need to Break Links in Excel to make the dataset more efficient. Break Links means to end the connection of the external references. It helps us to avoid important data loss as well as minimize the unnecessary errors that occur during calculation. In this article, we are going to learn about some quick methods to Break Links in Excel with some examples and explanations.

Practice Workbook

Download the following workbook and exercise.

3 Suitable Methods to Break Links in Excel

1. Break Links by Copying and Paste as Values in Excel

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 the sheet named Dataset. We can break these links by copying the data and pasting them as values.

Let’s follow the below steps.

STEPS:

• First, select the cell range D5:D9.
• Next, right-click on the mouse.
• Furthermore, select the option Copy from the Context Menu.

• Now, again select the cell range D5:D9.
• Right-click on the mouse as well to open the Context Menu again.
• Then click on the Paste Special option.

• Consequently, it will open a Paste Special window. Select Values from the Paste group.
• After that, click OK.

• Finally, we can see that the links are broken and it’s showing only the values of the cell.

Read More: Find Broken Links in Excel

2. Use Excel Edit Links Feature to Break External Links

Let’s imagine, we have a dataset (Workbook) of a company with their employees’ information on working projects and total working hours. We shall copy this information to a new workbook and link between them like the dataset below. We are going to break these links.

STEPS:

• In the beginning, go to the Data tab.
• Then select the option Edit Links from the Queries & Connections section.
• An Edit Links dialogue box will pop up.
• Now select the required source (Workbook).
• Next click on the Break Link tab.

• Consequently, a warning message will appear here. Click on the Break Links option.

• In the end, we’ll see that all the links are not there. It’s showing the values only.

3. External Links Breaking with Excel VBA

VBA (Visual Basic for Application) is one of the most popular programming languages for Microsoft Excel. We can break any external links with this. To demonstrate this method, we are using the same dataset as Method 2 (See screenshot).

STEPS:

• Firstly, go to the Data tab.
• After that, select the Visual Basic option from the Code section.

• It will open a VBA window. We can open it by using the keyboard shortcut ‘Alt + F11’.
• Next, go to Insert > Module.

• Or, you may click on the sheet bar and select View Code from there to open the VBA Module.

• Then in the Module window, write down 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

• Now click on the Run Sub/UserForm option or we can simply press the F5 key to run the code.

• Finally, we can see that the links are gone from the whole 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. It says that the links are not updated. To solve this problem, simply follow the below steps.

STEPS:

• Click on the Enable Content option at first.

• Now click on the Update option displayed in the following window.

• In the end, we see that the new information is updated from the linked workbook.

Search Cells with External Links to Break in Excel

Let’s say that we have a dataset like the above methods and are linked with external files. Now we are going to look for them and break the links. The Excel Find & Replace tool is going to help us in this search.

STEPS:

• First, select the ‘Find & Select’ drop-down from the Home tab.
• Secondly, click on the Find option.

• This will open a Find and Replace window.
• Furthermore, in the Find tab, write down ‘.xl’ in Find what blank box.
• Now, select Workbook from the Within drop-down.
• Next, from the Look in drop-down, select Formulas.
• After that, click on the Find All button.
• Finally, we see 5 cells that contain the external links. To break these links, follow any of the above procedures.

Break Link Option Is Not Working in Excel

Sometimes, we can see that the Break Link option is grayed out. It happens if the excel sheet is anyhow protected with a password. Let’s follow the below procedure to make the Break Link option available.

STEPS:

• In the beginning, go to the Review tab from the ribbon.
• After that, select the ‘Unprotect Sheet’ option from the Protect group.

• Now, enter the Password in the Unprotect Sheet dialogue box.
• Next, press OK.

• That’s it! Finally, we can see that the Break Link option is available to use again.

Things to Remember

We have to remember some points in the case of breaking links.

• We cannot undo the procedure after breaking links.
• Sometimes having external links is harmful to Excel.

Conclusion

By using these methods, we can quickly break links in Excel. There is a practice workbook that we added. Go ahead and give it a try. Visit the ExcelDemy website for more articles like this. Feel free to ask anything or suggest any new methods.

Related Articles

• Why Do My Excel Links Keep Breaking?
• Break Links in Excel and Keep Values
• Break Links in Excel Before Opening File
• Break Links in Excel When Source Is Not Found
• [Fixed!] Break Links Not Working in Excel

<< Go Back To Linking in Excel | Learn Excel