External links are also known as external references. In Excel, when we use any formula and refer to any other spreadsheet apart from the one with the formula, the new spreadsheet is known as an external link to the formula. Also, when we provide a link to use a formula in another workbook, it is called an external link.
The location of the file that is highlighted in yellow in the above picture is the external link/reference. The link will make Excel go to the location and refer to the specified cell in the workbook.
The positive point in having an external link is that it’ll automatically update once the data in the workbook is changed. The best way to keep everything updated is by not changing the location, name, or deleting the workbook.
Otherwise, the file won’t update the link and data.
How to Find Links in Excel?
- Edit Links Option
- Find and Replace Option
Find External links using the Edit Links option:
Microsoft Excel has an inbuilt tool that allows you to search and find all external links, as well as edit them at your convenience. The following steps allow you to use the edit links option and find external references in your spreadsheet.
Step 1: Go to Data Tab
- Navigate to the Data tab in the top left corner of the window and go under connections group.
Step 2: Execute Edit Links option
- After navigating to the data tab, go under connections and click on the edit links option. The following will open the edit links dialog box and list all spreadsheets that’ve been referred to.
Step 3: Use the Break link option
- Click on the break link option to convert all the linked cells into values.
- Be careful that once these are converted into values, it’s not possible to undo the operation. The best way you can help yourself is by taking proper backup of the workbooks via clouds storage or external hard drive storage.
Using Find links option
Step 1: Select the Cells
- Select the cells in the workbook and go to Find and Select option in the top right corner of the Home tab.
- From the drop-down menu, click Find.
Step 2: Find Menu
- In the find and replace pop-up menu, enter – ‘.xl’ in the search field and click find all.
Step 3: Check the Links
- The workbook will find all the cells that have external links in it.
Step 4: Select the Cells with links
- Select the following cells that have the links and convert all these values into formulas.
Dealing with Prompt error
In many cases, despite having found and removed all the links from the workbook, Microsoft Excel gives a prompt as that the workbook contains one or more than one external reference.
If you’re facing this issue, even after finding and removing the links, check for the following types of links in the workbook:
- Shapes
- Conditional formatting
- Named ranges
- Chart titles
- Data validation
The find and replace links option won’t fix the above-mentioned types, but it’s unlikely that you’ll get the prompt. There is also an add-in(extension) available to find links online. The following methods allow you to find, remove and edit links in Microsoft Excel.
Conclusion
That’s it for the tutorial. Following the above method, you can easily find links in your Excel workbook.