How to Find External Links in Excel (6 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working with Microsoft Excel, it’s a common scenario to look for external links and references in the active workbook. In this article, you’ll get to know all the simple and easy techniques to find external links with suitable examples and proper illustrations.


How to Find External Links in Excel: 6 Suitable Methods

1. Use Find Command to Search External Links Used in Formulas

In the following picture, there lies some sales data over three months for some random salesmen. We’ll find out if any sales data contains an external link or reference.

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

➤ Press Find All.

Use Find Command to Search External Links Used in Formulas

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

Use Find Command to Search External Links Used in Formulas


2. Use Edit Links Command to Find and Remove External Links in Excel

We can also use the Edit Links command to look for external links. With this method, we can easily remove the external links too as the links will be turned into values only.

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

Use Edit Links Command to Find and Remove External Links in Excel

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

📌 Step 2:

➤ Click on the Break Link option.

Use Edit Links Command to Find and Remove External Links in Excel

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

Use Edit Links Command to Find and Remove External Links in Excel

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.

Use Edit Links Command to Find and Remove External Links in Excel


3. Use Name Manager to Find Named Range with External Links

Sometimes our dataset may contain a Named Range that is linked to an external workbook. By using the Name Manager, we can easily find the named range present in the workbook.

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.

Use Name Manager to Find Named Range with External Links

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

Use Name Manager to Find Named Range with External Links

 


4. Find External Links in Series Chart in Excel

In Excel, our dataset may contain series charts that are linked to the external workbooks. It’s quite easier to look for an external link in the chart.

Find External Links in Series Chart in Excel

What you have to do is simply put your mouse cursor on the data or series bar in the chart and you’ll see the external link in the Formula Box.

Find External Links in Series Chart in Excel


5. Find External Links in Pivot Table in Excel

Now we’ll find out if a pivot table in our workbook contains an external link.

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.

Find External Links in Pivot Table in Excel

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

Find External Links in Pivot Table in Excel


6. Use VBA Codes to Find External Links in Excel

In our final method, we’ll apply the VBA codes to look for external links and references in the workbook.

📌 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

Use VBA Codes to Find External Links in Excel

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

Use VBA Codes to Find External Links in Excel


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 simply click on the Update option and the workbook will activate the external links within seconds.

Enable External Links While Opening Excel Workbook


Download Practice Workbook

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


Concluding Words

I hope all of these methods mentioned above will help you to apply them in your Excel spreadsheets when you have to find external links and references in the active workbook. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

2 Comments
  1. 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
      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo