How to Find External Data Connections in Excel (5 Quick Tricks)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show how to find External Data Connections in Excel. External Data Connections are the reference to the data in another workbook from where one is inserting the data into the existing workbook. This allows users to update their dataset according to the data from the other workbook. In this article, we will know how to keep track of that external connections.


Download Practice Workbook

You can download the practice workbook here.


5 Ways to Find External Data Connections in Excel

In this article, we will discuss 5 handy ways to find External Data Connections in Excel. Firstly, we will use the Find feature to get the links. Secondly, we will use the Name Manager. Thirdly, we will use the Edit Links command to get the connections. Then, we will use a VBA Code to find the connections. Finally, we will use the 4.0 Excel Macros to find External Data Connections in Excel. This is a sample dataset which has data connected to it from two different sheets named Number of Sale.xlsx and Unit Price.xlsx .

excel find external data connections


1. Applying Find Feature

In this method, we will use the Find feature of Excel to find the External Connections of every cell in the dataset. The Find feature allows users to look for the value or any data that they require from the dataset. It also allows them to look for External Connections.

Steps:

  • Firstly, go to the Home tab.
  • From there, select the Editing option.
  • Then, from the available options, select Find & Select.
  • Finally, choose Find from the drop-down options.
  • As a result, a prompt will be on the screen.

applying find feature to find external data connections in excel

  • In the prompt, first, write “.xl” in the Find What box.
  • Then, select Within workbook.
  • Next, Look in Formulas.
  • Finally, click on Find All.

  • Consequently, we will get all the connections of all the cells of the workbook.

using find all command to find all data connections in excel

Thus, we will find all the External Data Connections in Excel.

Read More: [Fixed!] External Data Connections Have Been Disabled in Excel


2. Using Name Manager

The Name Manager option manages all the Name Ranges of the workbook. Users often add a range of data having a Name Range of a workbook into another workbook. This allows them to apply formulas effectively. In this example too, we added a name range from another workbook into our workbook.

Steps:

  • At the beginning, open the Number of Sales.xlsx workbook.
  • Then, select the B5:B10 cell range.
  • Finally, write Fruits in the Name Box to name the range.

naming data range to find all data connections in excel

  • Next, go back to the existing workbook.
  • In the B5 cell, link the workbook with the Number of Sales.xlsx workbook by typing “=” sign and selecting the Fruits range of the Number of Sales.xlsx workbook as formula.

linking two workbooks to find all data connections in excel

  • Thereafter, go to the Formulas tab.
  • From there, choose Define Name.
  • As a result, a prompt will be on the screen.

clicking on the define name command to find all data connections in excel

  • In the prompt, write Fruit_Items as Name.
  • Then, select the Fruits range from the Number of Sales.xlsx workbook as Refer To range of the name.
  • Finally, click OK.

  • Next, go to the Formulas tab.
  • Then, select the Name Manager option under the Defined Names option.
  • Consequently, a prompt will be on the screen.

using name manager to find external data connections in excel

  • In the prompt, under the Refer to section, we will be able to see the External Data Connection of the existing Name Range.

You could copy this link and paste it in the windows taskbar, and it will take you to the location of the Number of Sales.xlsx  workbook.

Read More: How to Create Excel Data Connection to Another Excel File


3. Using Edit Links

The Edit Links option is a good way to check on, edit or modify the External Connections. Here, we will use this option to find the links.

Steps:

  • To start with, select the Data
  • Next, select the Edit Link option from the Queries and Connections
  • As a result, a prompt will be on the screen.

using edit links option to find external data connections in excel

  • In the prompt, you will see the name of the source of the data in the Source column.
  • You could click on the Open Source option to open the source workbook.

  • As a result, the Number of Sales.xlsx Excel worksheet will be opened.

opening the linked workbook to find external data connections in excel

This is how you will find and open the External Data Connections in Excel.

Read More: Excel Connections vs Queries: Main Dissimilarity with Example


4. Applying VBA Code

VBA codes are a good way to do multiple tasks at a time. In this method, we will use a simple VBA Code to get all the External Connections of the workbook and show them in a new sheet.

Steps:

  • Firstly, go to the Developer tab.
  • From there, select the Visual Basic option.
  • As a result, a new window will be on the screen.

  • From the Visual Basic window, first, select
  • Then, choose Module from the drop-down list.
  • Consequently, a new module will appear.

  • In the module, write down the following code and save it.
Sub FindExternalConnections()
Dim am_link As Variant
am_link = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(am_link) Then
Sheets.Add
For a = 1 To UBound(am_link)
Cells(a, 1).Value = am_link(a)
Next a
End If
End Sub

writing code and saving it to find external data connections in excel

  • Finally, run the code by clicking on the triangular shaped green button.

running code to find external data connections in excel

  • Consequently, we will get the list of External Connections of the workbook.

applying vba code to find external data connections in excel

Like the Edit Links method, these links will also take you to the location of the workbooks.

Read More: How to Create a Data Source in Excel (with Simple Steps)


5. Extracting All External Connections

The 4.0 Macros in Excel are the predecessors of VBA. They allow users to perform repetitive works before the introduction of VBA Codes. In this example, we will use one of the still exiting 4.0 or XML Macros to extract External Data Connections. Then, we will transpose the link list using the TRANSPOSE function.

Steps:

  • Firstly, go to the Formulas tab.
  • Then, select Name Manager from the Defined Names group.
  • As a result, a prompt will appear on the screen.

  • From the Name Manager prompt, choose

 

  • Now, first, Name the macros.
  • We will name it
  • Secondly, write LINKS() in the Refer to
  • Finally, click on OK.

  • Next, select the B5 cell and type,
=TRANSPOSE(FetchLinks)
  • Then, hit Enter.
  • As a result, we will have the list of External Data Connections.

You could put this links in the Windows search box to get the location of the External Data.


Conclusion

In this article, we have shown multiple ways to find External Data Connections in Excel. This will allow users to check out, update, break or delete the existing links to the workbook effectively. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.


Related Articles

Adnan Masruf
Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo