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.

Break Links by Copying and Paste as Values in Excel

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.

Break Links by Copying and Paste as Values in Excel

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

Break Links by Copying and Paste as Values in Excel

  • 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 (4 Quick Methods)


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.

Use Excel Edit Links Feature to Break External 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.

Use Excel Edit Links Feature to Break External Links

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

Read More: How to Edit Hyperlink in Excel (5 Quick & Easy Ways)


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

External Links Breaking with Excel VBA

STEPS:

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

External Links Breaking with Excel VBA

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

External Links Breaking with Excel VBA

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

External Links Breaking with Excel VBA

  • 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: Find External Links in Excel (6 Quick Methods)


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.

Enable External Links to Break Links in Excel

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

Search Cells with External Links to Break in Excel

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.

Break Link Option Is Not Working in Excel

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.

Read More: [Fixed!] Break Links Not Working in Excel (7 Solutions)


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

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo