How to Remove Unknown Links in Excel (4 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we use the information of one Excel file for other. This way, we link Excel files one to another. Sometimes we see that, there are unknown links are present in the Excel file. In this article, we will discuss how to remove those unknown links in Excel and also the other external link with the proper illustration.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Examples to Remove Unknown Links in Excel

In the sample dataset, we can see that there are two external links Cell D5 and D9. In this article, we will show how to indicate those external links and then remove them.

Sample Dataset to show how to remove unknown links in Excel


1. Remove Unknown Links from Cells

Links may exist in Excel in different forms. Here, we will show how to remove unknown links from cells. We will use the Data feature of Excel to remove links.

📌 Steps:

  • Click on the Data tab first.
  • Choose Edit Links from the Queries & Conditions group.

Availing Data section of Excel

  • The Edit Links window appears. We see the source link is showing here.
  • Click on the Break Link options.

  • A warning dialog will show here. Choose the Break Links option.

  • Again, look at the Edit Links window. Then, click on the Close option.

The attached link has been removed successfully.

  • Now, move the cursor to Cell D5.

We can see no links are showing here.

Read More: How to Remove Hyperlink from Excel (7 Methods)


2. Remove Unknown Links from Shapes

In this section, we have an oval shape. Also, the shape consists of an external link. We will remove this link here.

At first, we will check how many unknown links are present in the sheet.

Showing external link of Excel Shape

📌 Steps:

  • Press the F5 button and the Go To window will appear.
  • Click on the Special button.

  • The Go To Special window appears.
  • Choose the Objects option.
  • Then, press the OK button.

Enter Go To Special section of Excel

  • Now, all the external links will be selected. We can see only one unknown link present in the sheet.
  • Press the right button of the mouse.
  • Choose the Remove Link option from the Context Menu.

Context menu to remove link in Excel

The unknown link has been removed from the shape.

Read More: How to Remove External Links in Excel


3. Remove Unknown Links from Named Range

In this section, we will remove unknown links from Named Range.

We can see that in the dataset, the values of the Salary column are taken from a named range.

📌 Steps:

  • Now, we will check the named range. Click on the Formulas tab.
  • Choose the Name Manager option.

Enter Name Manager of Excel

  • The Name Manager window appears.

Check unknown links of Excel

We can see there is an external source here. If we remove this external source from the named range the cells will be blank or values will remove also.

Read More: How to Remove Hyperlink for Entire Column in Excel (5 Ways)


4. Remove Unknown Links Using VBA

In this section, we will apply the VBA macro to remove unknown links. It also shows the number of unknown links that are removed from the file.

📌 Steps:

  • First, go to the bottom of the Excel Sheet.
  • Press the right button of the mouse.
  • Choose the View Code option from the Context Menu.

Introduce VBA in Excel to remove links

  • We enter the VBA window.
  • Choose Module from the Insert tab.

Enter VBA module in Excel

  • This is the VBA module. We will write VBA code here.

  • Copy and paste the following VBA code on the module.
Sub Remove_Unknown_Links()
Dim Unknown_Links As Variant
Dim N, Remove_Link As Long
Unknown_Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
If IsEmpty(Unknown_Links) = True Then GoTo ReportResults
For N = 1 To UBound(Unknown_Links)
ActiveWorkbook.BreakLink Name:=Unknown_Links(N), Type:=xlLinkTypeExcelLinks
Remove_Link = Remove_Link + 1
Next N
ReportResults:
MsgBox "Unknown Links Removed: " & Remove_Link
End Sub

VBA code to remove unknown links in Excel

  • Press F5 to run the code.

Number of unknown links in Excel file

A dialog box will appear. It presents the number of unknown links in the file.

  • Go to the sheet.

We can see unknown links have been removed.

Code Explanation:

Dim Unknown_Links As Variant
Dim N, Remove_Link As Long

Declare the variables.

Unknown_Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

Set the active workbook as the value of one variable and check whether it consists of any links or not.

If IsEmpty(Unknown_Links) = True Then GoTo ReportResults

If there is no link, then go to the ReportResults section.

For N = 1 To UBound(Unknown_Links)

Check the links to the variable.

ActiveWorkbook.BreakLink Name:=Unknown_Links(N), Type:=xlLinkTypeExcelLinks

Converts the links into values.

Remove_Link = Remove_Link + 1

Remove the link.

Next N

Go to the next link.

MsgBox "Unknown Links Removed: " & Remove_Link

Print a text and the number of links.

Read More: How to Remove Hyperlink Permanently in Excel (4 Ways)


Conclusion

In this article, we described how to remove unknown links from the Excel file. We used a VBA code that indicates the number of links in the file also. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo