Home » Excel, VBA & Power BI » Excel Resources » External Links in Excel

External Links in Excel

Jeevan A Y
Article byJeevan A Y
Dheeraj Vaidya, CFA, FRM
Reviewed byDheeraj Vaidya, CFA, FRM

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.

External Links 1

‘C:\Users\Admin_2.Dell-PC\Desktop\: 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
You can download this External Links Excel Template here – External Links Excel Template

#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.

External Links - Types 1

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.

External Links - Types 2

#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.

External Links - Types 3

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.

External Links 2

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).

External Links 3 (Find and Replace)

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

External Links 3 (Find and Replace) 1

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 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).

Find and Replace Method 1

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

Find and Replace Method 2

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.

method

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

method 1.

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

method 2

  • 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 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>