Get rid of those annoying links

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.

Gary Knott | how2excel - Excel Schulungen annoying links

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.

  1. 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.

  2. Range names 

    These are also easy to find. Go to Formulas, Name Manager.

    Gary Knott | how2excel - Excel Schulungen annoying links

    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.

    The next four linkage types 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.

  3. Graph data
  4. Pivot table data
  5. Conditional format
  6. Data validation

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. Graphs 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.

Gary Knott | how2excel - Excel Schulungen annoying links

The relevant cells are then marked, and you can review them: Home, Conditional Formatting, Manage Rules or Data, Data Validation.

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 deleting a few sheets then save the file. Close and open it to see if the links remain. Closing and reopening the file is often necessary to force Excel to review the links.

  • If the links are gone, they were in one or more of the sheets you deleted. Make another copy of the original spreadsheet file and repeat the process but delete less sheets until you identify the offending sheet.
  • If the links are not gone, delete more sheets then save, close and open the file until you identify the offending sheet.

 
Now you know the sheet with the link(s). 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 delete sections of it (like the sheets 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!