Find, modify and break links to an Excel workbook

sharyn baines

Learn how to find, modify and break links to an Excel workbook.

Linking is great, until you no longer need it. Then it can be a bit of a pain.

A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data is within your workbook and another is actually breaking the link.

This workbook contains links

The message ‘This workbook contains links’ is shown when you open a workbook that has links to a closed (as in, nobody currently has it open to work on it) external source file.

Excel External LInk Message

You can put up with seeing this message for so long…then it starts to become annoying. You also start asking yourself, “should I be clicking Update or Don’t Update”.

You will eventually want to locate where the links are to establish what the source data is and if you still need the links in your workbook.

Finding linked data within your workbook

Links aren’t obvious and it can sometimes be frustrating trying to locate them within the worksheet. To quickly locate linked cells try the options listed below.

Option 1 – Use the Show Formulas button

From the Formulas tab, in the Formula Auditing group, click on Show Formulas. All cells containing formulas will now display the formula instead of the value, allowing you to easily identify those cells that are linked to other worksheets.

Option 2 – Try searching for the filename

To quickly locate links to external files, press CTRL + F to display the Find dialog box. In the Find what box type .xls or just .xl if you want to ensure you include other types of Excel files in your search. Make sure the Look in option is set to Formulas. If you can’t see this option click Options. Click Find All and all formulas containing links will be listed.

Excel Find Linked cells

Option 3 – Check additional elements

Check that there are no links to external files in Conditional Formatting or hidden worksheets.

To check in Conditional Formatting, from the Home tab click Conditional Formatting and then click Manage Rules. From the Show Formatting Rules for drop-down box select This Worksheet. Check all of the rules and delete any that are connected to external files.
To unhide hidden worksheets, right-click any sheet tab, select Unhide and then check for external links.

Modifying and breaking links to a source workbook

1. Open the dependent workbook.

2. From the Data tab, in the Connections group, click on Edit Links.

Excel Linking

3. In the Edit Links dialog box click Open Source to open the linked source workbook, Change Source to select the name of a new source workbook or Break Link to break the link to the source workbook.

4. Click Close. If you have broken the link all cells that previously held link formulas will now be shown as values.

Note: when you change the source file, cell references formats in the new source file will match those of the old source file; otherwise Excel will display a #REF error. Make the necessary adjustments to the new source file before attempting once again to use it, e.g. if the referenced cell is being used in a calculation ensure it does not contain text.

Tip: if you open a dependent worksheet and your formulas have been replaces with #VALUE errors try closing the dependent workbook, opening the source workbook and then opening the dependent workbook again.

Breaking links using Paste Values

Links between worksheets can be removed using Paste Values.

1. Open the dependent workbook and then select the range containing the linked data. BTW, you can select the entire worksheet if you want (CTRL + A).

2. Copy the data.

3. Leave the linked range selected and then do one of the following.

From the Home tab, in the Clipboard group click the Paste drop-down arrow. Select Paste Values from the list.
Right-click and select Paste Values.

This replaces the external reference with the values of the cells, therefore breaking the link to the source data.