File & System › 7. Find and/or remove external links...
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
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.
How to check for non-phantom links
Check for Real Links in your formulas
- 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)
- Run a search (Ctrl + F) for the file that appears in the Edit Links menu
- Search all tabs simultaneously by pressing Shift + Ctrl + Page Down and then running the search
- You can use the * wildcard character in your search
- If you find a real link, change the file reference or delete the link
Check for Defined Name Links
- Go to the Insert Name Define menu
- Select each Name to see what is in the “Refers To” box
- If any of these names is referring to cells in another file, change or delete the Name
Check for Chart Links
- If there are any charts in the file, the charts may be linked to cells in another Excel file
- Check the data ranges and x-axis labels for each chart to ensure that they are not referencing data in another file
- If any of the charts is referring to cells in another file, change or delete the data references
Check for Object Links
- Links can exist in Object such as text boxes, auto shapes or drawing objects
- On each worksheet, press the F5 key, click the “Special” button and select “Objects”
- Delete these objects and then save the file
- 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
- Links can exist as part of Data Validation criteria
- 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)
- Copy any blank, unformatted cell
- Select all cells with Validation; press the F5 key, click the “Special” button and select “Data validation”
- Select Paste Special and then select Validation
- Save, close and reopen the file to see if this has solved the problem
- Repeat steps above for the remaining worksheets in the file
Check for Conditional Formatting Links
- Go to the Conditional Formatting Rules Manager
- Select “This Worksheet” from the drop down
- Check each Rule to ensure they are referencing data in an external file
- Amend or Delete the Rule to remove the Link
- Repeat steps above for the remaining worksheets in the file
Check for Links on Hidden Worksheets
- If you or anyone else has hidden any worksheets in the file, there may be links on these hidden sheets
- Check for hidden worksheets by going to the Format Sheet Unhide menu
- 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
- 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
- 2 Different Methods to Break External Links in Excel
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)
2 Different Methods to Break External Links in Excel
Method #1 – Copy and Paste as Values
Now, we must paste them as values.
We can see here that this value does not contain any links. It shows only value.
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.”
Now, we can see the below-shown dialogue box.
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.”
As soon as we click on “Break Link,” we may see the dialog box below.
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.
Recommended Articles
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: –