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.

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

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

Read More: How to Find Links in Excel ( 8 Suitable Approaches)

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.

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.

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.

Read more: How to Remove External Links in Excel (8 Easy Methods)

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.

📌 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 will be found under the Refers To tab.

Read More: How to Find Value in Range in Excel (3 Quick Methods)

Similar Readings:

• Find Broken Links in Excel (4 Quick Methods)
• How to Create Dynamic Hyperlink in Excel (3 Quick Ways)
• Use FIND Function in Excel (7 Suitable Examples)
• How to Remove Hyperlink for Entire Column in Excel (5 Ways)

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.

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.

Read More: How to Edit Links in Excel (3 Suitable Methods)

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.

📌 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 that has been used to embed the pivot table in the current worksheet.

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

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

Read More: VBA to Add Hyperlink to Cell Value in Excel (4 Criteria)

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.

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.

Related Articles 

• How to Combine Text And Hyperlink in Excel Cell (2 Methods)
• Find Duplicates in Two Columns in Excel (6 Suitable Approaches)
• Excel Search for Text in Range (11 Quick Methods)
• How to Find Max Value in Range with Excel Formula (5 Easy Methods)
• [Fixed!] Hyperlinks in Excel Not Working After Saving (5 Solutions)
• How to Hyperlink Multiple PDF Files in Excel (3 Methods)
• Remove Hyperlink Permanently in Excel (4 Ways)
• How to Create a Hyperlink in Excel (5 Easy Ways)