How to find external links in Excel and remove them?

To find and remove external links in Excel, you primarily use the 'Edit Links' dialog box to identify and break connections, while also checking various other locations such as formulas, named ranges, and data connections.

How to Find External Links in Excel

External links in Excel can connect your workbook to data in other Excel files, web pages, or other data sources. These links are often used for dynamic updates but can also cause issues like slow performance or broken references if the source file moves or is deleted.

Here's how to locate them:

1. Using the "Edit Links" Dialog Box

This is the most straightforward method for finding external links that Excel formally recognizes.

Navigate to the Data tab on the Excel ribbon.
In the Connections group, look for and click Edit Links.
If the Edit Links command is greyed out or unavailable, it means your file does not contain linked information that Excel can manage through this dialog.
If available, the dialog box will list all recognized external sources. You can select each source to see its status (e.g., OK, Error, Unknown).

2. Checking Formulas

Many external links reside within cell formulas. These typically include the path to the external file enclosed in square brackets [] and an exclamation mark ! before the cell reference.

Example: ='C:\Reports\[SalesData.xlsx]Sheet1'!A1
Using Find & Replace:
1. Press Ctrl + F to open the Find dialog.
2. In the "Find what" box, type [. This is a common indicator of an external link.
3. Click "Options" and ensure "Look in" is set to "Formulas".
4. Click "Find All" to list all cells containing [. Review these to identify external links.
Trace Precedents/Dependents: For specific cells, you can use the "Formulas" tab's "Trace Precedents" or "Trace Dependents" tools in the "Formula Auditing" group to visualize formula relationships, which might point to external sources.

3. Reviewing Named Ranges (Name Manager)

External links can be hidden within defined names, especially if the named range refers to data in another workbook.

Go to the Formulas tab.
In the Defined Names group, click Name Manager.
Carefully review the "Refers To" column for any names that include external file paths (e.g., ='C:\Path\[ExternalData.xlsx]'!Sheet1!$A$1:$B$10).

4. Inspecting Data Validation Rules

Sometimes, data validation lists are sourced from external workbooks.

Select the range of cells that might have data validation.
Go to the Data tab, and in the Data Tools group, click Data Validation.
In the Data Validation dialog box, check the "Source" field under the "Settings" tab for any references to external files.

5. Examining Charts and Objects

Charts can have their data series linked to external workbooks. Embedded objects (like linked images or OLE objects) can also maintain external links.

Click on a chart, then go to Chart Design > Select Data. Review the "Chart data range" and individual series for external references.
Right-click on embedded objects or shapes and check their properties, source, or linked files.

6. Checking Data Connections and Queries

If your workbook imports data from external sources using Power Query, connections, or older data import features, these represent external links.

Go to the Data tab.
In the Queries & Connections group, click Queries & Connections to open the task pane.
Review the listed queries and connections for any external data sources.

7. Conditional Formatting Rules

Less common, but conditional formatting rules can sometimes reference external workbooks.

Go to the Home tab, Styles group, click Conditional Formatting > Manage Rules.
Review the rules, especially those that use formulas, for external references.

How to Remove (Break) External Links in Excel

Once you've identified external links, you can remove them. Be aware that breaking a link means the data will no longer update automatically; the values from the external source will be converted to static values in your current workbook.

Here's how to remove them based on where they were found:

1. Breaking Links Using the "Edit Links" Dialog Box

This is the primary method for formally recognized links and often the most efficient.

Go to the Data tab on the Excel ribbon.
In the Connections group, click Edit Links.
- Note: This command will be unavailable if your file does not contain linked information that Excel manages formally.
In the Source list, click on the specific link (or links, using Ctrl for multiple selections) that you want to break.
Click the Break Link button.
Excel will display a warning about permanently converting formulas to values. Confirm by clicking Break Links again.

2. Manually Removing Links from Formulas

For links found directly in cell formulas, you'll need to edit the formulas.

Individual Cells: Select the cell, go to the formula bar, and manually delete the external file path and name (e.g., remove ='C:\Reports\[SalesData.xlsx]). You will be left with =Sheet1!A1 or just =A1.
Using Find & Replace for Multiple Cells:
1. Select the range containing the formulas with external links.
2. Press Ctrl + H to open the Replace dialog.
3. In the "Find what" box, enter the specific external path you want to remove (e.g., ='C:\Reports\[SalesData.xlsx]).
4. Leave the "Replace with" box empty.
5. Click "Replace All." This will remove the external path, converting formulas like ='C:\Reports\[SalesData.xlsx]Sheet1'!A1 to =Sheet1!A1.

3. Deleting or Modifying Named Ranges

Go to the Formulas tab and click Name Manager.
Select the named range that refers to an external workbook.
You can either:
- Click Delete to remove the named range entirely.
- Click Edit to change its reference to point to a local range or value, or to simply remove the external part of the reference.

4. Updating Data Validation Sources

Select the cells with external data validation rules.
Go to the Data tab and click Data Validation.
Under the "Settings" tab, change the "Source" to an internal range or a list of values, or select "Any value" if the validation is no longer needed.

5. Adjusting Chart Data and Object Properties

For Charts: Select the chart, go to Chart Design > Select Data, and either change the data range to an internal source or manually edit each series to remove external references.
For Objects: Right-click the object and check its properties or source to unlink it or embed the data rather than linking.

6. Removing Data Connections

Go to the Data tab and click Queries & Connections.
In the task pane, right-click on the query or connection linked to an external source.
Select Delete or Remove to break the connection. Be aware that this might remove the imported data from your workbook.

Important Considerations:

Backup: Always save a backup copy of your Excel file before breaking links, as the process is often irreversible for the data.
Hidden Elements: Links can sometimes be present in hidden sheets, rows, columns, or even very small, hidden objects. Ensure you check all parts of the workbook.
VBA Code: If your workbook contains VBA macros, they might be creating, modifying, or relying on external links. Removing links without considering the VBA code could lead to runtime errors.