External Links in Excel

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:UsersAdmin_2.Dell-PCDesktop: This is the path to that sheet on 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 link 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.

#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 “[“

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

A cell with external references includes a workbook name, i.e., workbook name, and the type of 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.

The 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 formattingCondition FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more, chart title, shapes, or objects.

Things to Remember

• We can find external links by using VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. 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 ExcelSUMIF Formulas In ExcelThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. read more, SUMIFS & COUNTIF formulasCOUNTIF FormulasThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more. It will show the values only if the sourced file is opened.
• If excel still shows an 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 –

• Break Links Excel
• Remove Excel Hyperlinks
• VBA Hyperlinks
• Insert Hyperlinks in Excel