How to Find External Links in Excel: 5 Quick Techniques

Have you received an Excel workbook that repeatedly shows warnings about external links whenever you open it? Worry not, because this is a pretty familiar occurrence for anyone who works with Excel. In moments like this, you feel like you should have known how to find external links in Excel and solve the problem.

We understand this situation. So let’s change it today with our visual guide. We are going to show you 5 smooth and effortless ways to find external links in Excel. Apart from all the methods explained in simple and easy-to-follow steps, we are going to include important tips in our guide as well.

We are also sharing with you the Excel file to practice the methods step-by-step. You may use it to further reinforce your understanding and learning.

Let’s begin then!

What are External Links in Excel?

External Links in Excel are also sometimes referred to as External References. They accommodate references to data in cells or ranges in another Excel workbook. They offer an effective way to work with data from multiple workbooks without congesting all those data into the same workbook.

For instance, if you have to create an annual employee performance chart and you have multiple workbooks for each month, you can simply create a new workbook and link data from the monthly workbooks. If something has to be changed in monthly workbooks then you can change them also. The changes will be automatically updated in the annual performance workbook.

But, sometimes when work is done, external links or external references are not needed to be kept in the workbook. Especially when you want to send an Excel workbook to someone, it is better if external links are removed before sending the file. Contrarily, if you are on the receiving end, you may need to keep only data and remove the external links. But how do you know that the Excel workbook you have received contains external links?

There are some ways you can understand it instantly. They are shown below with pictures.

  • When you have opened a workbook and you get a warning just above the Formula Bar, you can be certain that your workbook contains external links. Notice the marked area below.

What are External Links in Excel 1

  • You may get a different kind of warning pop-up as well, like the one in the picture below. Click on Update if you want to update your workbook with your latest data. Otherwise, click the Don’t Update button to open the workbook as it is.

What are External Links in Excel 2

  • You can recognize a workbook with external links by clicking on cells with data as well. Select a cell and notice the Formula Bar. The formula bar will contain external links in them. See the highlighted part in the image below.

What are External Links in Excel 3

  • The links may be inside a formula as well. See the image below.

What are External Links in Excel 4

  • In some cases, the links may not have the full address showing within the address bar. Notice in the picture. In most cases, it is an internal link with a reference to a different sheet within the same workbook.

What are External Links in Excel 5

This is how you can realize that your workbook has external links. But how can you find all the external links in your Excel workbook? For that, let’s move on to the next section. You are in for a good time!

5 Ways to Find External Links in Excel

Now that we know what it looks like, we are going to show you 5 different ways to find external links in Excel. These five techniques are super-easy to follow and practice. At times, you may need to try more than one method to find all the external links in your workbook.

Now, to apply the methods as you go through them, we are sharing the Excel workbooks with you for this guide. You may use your own files to practice the methods. However, our workbooks will match the pictures here, and therefore, they may prove easier to follow due to the similarities.

Let’s begin with the first method!

Method 1: Finding External Links with the Edit Links Option

For our first method, we are going to show step-by-step how to find external links in Excel by using the Edit Links option. This one is useful for almost all situations. It lets you break the external references links in case you are looking to remove those links from your workbook.

Let’s see how to work with this method. Always make a backup of your workbook beforehand if you are going to use the Edit Links option.

1. First of all, click on the Data tab to open the ribbon. From there, within the Queries & Connections group, click on the Edit Links option.

Finding External Links with the Edit Links Option 1

2. Now you will see a new mini-window titled Edit Links. There is a box in that window that has a list of all the external links. Right below the box, the location of the selected external link file is also shown.

In this window, after selecting an external link from the list, you can open them by clicking on the Open Source button. In the case that a link is broken, you can check the status by clicking on the Check Status button and then see it in the box under the Status column.

Finding External Links with the Edit Links Option 2

If you want to remove an external link by breaking a link, select the link, and then click on the Break Link button.

3. You will see a warning message after you click the button. Remember that once you break a link, there is no going back. It is impossible to undo the action even if you try to use the Undo option (or press the CTRL+Z buttons). Closing the file without saving it also will not work. Once you break the link, it is removed instantly. There is no way to re-link it.

Finding External Links with the Edit Links Option 3

Now, if you wish to remove the external links, click the Break Links button on the warning message window. And the selected external link will be removed!

4. For instance, we are going to remove the reference link for the “PUM Sheet.xlsx” in our workbook.

Finding External Links with the Edit Links Option 4

After selecting the link in the Edit Links window, we clicked the Break Link option and then confirmed in the warning box.

5. Right afterward, the external link for the “PUM Sheet.xlsx” vanished from the list.

Finding External Links with the Edit Links Option 5

And as marked in the picture above, the cell now showed only the value instead of the whole formula which accommodated the link to an external sheet named “PUM Sheet.xlsx” before breaking the link.

6. For the other link, the same procedure was followed. Notice the marked areas, especially the formula bar for the external link.

Finding External Links with the Edit Links Option 6

7. See the difference in the After image below.

Finding External Links with the Edit Links Option 7

As the link has been broken, only the values remain.

That is so easy, isn’t it?

Important Tip: You need to be extremely careful to keep a backup before you mess around with the Edit Links option.

If you want to remove or break multiple external links using this technique, you can select those links by pressing and holding the CTRL button and then clicking all the external links in the list. After you have selected the desired links, click the Break Link button and follow the same procedures shown in this Method 1.

Method 2: Using the Find Option to Locate External Links

The external links or references can also be found using the powerful Find option in Excel. Although it may need the steps to be followed precisely, it is rather a straightforward process for locating the external links.

And instead of showing only the external links within a workbook like in Method 1, this Find option shows all the cells and ranges that contain external links. You can even select all the cells and do all sorts of things with them including highlighting them to locate them easily. We will show everything in steps.

So, let’s see how to find external links in Excel by using the Find option.

1. Start by clicking on the Find & Select option in the Editing group under the Home ribbon. From there, click on the Find option. Alternatively, you can press the all-familiar shortcut buttons CTRL+F to bring up the Find and Replace mini-window.

Using the Find Option to Locate External Links 1

2. After the window opens up, locate the Find what: text box under the Find tab. This is where some specific texts will be used to find the external links. Let’s move on to the next step to see how to do it.

Using the Find Option to Locate External Links 2

3. You can click on the Options button to expand the window which will show you other options. From the Within drop-down box, you can select Sheet or Workbook depending on which area you want to conduct your search for finding the external links.

Using the Find Option to Locate External Links 3

If you want to find the links in the sheet you are in right now, select Sheet. Otherwise, keeping the Workbook selected will search the whole workbook for external links.

4. Now, write down the magical bits in the Find what: text box. Click on the marked box shown below and then write *.xl*. The asterisks will work as wildcards because there are many Excel extensions out there, like .xlsx, .xlsm, .xlsb, .xls, .xml, etc. which can be linked externally. You will notice that all of them start with “.xl” and then the rest of the letters are different.

Using the Find Option to Locate External Links 4

Alternatively, you can simply write the open bracket “ [ ” and then click Find All. It works the same way because if you notice in the formula bar, all the external links have the filenames within brackets in [file_name.xlsx] format.

Using the Find Option to Locate External Links 5

5. As soon as you click the Find All button, you will be shown a list of external links right in the below part of the Find and Replace window!

Using the Find Option to Locate External Links 6

Beneath the list, you can also see how many cells with external links have been found through your search. Every detail is there for a particular cell along with the used formula.

6. You can click on a link from the list and it will select the corresponding cell instantly! Look at the image below.

Using the Find Option to Locate External Links 7

7. Now, if you want to highlight the cells that have external links, press the CTRL+A buttons to select all the links. You will see that the cells with external references have been selected.

Using the Find Option to Locate External Links 8

8. While they are selected, apply Fill Color from the Font group found under the Home tab.

Using the Find Option to Locate External Links 9

That’s it!

Method 3: Detecting External Links in Excel Objects

Let’s say whenever you open a workbook, you keep getting the external link warning message. You have removed all the external links but you keep getting the message. Now you don’t know where else some external links might be hiding.

Let us help you with the information and how to locate them. We understand that when external links are applied to objects, a traditional Edit Links option or Find option fails to locate them almost all the time. It’s because those two options look for links in cells and ranges whereas objects are a different thing. This is the problem you might be facing.

So, how to find them? It requires a bit of manual work. But when you have a huge sheet full of data with some objects in there and it is hard to scroll through them all, an easy technique must be discovered. Hence, using the Go To Special option to locate the objects with external links is the way to go.

We are going to work with the sheet below. For an easier understanding, we are going to declare beforehand that the marked areas contain external links.

Detecting External Links in Excel Objects 1

While the names in this example have standard a type of external referencing within a formula, the objects (rectangular shapes with PHY, MAT, and CHE) also have external links in them. The goal is to find those in the shapes which happen to be Excel objects.

Let’s see how it is done.

1. Under the Home ribbon, click the Find & Select option in the Editing group. When the drop-down menu opens, click the Go To Special… option.

Detecting External Links in Excel Objects 2

Alternatively, you can simply press the F5 button to bring up the Go To mini-window. From there, click the Special button as marked in the image below.

Detecting External Links in Excel Objects 3

2. When the Go To Special window pops up, click on the Objects radio button. Then hit OK.

Detecting External Links in Excel Objects 4

3. You will see that the shapes have been selected. Now, go through them one by one to see which objects have external links in them.

Detecting External Links in Excel Objects 5

When you select an object, you can see the external link address or filename in the formula bar.

It may need a bit of extra work, but it’s still better than manually scrolling through the whole sheet and finding the objects!

Method 4: Using the Name Manager to Find External Links

Getting the warning for external links repeatedly but you cannot find the links anywhere? You may have already gone through the previous three methods and they did not seem to remove or break all the links. Let’s now take a look inside the Name Manager option to see if it is hiding some links from you.

In three easy steps, you are going to learn how to use this method to find your external links. Let’s go!

1. Click on the Formulas tab first. When the ribbon shows the groups with options, in the Defined Names group, click on the Name Manager option.

Using the Name Manager to Find External Links 1

2. The Name Manager window will open. There, you will find the external links. When you click on a link, you will see the location of the external file to which that specific link refers to.

Using the Name Manager to Find External Links 2

From this window, you can Edit or Delete external links. You can even add New external links.

3. If you want to delete every single external link in this list, you may select all the links using the famous shortcut CTRL+A and then click the Delete button.

Using the Name Manager to Find External Links 3

Easier than it looks, isn’t it?

Method 5: The Visual Basic for Applications (VBA) Approach

For the final technique on how to find external links in Excel, we are going to use Visual Basic for Applications (VBA). This approach is extremely useful when you need to make some changes in the external source files but don’t know where the files are. So, finding and listing all the links or addresses to the external files in your workbook first is the goal here. Then you can use those links to locate and edit the files you want.

And for this, a few lines of code do an amazing job! Those who are already familiar with the VBA game do not need any introduction. Even if you don’t know how to code in VBA, simply copy-paste the code we are sharing with you and see the magic happen!

We are still using the same sheet shown below.

The Visual Basic for Applications (VBA) Approach 1

Let’s see in a few steps how to find external links in Excel workbooks and list them all in a single separate sheet!

1. Click on the Developer tab. In the Code group, click on the Visual Basic button. If you don’t see the Developer tab, we will show you how to activate it right after this method.

The Visual Basic for Applications (VBA) Approach 2

Alternatively, you can press the ALT+F11 buttons. It will open the VBA window.

2. When the Microsoft Visual Basic for Application window opens up, click on the Insert tab, and then from the drop-down list below, click on the Module option.

The Visual Basic for Applications (VBA) Approach 3

3. The Module window will open now. This is where you will need to write the code.

The Visual Basic for Applications (VBA) Approach 4

You can also copy the code from below and paste it into the newly-opened Module window.

Sub LinksList()
Dim nLinks As Variant
nLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
Dim m As Integer
If Not IsEmpty(nLinks) Then
Sheets.Add
For m = 1 To UBound(nLinks)
Cells(m, 1).Value = nLinks(m)
Next m
End If
End Sub

4. After you have copied the code from above, paste it inside the box. Then click the marked Run button. Alternatively, you can hit F5 and it will run the code.

The Visual Basic for Applications (VBA) Approach 5

Now, after clicking the Run button or hitting F5, simply close the Microsoft Visual Basic for Applications window to go back to the spreadsheet.

5. Now you will see that a new sheet has been added to your workbook which contains the list of all the external links in your workbook!

The Visual Basic for Applications (VBA) Approach 6

How smooth was that?

How to Enable the Developer Tab

If you cannot find the Developer tab in your Excel window, you can add it in two easy steps.

1. First, go to the Options from the File menu.

How to Enable the Developer Tab 1

2. The Excel Options window will open. Click on the Customize Ribbon tab in the left-most column with tabs. Then, on the Customize the Ribbon column to the right, you will find the Developer option unchecked if your Excel does not show the Developer tab by default. So, make sure that this Developer option is checked now.

How to Enable the Developer Tab 2

Finally, click the OK button. And now, you will find the Developer tab in your Excel window.

Final Words

As a regular Excel user, it is necessary to know how to use external links in Excel. It makes life easy and brings speed to work. However, knowing how to find those links is no less important. And as you have gone through the guide, you may have understood that sometimes you will need to apply more than one method to find all the links in a workbook.

For this reason, knowing all the methods is important. We cannot say that one method is more important to know than some other methods from all five methods that we have explained in this guide. Because all of these will help you find the links. Hence, we highly recommend learning all of them equally.

While learning, follow the steps and practice simultaneously. It will ensure effective learning for you. In this way, you can master all five methods in less than thirty minutes! So, let’s make it count!

Related Articles:

Leave a Comment