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.


Download Practice Workbook

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


6 Suitable Methods to Find External Links in Excel

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

Read more: How to Edit 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


Similar Readings:


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 the 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


Concluding Words

I hope, all of these methods mentioned above will now 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.


You May Also Like to Explore

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo