What are External Links in Excel?
External links are also known as the external references in excel, when we use any formula in excel and refer to any other workbook apart from the workbook with formula then the new workbook being referred to is the external link to the formula, In Simple words when we give a link or apply a formula from another workbook then it is called an external link.
If our formula reads 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.
Types of External Links in Excel
- Links within the same worksheet.
- Links from different worksheets 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 sheet1 and giving a link from sheet2.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
#3 – Links from a different workbook
This type of link is called external links. This 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.
How to Find, Edit and Remove External Links in Excel?
There are multiple ways we can find external links in the 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).
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).
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 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 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
- We can find external links 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 the case of SUMIF Formulas in Excel, 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.
Recommended Articles
This has been a guide to External Links in Excel. Here we discuss types of links and dealing with external links, how to find, edit and remove External links in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
