Nehad Ulfat
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.
📌 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.
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 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.
📌 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.
Similar Readings:
- Find Broken Links in Excel (4 Quick Methods)
- How to Hyperlink to Cell in Excel (2 Simple Methods)
- How to Use FIND Function in Excel (7 Suitable Examples)
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.
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.
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.
