How do I remove external links?

English (us) ⁄ Nederlands ⁄ Deutsch ⁄ Español ⁄ Français ⁄ Português do Brasil ⁄ Italiano ⁄ Русский ⁄ 中文(简体) ⁄ 日本語This utility shows the links in your workbook to other files and it allows you to remove them.
When a link to another workbook exists in a formula and the link is removed, then the formula will be replaced with its calculated value.

Screenshots


Starting this tool


How do I remove external links?

Additional keywords for this tool:
external references, linking worksheet data, data links, broken links, linked cells

If you have ever opened an Excel file and been prompted to update links to another file, yet you are certain that there are no links to any other files, your file may contain phantom links.

To remove Phantom Links from a workbook, first check to make sure that there are not any non-phantom links in the file.

Check for Real Links in your formulas

  1. Go to the Edit menu and select the Links option towards the bottom (if this option is grayed out, then there are no real formula links)
  2. Run a search (Ctrl + F) for the file that appears in the Edit Links menu
  3. Search all tabs simultaneously by pressing Shift + Ctrl + Page Down and then running the search
  4. You can use the * wildcard character in your search
  5. If you find a real link, change the file reference or delete the link

Check for Defined Name Links

  1. Go to the Insert Name Define menu
  2. Select each Name to see what is in the “Refers To” box
  3. If any of these names is referring to cells in another file, change or delete the Name

Check for Chart Links

  1. If there are any charts in the file, the charts may be linked to cells in another Excel file
  2. Check the data ranges and x-axis labels for each chart to ensure that they are not referencing data in another file
  3. If any of the charts is referring to cells in another file, change or delete the data references

Check for Object Links

  1. Links can exist in Object such as text boxes, auto shapes or drawing objects
  2. On each worksheet, press the F5 key, click the “Special” button and select “Objects”
  3. Delete these objects and then save the file
  4. Close and reopen the file to see if this has solved the problem (you may want to make a copy of your file before performing this step)

Check for Data Validation Links

  1. Links can exist as part of Data Validation criteria
  2. The easiest test is to remove all Validation and see if the link remains (you may want to make a copy of your file before performing this step)
  3. Copy any blank, unformatted cell
  4. Select all cells with Validation; press the F5 key, click the “Special” button and select “Data validation”
  5. Select Paste Special and then select Validation
  6. Save, close and reopen the file to see if this has solved the problem
  7. Repeat steps above for the remaining worksheets in the file

Check for Conditional Formatting Links

  1. Go to the Conditional Formatting Rules Manager
  2. Select “This Worksheet” from the drop down
  3. Check each Rule to ensure they are referencing data in an external file
  4. Amend or Delete the Rule to remove the Link
  5. Repeat steps above for the remaining worksheets in the file

Check for Links on Hidden Worksheets

  1. If you or anyone else has hidden any worksheets in the file, there may be links on these hidden sheets
  2. Check for hidden worksheets by going to the Format Sheet Unhide menu
  3. If this option is greyed out, the file probably doesn’t contain any hidden sheets; however, be sure to also check for Very Hidden Sheets with the Visual Basic Editor
  4. If there are hidden worksheets, unhide them and complete all of the steps above on the sheets that were hidden

If you perform the steps on the previous page and there are no Non-Phantom links, then the workbook probably contains Phantom Links.

There are two different methods to break external links in the Excel worksheet. The first method is to copy and paste as a value method, which is very simple. The second method is a little different. First, we need to go to the “DATA” tab and click “Edit Links,” and find the option to break the link.

Table of contents
  • How to Break External Links in Excel?
    • 2 Different Methods to Break External Links in Excel
      • Method #1 – Copy and Paste as Values
      • Method #2 – Edit Options Tab
    • Things to Remember
    • Recommended Articles

How do I remove external links?

You are free to use this image on your website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Break Links in Excel (wallstreetmojo.com)

Method #1 – Copy and Paste as Values

How do I remove external links?

Now, we must paste them as values.

How do I remove external links?

We can see here that this value does not contain any links. It shows only value.

How do I remove external links?

Method #2 – Edit Options Tab

The second method is a little different. In this method, we must go to the “DATA” tab and click on “Edit Links.”

How do I remove external links?

Now, we can see the below-shown dialogue box.

How do I remove external links?

Here we can see all the available external links. We can update the values, open-source files, and many other things. Apart from all these, we can also break these links.

Now, we will click on the “Break Link.”

How do I remove external links?

As soon as we click on “Break Link,” we may see the dialog box below.

How do I remove external links?

Once we break the External link in ExcelExternal Link In ExcelExternal links are also known as external references in Excel. When we use a formula in Excel and refer to a new workbook, it is the external link to the formula. In other words, an external link is when we give a link or apply a formula from another workbook.read more, we cannot recover the formulas. So, we cannot undo the action once we break the link. It is unlike our “Paste Special” method.

If we wish to break all the links at once, we need to select all the links and click on “Break Links.”

Things to Remember

  • It is dangerous to have links to external sources in Excel. 
  • Once we break the link in Excel, we cannot undo the action.
  • Using *.xl can cover all kinds of file extensions.

This article is a guide to Break Links in Excel. We discuss how to break external links in Excel using Copy and Paste as Value and Edit the Links Option tab, along with practical examples. You may learn more about Excel from the following articles: –

On the Formulas tab, in the Defined Names group, click Name Manager. Check each entry in the list, and look in the Refers To column for external references. External references contain a reference to another workbook, such as [Budget. xlsx].
Change settings for Workbook Links.
In Excel, click the File tab..
Click Options > Trust Center > Trust Center Settings, and then click External Content..
Click the option that you want under Security settings for Workbook Links:.
Fix a broken link.
Open the workbook that contains the broken link..
On the Data tab, click Edit Links. ... .
In the Source file box, select the broken link that you want to fix. ... .
Click Change Source..
Browse to the location of the file containing the linked data..
Select the new source file, and then click Change Source..