External Links in Excel

Excel Functions Tutorials

External Links in Excel (Table of Contents)

External Links in Excel
Types of External Links in Excel
What is an External link?
How to Find, Edit and Remove External Links in Excel?

What are External Links in Excel?

In excel we give links to cells to ease out our job. Giving a link to cells means getting the one cell value to another cell by giving a link.

Types of External Links in Excel

Links within the same worksheet.
Links from different worksheet but from the same workbook.
Links from a different workbook

#1- Links within the Same Worksheet

These types of links are within the same worksheet. In a workbook, there are many sheets. This type of links specifies only the cell name.

For Example: If you are in the cell B2 and if the formula bar reads A1 that means whatever happens in the A1 cell will reflect in the cell B2.

Ok, this is just the simple link within the same sheet.

#2 – Links from different worksheet but within the same workbook

These types of links are within the same workbook but from different sheets.

For Example in a workbook there are two sheets and right now, I am in the sheet1 and giving a link from the sheet2.

#3 – Links from a different workbook

This type of link is called external links. Means this is altogether from a different workbook itself.

For Example, if I have I am giving a link from another workbook called “Book1” then first it will show the workbook name, sheet name and then the cell name.

Dealing with External Links

As advanced excel user, I quite often work with a formula to fetch the data from another workbook. If you are sharing the workbook with your subordinates then it is a daunting task to find external links in excel.

In this article, I will show you how to find external links in the excel sheet.

What is an External link?

When we give a link or apply a formula from another workbook then it is called an external link.

If our formula read like the below then it is an external link.

‘C:\Users\Admin_2.Dell-PC\Desktop\: This is the path to that sheet in the computer.

[External Sheet.xlsx]: This is the Workbook name in that path.

Vlookup Sheet: This is the worksheet name in that workbook.

$C$1:$D$25: This is the range in that sheet.

How to Find, Edit and Remove External Links in Excel?

There are multiple ways we canthe find external link in excel workbook. As soon as we open a worksheet we will get the below dialogue box before we get inside the workbook and that is the indication that this workbook has external links.

Ok, let me explain the methods to find external links in excel.

Method #1: Using the Find & Replace Method with Operator Symbol

If there are external, links the link must have included its path or URL to the referring workbook. One this common in all the links is the operator symbol “[“,

Step 1: Select the sheet press Ctrl + F (shortcut to find external links in excel).

Step 2: Enter the symbol [and click on find all.

The results of all the external links will be shown in the same dialogue box. Now you can select all those by holding the shift key convert those formulas to values by using paste special option.

Note: If your data includes the symbol, [then it will also convert to values.

Method #2: Using the Find & Replace Method with File Extension

A cell with external references includes workbook name i.e. workbook name and the type of the workbook is included.

The common file extensions are .xlsx , .xls , .xlsm , .xlb.

Step 1: Select the sheet press Ctrl + F (shortcut to find external links in excel).

Step 2: Now enter .xlsx and click on find all.

This will show all the external link cells.

Method #3: Using Edit Link Option in Excel

This is the most direct option we have in excel. It will highlight only the external link unlike in Method 1 & 2. In this method, we can edit the link in excel, break, or delete and remove external links.

Edit link option in excel is available under the Data Tab.

Step1: Select the cells you want to edit, break, or delete the link cells.

Step 2: Now click on Edit Links in Excel. There are a couple of options available here.

Update Values: This will update the any changed values from the linked sheet.
Change Source: This will change the source file.
Open Source: This will open the source file instantly.
Break Link: This will permanently delete the formula, remove the external link in excel and retain only the values. Once this is done, we cannot undo it.
Check Status: This will check the status of the link.

Note: Sometimes even if there is an external source still these methods won’t show anything but we need to manually check graphs, charts, names ranges, data validation, condition formatting, chart title, shapes or objects.

Things to remember about External Links in Excel

We can find external links in excel by using VBA code. Search on the internet to explore this.
If the external link is given to shapes, we need to look for it manually.
External formula links will not show the results in case of SUMIF Formulas, SUMIFS & COUNTIF formulas. It will show the values only if the sourced file is opened.
If excel still shows external link prompt we need to check all the formatting, charts, validation etc… manually.
Keeping external links will be helpful in case of auto-updating from the other sheet.

You can download this External Links in Excel template here – External Links Excel Template