WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » External Links in Excel

External Links in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 –

  • Break Links Excel
  • Remove Excel Hyperlinks
  • VBA Hyperlinks
  • Insert Hyperlinks in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download External Links Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More