Definitely, maintaining a record of all external references used in a workbook is exigent. You cannot find external links in a spreadsheet because it is a purely unwieldy thing. With Microsoft, you will not find a built-in function that can help you answer how to find external links in Excel. Apart from that, we still have managed to find some useful ways that will help you in finding external references in a spreadsheet.
What Are External Links of References?
Before digging into the main query on how to find external links in Excel, let’s have a brief overview of what an external link is.
While creating formulas in Excel, you tend to refer them to a data point in another workbook. Excel would like to make a link to that workbook instantly.
Doing so will make your formula like this:
Also, note that the yellow highlighted part is known as an external link or reference. This section of the formula shows a leading way to Excel to go this way. When you are having an external link in the formula, you are free to update it directly while changes are made in the linked workbook.
However, unfortunately, you always have to be available in that linked workbook. In any case, if you have deleted, renamed, or changed the file location, you will not be able to update the data. Removing the external links is necessary from the workbook that you have to share with others. It is because when you are having loads of formulas, you can get mad.
How to Find External Links in Excel
Coming back to the main point, let’s get to know how you can find external links or references in an Excel spreadsheet. We have some quick workarounds that let you find references easily.
Find External Links in Excel Using Report and Replace
In this technique, you will have to use Report and Replace function to find external links in the workbook.
- At first, press CTRL + F from the keyboard, and you will see a dialog box of Find and Replace.
- On the bottom right-hand side of the dialog box, choose Options.
- Type “*.xl*” in the Find What box, which is an extension used for other workbooks or external links such as *.xl* or *.xlsx
- Choose Workbook from the Within box.
- Choose Formulas from the Look in the box.
- Click on Find All option.
- With this option, all the external links will appear in the workbook.
From the function address bar, you can see the external links used in the workbook.
Find External Links in Excel Using Edit Links Option
This built-in tool in Excel is used to find all the external references. Below are some useful steps to find external links using the Edit Links option:
- Open the Data Tab.
- From the Connections group, choose the Edit Links option. Edit Links will open up a dialog box containing all the workbooks having links or references.
- To change all linked cells to values, click on Break Links.
You must have a backup before doing any new operation with your data.
Find External Links in Excel Using Excel Macro
This way could be productive, as you need to make a macro that lets you find and highlight all the external references given in a spreadsheet. Below are some useful steps are given:
- On the Excel sheet, go to the “View” tab and choose the “Macros” button.
- Now, you have to add the macro name, such as Fetch_Links, and press the Create button.
- With this, you will go to the Excel VBA Editor.
- You have to paste the code given below right after the first line.
Dim aLinks As Variant
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
Sheets.Add
For i = 1 To UBound(aLinks)
Cells(i, 1).Value = aLinks(i)
Next i
End If
- Remember the entire code will look as same as it appears in the screenshot given below.
- Press the F5 from the keyboard, it will run the macro. A new worksheet with all the external links and references will be created.
Find External Links in Excel in Series Chart
In Excel, your data might be having series of charts linked to the external workbooks. Though it is not tricky to see an external link in the chart you have to put in some effort.
For this, you have to navigate the mouse cursor on the data or series bar given in the chart. In the Formula box, you will see the external links.
Find External Links in Excel Using Pivot Tables
As you know that a Pivot Table is made with the data used in the same workbook. However, sometimes an outside file has the source data. That’s why to find the exact location of the source data of the Pivot Table you have to follow these steps:
- Click a cell within the Pivot Table.
- Go to the Analyze tab given in the Data group. Choose the Change Data Source button.
- From the dialog box, find the data source given in the Table/Range box to check the external linking presence.
Use VBA to Find External Links in Excel
In this technique, VBA codes are used to find external links and references. Let’s have a look:
- Right-click on the Sheet name from the mouse.
- Choose View Codes to open the VBA.
- Paste the below coding 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
- From the keyboard, press F5, and you will see the external links appear in the current workbook in a new sheet.
Find External Links Using Defined Names
- Open the Formulas Tab and choose Name manager in Excel given in the defined names group.
- See each entry in the list and find external links in the Refers to the column. In another workbook, External references have a reference.
Advantages of External References or External Links in Excel
- You can easily combine data from one workbook to another.
- You could easily have a view of the data. In a single worksheet, rather than having a bigger sum of data, you can use a report in a workbook.
- Multiple references can be used on a single worksheet from other worksheets and you don’t have to open them.
Things To Remember
- In any case, if the linked workbook is deleted, renamed, or changed the original location, then Excel will not be able to find the external links.
- When you re-open a workbook while connecting a workbook with external links or references, you will get a notification that makes your content able to update the links.
- Click on Enable Content and press Continue.
All Set! This is how you can easily find all the external links in Excel by using different techniques. Keep practicing and continue sharing knowledge.
