Updated August 21, 2023
Find External Links in Excel
Find External Links in Excel helps to locate the links in any cell or any Excel worksheet formula. There are 2 ways to find external links in Excel; one is using Find and Replace, and the other is using Edit Links options. If we use Find and Replace, we need to find with “*.xl*”; this will show all the links available in that worksheet or workbook with the list. And Another way to find external links is by the Edit Link option in the Data menu tab. From there, we will also get to know all the links in the current worksheet.
Benefits of using external references or external links in Excel:
- We can merge data from many workbooks.
- We can work on one worksheet with many references from other worksheets without opening them.
- We can have a better view of our data. Instead of having a large sum of data in a single worksheet, we can have our dashboard or report in one workbook.
How to Create External References in Excel?
Let us create an external reference in a workbook with an example,
Suppose we have five people who have to check 100 questions, and they have to mark their answers as correct or incorrect. We have three different workbooks. In Workbook 1, we have to collect all the data, which is named as the report, whereas in Workbook 2, which is named as correct, which contains the data marked by them as correct, and in Workbook 3, which is named as incorrect, has data of incorrect values.
Have a look at Workbook 1 or the Report Workbook:
In the above image, in Column A, there are names of people, and in Column B, it has a total number of questions. Column C will contain the number of correct answers, and Column D will contain the number of Incorrect answers.
- Use a VLOOKUP Function from workbook 2, i.e., the correct workbook in cell C2, to get the value of correct answers marked by Anand.
- The output will be as given below.
- Drag and drop the formula to the remaining cells.
- In Cell D2, use a VLOOKUP function from workbook 3, i.e., incorrect workbook, to get the value of incorrect answers marked by Anand.
- The output will be as given below.
- Drag and drop the formula to the remaining cells.
Now we have all the correct and incorrect values marked by those five people. Our main workbook, “Report”, has external links or references from different workbooks, i.e., Correct & Incorrect.
Now we come to the process of finding these external links or references in an Excel workbook. There are different manual methods to do so. We will use the above example further to discuss.
How to Find External Links in Excel?
There are two methods to find an external link or references in an Excel workbook. Let’s understand how to find external links with some examples.
Find External Links in Excel – Example #1
We have our workbook, “Report,” and we need to find external links in this Excel workbook.
- Press Ctrl + F and a dialog box appears of Find and Replace.
- Click on Options on the below right-hand side of the dialog box.
- In the Find what box, type “*.xl*” (an extension of the other workbooks or external links is *.xl* or *.xlsx)
- In the Within box, select Workbook.
- And in the Look in box, select Formulas.
- Click on Find All.
- It displays all the external links in this workbook.
In the function address bar, it can be seen that the workbook has an external link with the workbook “Correct” and displays the workbook’s path too.
Find External Links in Excel – Example #2
The second procedure is from the Edit links option.
- There is a section of connections in the Data tab where we can find the Edit Links option. Click on Edit Links.
- It shows the external links in the current workbook.
Edit links display all the other files to which our workbook is linked.
Explanation of External links in Excel
As explained earlier, why do we need external links to our worksheet? The quick answer would be that we cannot keep large amounts of data in the same workbook. In this scenario, we need to keep the data in different worksheets and reference the values in the main workbook.
Now, why do we need to find external links in a workbook?
Sometimes we need to update or delete our links to change or update the values. In such a scenario, we first need to find external links.
Let us also learn how to break down External links or references. In the data tab, where we clicked on edit links to check the existing connections, there is an option of Break Link (highlighted in yellow).
This breaks the connection between the connected workbooks.
Things to Remember
- If the connected workbook is deleted, renamed, or moved from its place, excel will be unable to find the external links.
- When a workbook has external links or references, re-opening the workbook Excel gives a prompt to enable content so that the links can be updated.
- Click on Enable Content, then continue.
Recommended Articles
This has been a guide to Find External Links in Excel. Here we discuss how to Find External Links in Excel and Excel examples. You can also go through our other suggested articles –
