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
An external link is a link or a reference to a cell or range on a worksheet in another excel workbook, or a reference to a defined name in another workbook.
Linking to other workbooks is a very common task in excel, but many times we come in such a situation when we cannot find even if excel tells us that external links or external references are present in the workbook. Any workbook which has external links or references will have the filename in the link with its .xl file extension.
External references are somewhat similar to cell references but there are some differences in them. External references are used while working with large amounts of data with many complex formulas.
Now the question arises why do we use external references in excel? There comes a situation when we cannot keep large amounts of data in the same workbook. In this scenario, we need to keep the data in different worksheets.
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 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 the 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. In Column C, it will contain the number of correct answers and in Column D it will contain a number of Incorrect answers.
- 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 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 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.
- In the Data tab, there is a section of connections where we can find an option of Edit Links. 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.
Note: By default, this option remains disabled and it only activates when our workbook has any external links to it.
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 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 –
