You have probably experienced it… you open an Excel file and get a message that the file is linked to one or more external sources.
If this was not expected, it could be that the file contains unintentional links which you may wish to review and remove. This is not always an easy task as there are at least six ways an Excel file can be linked to another source and not all of them allow easy removal. Let’s go through them in turn.
- Formulas
This is the easiest one to find and fix. Go to Data, Edit links… there you can see the files linked to and if the links are simply in formulas, you can click on “break links” to replace the formulas with values.
Warning: You may not want to replace formulas with values. E.g. you copied a formula from another version of the file, and this was linked to a sheet called “inputs”. The copied formula in the new file is linked to the sheet “inputs” in the old file. What you really want is for the formula to link to the sheet called “inputs” in the current file. You can search for the file name (in this example “Source file”) in the spreadsheet, then review and amend the formulas found to remove the superfluous path and file name of the old file.
If the formulas are not simple links to single cells in the source file but are more complex e.g. using SUMIFS then “break links” doesn’t always work. It also doesn’t work if one or more of the other linkage types exist, so I cover these below.
- Range names
These are also easy to find. Go to Formulas, Name Manager.
There you can see if any range names refer to cells in other spreadsheets and edit or delete them accordingly. If necessary, search for the range name in the spreadsheet to find where it is used and amend or replace the range name as appropriate.
Other linkages can arise if you copy content from another spreadsheet. The content stays linked to the data in the original spreadsheet… a link to an external file.
- Chart data
- Pivot table data
- Conditional formats
- Data validation
- Objects e.g.
(i) a shape containing text linked to a cell
(ii) a data slicer for a pivot table or a table
(iii) a VBA control such as a button linked to a macro
These can be tricky to find, so try to work smarter not harder. What have you copied or otherwise changed in the spreadsheet since you opened it last time and didn’t get a message that the file was linked to one or more external sources? Then this is likely to be the cause of the link and can make it easier to find. Review those elements and you can maybe find and amend / remove the link as appropriate.
If that doesn’t help you, then you can find and review each element (e.g. each graph) in turn. Charts and pivot tables are easy to identify. You can find conditional formats and data validation using F5 (goto), special, then select conditional formats or data validation and OK.
The relevant cells on the selected sheet are then marked, and you can review them: Home, Conditional Formatting, Manage Rules or Data, Data Validation. Repeat for other sheets.
The brute force method
If that fails, and your spreadsheet is large then a brute force method may be the only way to identify the links. Very important: Make a back-up copy of your spreadsheet file for safety. Also make a copy of it to work on. You will have to partially destroy this file, so it is important to work on a copy only. In the copy file, start by selecting one or more sheets and either move them to a new workbook or alternatively delete them: right-click on a tab name and select 'Move or copy' or 'Delete'. Then save the file. See if the links remain. Sometimes it is necessary to close and reopen the file to force Excel to review the links.
- If the links are gone, they were in one or more of the sheets you moved or deleted. Move some sheets back into the main file or make another copy of the original Excel file and repeat the process, but delete fewer sheets. Do this until you have identified the offending sheet.
- If the links are not gone, move or delete more sheets then save and if necessary close and open the file until you identify the offending sheet.
Now you know a sheet with one or more links. You can then review the graphs, pivot tables, conditional formatting and data validation on this sheet. If the sheet is very large, you may need to move or delete sections of it (like the sheets move/deletion approach above) to narrow down where the external links are.
Once you find the link, open the original file and amend or remove the link there. Then save, close and reopen the file. If Excel still reports that there are external links, you will have to repeat the above steps in a new copy of the original file until you find and amend or remove all the links in the original file.
Good hunting!
-->
