How to Find External Links in Excel

If you are working with a large Excel spreadsheet that includes several worksheets, you may have linked data between them which can sometimes include external links. External links can be difficult to find and remove, especially if you have inherited a spreadsheet from someone else and don’t know where all the data is coming from. In this post, we will show you how to quickly and easily find external links in Excel using built-in tools and formulas. This guide will help you identify any external links that may be causing issues with your data or causing your spreadsheet to slow down.

Introduction

Whether you are a seasoned Excel user or just getting started, finding external links in your spreadsheets can be a daunting task. These links can be hidden within your formulas, tables, charts, or macros. Sometimes these links can cause errors in your calculations or prevent your workbook from running correctly. In this post, we will show you how to quickly and easily find external links in Excel by using built-in tools and formulas.

Method 1: Using Excel’s Built-in Tools

Step 1: Open the Workbook

The first step is to open the workbook that you want to check for external links. Once it’s open, go to the “Data” tab in the Excel ribbon.

Step 2: Click on “Edit Links”

Under the “Connections” group in the Data tab, you’ll find the “Edit Links” button. Click on it.

Step 3: View the External Links

After clicking on “Edit Links,” a new window will open which lists all the external links. If there are no external links, it will say “The workbook does not contain any external data connections.” If there are links, they will be listed here, along with the location of the linked workbook and any warnings or errors.

Method 2: Using Formulas

Step 1: Identify the Cell with the Source Data

If you suspect there may be an external link in a worksheet, begin by selecting the cell where the data is being pulled from. This may be a cell in a different worksheet or workbook.

Step 2: Use the “Find” Function to Search for External Links

Once you have identified the cell with the source data and have it selected, go to the “Home” tab and click on “Find & Select” in the “Editing” group. Then select “Find”.

Type in the following formula and press “Find All”: =*

This will search for all cells that contain a formula. If any of those cells reference data in another workbook, Excel will display a message with the cell’s location and the linked workbook’s path.

By using these two methods, you can easily locate any external links in your Excel worksheets and take the necessary steps to update or remove them. Keeping your workbooks free of external links can save you time and prevent errors in your calculations, making sure your Excel skills are top-notch.

Tips for Working with External Links

Once you’ve identified external links in your Excel workbook, consider taking one of the following steps:

Update Links

If the external link is still necessary, you may need to update the link to the correct path or workbook. To do this, go to the “Data” tab and click “Edit Links” again. Here you can update the link or change the source file by clicking on “Change Source”.

Remove Links

If you find that the external link is no longer needed, then you might want to remove it. You can do this by going to the “Data” tab and clicking “Edit Links.” Select the link you want to delete and click “Break Link.”

Disable Automatic Calculations

If you have a large number of external links, or if your workbook is slow, consider disabling automatic calculations while you find and update the links. Go to the “Formulas” tab and under the “Calculation” group, select “Manual”. You can always toggle back to “Automatic” once you have finished updating or removing the links.

External links can cause issues and errors in your Excel workbook if not adequately maintained or updated. So it’s essential to know where they are and take the necessary steps to keep them up-to-date or remove them altogether. By using the two methods outlined in this post, you can quickly locate the external links in your workbook and keep your spreadsheets in top condition.

FAQs About Finding External Links in Excel

Here are some frequently asked questions and answers about finding external links in Excel:

Can an external link affect the calculation speed of my workbook?

Yes, external links can slow down your workbook’s performance if there are many or if they reference a large volume of data. Therefore, it’s vital to locate and remove any unneeded external links.

What happens if I delete an external link?

If you delete an external link, the workbook may reference a cell that no longer exists. Consequently, Excel will replace that cell’s value with a warning message. Therefore it’s better to break the link first and check if the data is needed before removing it.

How do I break an external link?

You can break an external link by going to the “Data” tab and clicking “Edit Links.” Select the link you want to break and click “Break Link.”

What are other reasons to search for external links besides performance issues?

You may need to find external links if you want to understand where all of your data is coming from, check the formulas used in those links, or if you need to update or change the source file associated with that data.

Is there an alternative method to finding external links?

Yes, you can use third-party Add-Ins like the “FindLink” by Excel Campus to locate external links in your workbook. The Add-In provides a clear list of all links, along with additional information, making it easier to manage them.