In this article, we will discuss 8 suitable approaches to find links in Excel. Excel has tremendous available options to link your working workbook to several external sheets and external workbooks. But, these links create problems sometimes especially when you change the location of the file. Sometimes, when you delete any linked file, issues arise. Now, if you wish to find your file locations, you might need to find links first. Besides, If you want to delete or break any link then again you have to find links first. Take a look at the overview before jumping into the methods.
How to Find Links in Excel: 8 Suitable Approaches
1. Use Find and Replace Option to Search Links in Excel
The easiest and most common option to find a link is to use the Find and Replace option. For instance, we have a grocery product datasheet that is linked to other workbooks containing sales data. Here is how we will find the links.
Steps:
- First, go to the active worksheet.
- Then, type Ctrl+F or go to Home > Find & Select > Find. The Find and Replace window will pop up. In our example, our data is linked to another Excel file. So, we will type .xl in Find what field. This is because Excel files have extensions like .xlsx or .xlsm. You can change Within and Look in fields according to your preferences. Then, click Find All.
- At last, you will get all the available links.
2. Search Links for Hyperlinked Data in Excel
Sometimes, we hyperlink data to other worksheets. Finding a hyperlink path is very easy. You just have to hover the mouse on the data that is hyperlinked to the external sheet. For example, we have some grocery items that are hyperlinked to their date-wise sales in another worksheet. Now, we will show how to find the linked path.
Steps:
- Simply, put your mouse on the hyperlinked data and you will see the link.
3. Find Links Using Edit Links in Excel
Searching links through the Edit Links option is very quick and easy. Moreover, you can use this option to break the link to other files.
Steps:
- Initially, go to the Data tab and choose Edit Links.
- Later, you will find all the links present in the data.
Note:
If you ever try to break links using Edit Link options, it is better to keep a backup of the files before the operation. The reason is, that the operation breaks all the links to other files. And, sometimes you might need to see the links again.
4. Using Defined Names to Get Links in Excel
Often, we use Define Name in Excel to link data to another file. If you want to find the source file of your existing data using Name Manager can be an option.
Steps:
- Firstly, go to the Formulas tab, and click Name Manager.
- As a consequence, the Name Manager window will show up. The window shows the referring file of our current dataset.
5. Find Excel Links in Charts
Sometimes, in Excel, you will see that charts are linked to another file. In such scenarios, if you want to know the source of the chart, you can follow the below steps:
Steps:
- First, select the chart.
- Then, go to the Format tab, and choose the data Series from the Current Selection group.
- Finally, you will find the path to which the chart is connected.
6. Search Links in Excel Objects
Likewise, charts and shapes can also be linked to other workbooks in Excel. Luckily, finding links in shapes and other objects is very easy. In this method, we have linked a shape to an external Excel file. So, now we will see how to find the links in objects.
Steps:
- Simply, hover the mouse over the shape, and you will see which file the shape is connected to.
7. Find Excel Links in Pivot Tables
Similar to objects and charts, you can also find the link in Excel Pivot Tables. In this example, we have linked a pivot table to another file. You can follow the below steps to get the link.
Steps:
- Firstly, select the Pivot Table you are working with. Secondly, go to the Pivot Table Analyze tab and click Change Data Source.
- Then, Choose Change Data Source.
- Finally, you will see the link to the file connected to the Pivot Table.
8. Use VBA to Get Links in Excel
If you want to find all the links in a workbook at once, using VBA code can be a suitable option. So, here is the procedure.
Steps:
- First, go to any worksheet where you want to see all the links used in your Excel workbook. Then right-click on the sheet name, and select the View Code option to bring the VBA window.
- Write the following code in the 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
- Now, run the code, and you will find all the links existing in the workbook.
Download the Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In the above-mentioned discussion, I have tried to explain most of the easy ways to find links in Excel. Using these methods, you can find the links. And, delete the links whenever necessary. If you have any queries or suggestions regarding these methods, please let me know.
