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’
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
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.
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.
Break/Remove Links Using Edit Links Option
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.
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:
- Click the Data tab
- In the Queries and Connecton group, click on the Edit Link option
- In Edit Links dialog box, it will list all the files to which your workbook has connections.
- Click on the Break Link button
- In the prompt that opens, click on Break Links
- 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
Break Links Using the Copy and Paste as Values Method
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.
Below are the steps to do this:
- Select the cell (or range of cells) that have the external reference
- 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)
- Again, right-clcik on the same selected cell
- Click on Paste Special
- In the Paste Special dialog box, select Values
- Click OK
The above steps would remove the formula/reference in the cell with the static value.
How to Find all Cells with Links to External Excel Files
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:
- 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
- Click the Options button. This will show some additional Find and Repalce options
- 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)
- 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
- In the Look in option, make sure ‘Formulas’ is selected.
- Click on Find All
The above steps would find and show you all the cells that contain external references.
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:
- As a part of Defined Named Range
- Used in Chart Title or Chart Data Series
- Used in Objects such as shapes and text boxes
Other Excel tutorials you may also find helpful:
- How to Remove Page Breaks in Excel? Quick and Easy!
- How to Concatenate with Line Breaks in Excel? 3 Easy Ways
- How to Remove Hyperlinks in Excel (Easy Step-by-Step)
- How to Extract URL from Hyperlinks in Excel (Using VBA Formula)
- How to Convert a CSV File to Excel?
- How to Make Excel File Read Only
- Circular References in Excel – How to Find and Fix it!
- How to Link Cells in Excel (Same Worksheet, Between Worksheets/Workbooks)