How to Break Links To External References in Excel?

by

Often when you inherit Excel files from other people, they can have links to other external Excel files and ranges.

Many times, you don’t need to keep these links to these external references and would want to get rid of them.

In this tutorial, I will show you two really easy ways you can use to break links to external references in Excel.

Let’s get started!

What is an External Reference?

An external reference means that you refer to a cell or range of cells in an Excel file other than the active file (the one in which you’re using the formula).

To give you an example, below is an external reference that refers to cell A1 in a file named ‘Example File.xlsx’

External link to a cell

Just like you can refer to the cells and ranges in the same workbook, you can also do it for external workbooks.

You can also refer to named ranges and Excel tables in external Excel files. For example, below I have referred to a cell that has been named as ‘ExampleName’ in the Example File.xlsx file

External Link to a Named Range

While these external references work just like the regular references that we use in the same worksheet or workbook, the issue can come when you don’t have access to the external file to which you have references.

For example, suppose I send my current file to a colleague and he/she doesn’t have access to Example File.xlsx

In such a case, my colleague will see a prompt that will tell them that the links to these external files can’t be established and updated.

Workbook contains external Links prompt

So they can either continue without updating the links or they can break these links.

In many cases, there is no need to keep these links live, so it’s a good idea to break these links (so that you stop seeing the prompt every time you open the workbook).

Now, let’s see two simple ways to break these external links in Excel.

When you open an Excel file that already has links to external Excel files, you will a prompt as shown below as soon as the file opens.

Workbook contains external Links prompt

This tells you that there are links in the file that Excel can not automatically update (as the file may be closed or renamed or moved/deleted from the current location), so it’s asking you whether you want to update these or not.

Below are the steps to use the Edit link option to break all the links in the workbook:

  1. Click the Data tab
  2. In the Queries and Connecton group, click on the Edit Link option
Click on Edit Links
  1. In Edit Links dialog box, it will list all the files to which your workbook has connections.
  2. Click on the Break Link button
Click on Break Links
  1. In the prompt that opens, click on Break Links
Break Links Prompt in Excel
  1. Close the Edit links dialog box.

The above steps would break all the links and remove the external references with static values. In case you had formulas that were using external references, those would also be replaced with the formula result.

Remember that the changes are not reversible, so if you break the links then you won’t be able to get those back.

So, it’s always a good idea to make a backup copy before you decide to go ahead and break the links.

Note: While this method would take care of breaking the links in most cases, there is a possibility that your file has some links that are not identified and removed by this method. In such as case, you might have to use some external third party add-in to do this 

Also read: Excel Hyperlink Cannot Open the Specified File – Fix!

The easiest way that I use to break links is to simply copy the cell that has the external reference and then paste it over itself as a value.

This is also the technique I use when I need to remove the formulas and keep the data, and it works just as well to break external links.

Suppose I have a dataset as shown below, where I have an external link in cell A1 that I want to break.

External link to a cell

Below are the steps to do this:

  1. Select the cell (or range of cells) that have the external reference
  2. Copy the cells (you can use Control + C or Command + C keyboard shortcut, or you can right-clcik on the selected cells and then click on Copy)
Copy the cell that has external links
  1. Again, right-clcik on the same selected cell
  2. Click on Paste Special
Click on Paste Special
  1. In the Paste Special dialog box, select Values
Select Values option
  1. Click OK

The above steps would remove the formula/reference in the cell with the static value.

You can use the Find and Replace functionalities to quickly find all the cells that have links to external excel files.

Below are the steps to select all the cells that have external link references:

  1. Hold the Control key and then press the F key (or Command + F if using Mac). This will open the Find and Replace dialog box
Find and Replace dialog box
  1. Click the Options button. This will show some additional Find and Repalce options
Click on the Options button
  1. In the ‘Find what’ field, enter .xl (as shown below). Doing this will find all the cells that have the string .xl (which would include all references to external Excel files – including XLSX, XLS, XLSM and XLSB)
Enter XL in the Find what field
  1. In the Within drop down options, select ‘Workbook’. This will find all the cells with external references in the entire workbook. If you only want to do this for the active sheet, keep the ‘Sheet’ option selected
Make sure Within Workbook is set
  1. In the Look in option, make sure ‘Formulas’ is selected.
Look in Formulas options need to be selected
  1. Click on Find All

The above steps would find and show you all the cells that contain external references.

References of all the found cells with external Links

While this technique allows you quickly find all the cells with external links, you can not break all the links in one go using the paste special technique I covered earlier (unless these are all as a contiguous range).

If you have these cells scattered, you will have to do this one by one.

This method will only be able to find external links that are there in cells and used in formulas.

But there is also a possibility of external links being used in the following scenarios:

Other Excel tutorials you may also find helpful:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment