Trying to break excel links but excel can not break links? It may be because you have referred external file in the data source of excel objects or validations. Excel warning messages become a headache if excel cannot break links when tried.
In normal cases, it is easy to break links in excel files by just Alt + A + K or by going to Data tab-> Queries & Connection manually. Then you have to select the wanted link to break and press the “Break Link” button. This option allows you to break visible known normal external links.
But What if excel won’t break external links even after trying this method?
Why Excel can not break links?
Excel fails to break external links if they are used for Data Validation or defining names in Names Manager.
In normal cases, if external links are placed in excel date cells you can select and see them, so you can delete, break or edit them manually. But if they are used in the following excel features,
- Data Validations
- Defined Name range in Name Manage
- Charts
Excel won’t break them using the Edit link option, you have to break them manually. Hence, if links are from the missing files every time you open an excel file following error message will keep appearing.
How to break links in excel?
To break links you have to look for links used in the formula or data source of excel features. If you found one, verify is it the same which causing the problem, and if “yes” then fix it.
1 Break Excel Links used in Data Validation
If external links are used in Data Validation or in Names Manager they are not available to access through applied data cells. It becomes more difficult when you don’t know exactly which cells of rows and columns are used in data validation.
Follow these steps,
Step-1: First identify & select the data range (i.e cells, columns, rows) that have applied data validation referring to external files. If you don’t know then simply select all (Ctrl + A)
Step-2 : Then go to Data tab-> Data Validation (Alt + A + V+V).
If your selection is perfect then it will show you referring file address as shown in the above screenshot. Otherwise, it will not and you have to select a large cells range (i.e select all).
Step-3: Then click on “Clear All”. It will clear all data validations from selected cells and so links.
Note: Clear All will remove all applied data validation from the selected range. You have to again set up validation to the required cells.
2 Break Excel Links used in Name ranges
If external links are used in defining names in Names Manager you can delete or edit them by editing names only. You can not break these links from the Edit Links dialog.
Follow these steps,
Step-1: Goto Formula tab->Name Manager (Alt + M + N)
Step-2: Look for a defined name with an external link in the “Refers To” column
Step-3: Edit/Correct or delete defined Name range
Note: Deleting a defined Name will cause an error in cells that contain the same Name as part of the formula.
3 Break Excel Links used in Charts
Moving excel charts to external files cause the creation of phantom links (links to external file you haven’t create). You have to look for such link in data source and code of excel charts if there is any.
Follow these steps,
Step-1: Select excel chart then Right click->Slect Data.. source
Step-2: Look for links with the external file name
Step-3: Edit/Correct or delete defined source
If your links are in VBA code, select item and press Alt + F11 to open VBA project manager. here you can check if there is any external link that is causing the problem.
FAQ
Break link in excel not working, what to do ?
If break link in excel is not working then you have to search and break links manually. Look for the links in data validation, name manager, and data source of charts. You can also use paid link break addon.
How do I force break links in Excel?
If excel cannot break links normally using the edit link option you can break it forcefully using the manual search and edit method. Look for phantom links in various excel features data sources. Such as data validation, charts, and name manager.
How do I remove hidden external links in Excel?
Some external links are hidden in the data source of data validation, names, and charts. You can remove hidden external links in excel by looking inside sources of charts, names, and data validations.
How do I remove a ghost link in Excel?
Ghost links also called phantom links, they are hidden in the data source and VBA code of excel object. You can remove ghost links by manually searching and editing. Check and verify data source of excel objects, formatted and validation applied cells.
How do you fix external links in Excel?
You can fix external links in excel by breaking them. If edit links (Alt+A+K) not working, look for external links in the data source of applied data validations, excel charts, and names manager. Also, look in the VBA code if applicable.
Conclusion
Excel cannot break links if they are used in the data sources of excel features and objects. Links may become a ghost or phantom links if you move sheets, objects (such as charts) to another sheet. It can also cause problems when you move, edit, or remove referred excel files. You have to reconfigure and correct reference formulas and data sources to fix such link-related issues.
