Excel Find External Links (Table of Contents)
- Find External Links in Excel
- How to Create External References in Excel?
- How to Find External Links in Excel?
- Explanation of External links in Excel
Find External Links in Excel
Find External Links in excel helps to locate the links there in any cell or any formula of the excel worksheet used in. There are 2 ways to find external links in excel; one is using Find and Replace, and the other is Edit Links options. If we use Find and replace, then 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, which is in the Data menu tab. From there also we will get to know all the links therein 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 in Column D, it will contain a number of Incorrect answers.
4.9 (9,548 ratings)
View Course
- Use a VLOOKUP Function from workbook 2, i.e. correct workbook in the 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.
- Now 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, which is “Report”, has external links or references from different workbooks, i.e. from workbooks named 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 different 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 it displays the path of the workbook 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.
Basically, 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 because 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 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 along with excel examples. You can also go through our other suggested articles –
