How to Find External Links in Excel (2023 Guide)
Are you tired of manually searching through your Excel spreadsheet to find external links?
Look no further! ✋
In this quick guide, we’ll show you how to locate and manage all your external links in Excel within seconds.
Say goodbye to wasted time and hello to a more efficient way of working. Let’s get started!
Table of Contents
What are external links?
External links are a quick method to link data that exists outside of your current workbook 💻
Excel external links let you connect multiple worksheets together and use data from one source in another. This is a common practice for everyday Excel users.
However, as easy as it is to add external links, it is equally difficult to locate them in the workbook.
Moreover, the external link breaks if the linked source is moved, deleted, or even renamed. This means you need to be extremely careful while working with external links.
To identify external links in excel, you might need to use a couple of different methods. To help you out, we have explained some of these ways below.
Let’s review these methods in detail and find external links in our workbooks 📓
Find external links with Find and Replace
The easiest way to find external links in your Excel sheet is to use the Find and Replace tool.
It will filter out all the cells containing links and show you the external links in your workbook. To do this:
- Press CTRL + F or select Replace from the Find & Select option under the Editing group.
The Find and Replace dialog box appears.
- In the Find What box, type .xl.
You can also type .xlsx. But typing .xl will show you linked files of all Excel versions in your sheet.
- Select the Workbook option from the Within box.
- Select Formulas from the Look-In box.
- Click Find all.
Excel will show you information about all the cells containing the links as:
You can select a cell by clicking any option under Cells. And to select all results, press CTRL A.
Note that by using Find and Replace, you will be able to identify all external links in Excel. But if Excel still shows a prompt of external links, try one of the following methods.
Find external links with the Edit Links Option
You can use this option to find and break links in the Excel workbook.
Upon breaking the link, it will be removed. And the worksheet will only contain the values of the external reference.
Let’s understand this concept below 🔽
Suppose we have an external link from a file named Multiples of 5 & 10.
As we link the source file to our destination file, values of the source file will be displayed in the formula bar as:
To find the external link:
- Go to Data Tab.
- Select Edit Links from the Queries & Connections group.
- The Edit Links dialog box will appear.
This shows a list of all the external links present in your workbook.
Now to break the external reference:
- Select the Source you want to break.
- Click Break Link at the mid-right of the dialog box.
- Excel will show a warning that the action of breaking a link cannot be undone. Press Break Links.
The link will be broken, and the external link will be converted to its existing values.
If you select any value from the source data now, this is what the formula bar will show:
You might need to practice this a couple of times to master finding and breaking external links. But once you do that, it will be at your fingertips 🎯
Note that breaking a link is a permanent action and cannot be undone. To avoid any mishaps, it’s better to keep a backup of your external link.
Other causes for external link prompt still showing
If Excel shows this prompt even after removing all external links – don’t smash the screen. Keep your calm, and we will show you some easy solutions to get this error fixed 😁
These external sources might be in Named ranges. Data Validation, Chart Titles, Conditional Formatting, or even Shapes.
The Find & Replace and Edit Links options might not work here. So we will have to check each above-mentioned option individually.
To find external links in Named Ranges,
- Go to the Formula Tab and open Name Manager.
- All Named Ranges in your spreadsheet will appear.
- Click the ‘Refers to‘ column.
- The External links will appear.
To find external links in Data Validation,
- In the case of Data Validation, it is possible it refers to a named range in your sheet.
- Looking through the Named Ranges should suffice for this part.
To find external links in Chart Titles,
- Click on the Chart Title.
- If it contains any external links, the Formula Bar will show it.
To find external links in Conditional Formatting,
- Go to Home Tab > Custom Formatting > Manage Rules.
- If there are any external links in conditional formatting, you will be able to see them here.
External links in Conditional Formatting are mainly because of the Custom Format. Checking it in the rules should solve the problem.
To find external links in Shapes,
- Press F5 on your keyboard, and the Go To dialog box will appear.
- Select Special > Objects.
- Click Ok.
All the shapes in your spreadsheet are now selected.
This might take a while, but you will get a hold of it once you begin doing it. 😉
That’s it – Now what?
In this article, we saw how to find external links in excel. We explored methods like Find and Replace, Edit links options, and more.
While creating external links in Excel is easy, finding them manually can be challenging. And even more so when your workbooks contain multiple worksheets with tons of data.
Luckily, using the above methods, you will be able to identify and remove external links in no time 😀
Similar to external links, you can also use internal links in Excel. These are more commonly known as cell references and are used in almost every Excel task.
Linking in Excel is great, but the best Excel features have to be the functions and formulas. Some fantastic Excel functions include SUM, SUMIF, VLOOKUP, etc.
You can learn them for free in my 30-minute free email course. Only at the cost of your email address. So join now and learn these functions and more!
Did you enjoy reading this article? If yes, then you’d love to know more about such topics.
Read Similar Topics: Find and Replace, Cell References, Hyperlink, and more.