How to Find External Links in Excel: 6 Quick Methods

Method 1 – Use Find Command to Search External Links Used in Formulas

Steps:

➤ Press CTRL+F to open the Find and Replace dialog box.

➤ In the Find what box, type “.xl”.

➤ Click Options.

➤ Choose Workbook for Within options.

➤ For Search and Look in options, select By Rows and Formulas.

➤ Press Find All.

In the following picture, you’ll find an additional tab with the external links and corresponding location names.

Method 2 – Use the Edit Links Command to Find and Remove External Links in Excel

Step 1:

➤ Go to the Data tab.

➤ Select the Edit Links option from the Queries & Connections group of commands.

A dialog box named Edit Links will open up.

You’ll find the external link present in the workbook here. Now let’s remove the link.

Step 2:

➤ Click the Break Link option.

And the link will disappear at once. Now let’s go to the Excel spreadsheet.

Enable editing in Cell C6, and you’ll find no formula or external link there. The external link used here before has turned into a numeric value after the link has been removed.

Method 3 – Use Name Manager to Find Named Range with External Links

Steps:

➤ Go to the Formulas tab first.

➤ Select Name Manager from the Defined Names group of commands.

In the Name Manager dialog box, you’ll notice the external links present in the workbook. The reference address of the named range is under the Refers To tab.

Method 4 – Find External Links in Series Chart in Excel

Our dataset may contain a series of charts that are linked to the external workbooks. It’s easier to look for an external link in the chart.

Put your mouse cursor on the data or series bar in the chart and you’ll see the external link in the Formula Box.

Method 5 – Find External Links in Pivot Table in Excel

Steps:

➤ Go to the PivotTable Analyze tab.

➤ Select the Change Data Source option, and a dialog box will appear.

In the Table/Range box, you’ll find the external link to embed the pivot table in the current worksheet.

Method 6 – Use VBA Codes to Find External Links in Excel

Steps:

➤ Right-click your mouse on the Sheet name.

➤ Select View Codes to open the VBA window.

➤ Paste the following codes in the VBA module:

Option Explicit
Sub Find_External__Links()
    Dim i as Integer
    Dim links As Variant
    links = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(links) Then
        Sheets.Add
        For i = 1 To UBound(links)
            Cells(i, 1).Value = links(i)
        Next i
    Else
        MsgBox "External links not found.", vbInformation, "Find Links"
    End If
End Sub

➤ Press F5 and you’ll notice the list of external links present in the current workbook in a new worksheet.

Enable External Links While Opening Excel Workbook

When you have to open a workbook that contains external links then you’ll find the following message box. What you need to do is click on the Update option, and the workbook will activate the external links within seconds.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Find External Links.xlsx

<< Go Back To Find Links in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub CountAndListLinks() Dim wb As Workbook Dim linkCount As Integer Dim linkCell As Range Set wb = Application.ActiveWorkbook linkCount = 0 If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then For Each linkCell In wb.Sheets(1).UsedRange.SpecialCells(xlCellTypeFormulas) If InStr(1, linkCell.Formula, "[") > 0 Then linkCount = linkCount + 1 Debug.Print "Cell " & linkCell.Address & " contains a link." End If Next linkCell MsgBox "Total links found: " & linkCount Else MsgBox "No links found." End If End Sub

2 Comments

Anupam Feb 5, 2024 at 2:06 PM

Referring to your VBA code:- Option Explicit
Sub Find_External__Links()
Dim i as Integer
Dim links As Variant
links = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(links) Then
Sheets.Add
For i = 1 To UBound(links)
Cells(i, 1).Value = links(i)
Next i
Else
MsgBox “External links not found.”, vbInformation, “Find Links”
End If
End Sub
But how to know in which cell the link is present?
- Reply
  Lutfor Rahman Shimanto Feb 6, 2024 at 5:49 PM
  Hello ANUPAM
  
  Thanks for reaching out and sharing your queries. You not only want to count total links but also to display cells containing links.
  
  I am delighted to inform you that I have an Excel VBA sub-procedure that will fulfil your goal. The code will display the total links in a MsgBox and show all the cells containing links in the Immediate window.
  
  OUTPUT OVERVIEW:
  
  Excel VBA Sub-procedure:
```
Sub CountAndListLinks()
    Dim wb As Workbook
    Dim linkCount As Integer
    Dim linkCell As Range
    
    Set wb = Application.ActiveWorkbook
    linkCount = 0
    
    If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
        For Each linkCell In wb.Sheets(1).UsedRange.SpecialCells(xlCellTypeFormulas)
            If InStr(1, linkCell.Formula, "[") > 0 Then
                linkCount = linkCount + 1
                Debug.Print "Cell " & linkCell.Address & " contains a link."
            End If
        Next linkCell
        MsgBox "Total links found: " & linkCount
    Else
        MsgBox "No links found."
    End If
End Sub
```
  Hopefully, the code will help you; good luck.
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy

How to Find External Links in Excel: 6 Quick Methods

Method 1 – Use Find Command to Search External Links Used in Formulas

Method 2 – Use the Edit Links Command to Find and Remove External Links in Excel

Method 3 – Use Name Manager to Find Named Range with External Links

Method 4 – Find External Links in Series Chart in Excel

Method 5 – Find External Links in Pivot Table in Excel

Method 6 – Use VBA Codes to Find External Links in Excel

Enable External Links While Opening Excel Workbook

Leave a reply Cancel reply