External Links In Excel

What Are External Links In Excel?

External Links in Excel are references to cells, ranges, or defined names in different workbooks. And while adding external links in a worksheet is straightforward, locating them is challenging.

Users can use external links to merge data from multiple workbooks, build different views of the Excel data, and streamline massive, complex models.

For example, the image below shows two workbooks, Jan2022_Product Revenue_Data, and 2022_Total Monthly Product Revenue_Data.

Suppose the requirement is to update the total product cost from the Jan2022_Product Revenue_Data workbook as the total revenue for January in the second workbook.

Then, we can check External Links in Excel options to achieve the desired output.

We must update the total product revenue for January in cell B2 of the 2022_Total Monthly Product Revenue_Data workbook using the data in cell D8 of the first workbook. Thus, we use an external link or reference to cell D8 in the worksheet of the first workbook in the target cell.

The external reference in cell B2 includes the source workbook and worksheet names and an absolute reference in excel to the source cell containing the required data.

Likewise, we can update the revenue figures for the remaining months from the corresponding workbooks using external links. And clicking the target cells will allow us to view External Links in Excel, as shown above.

Types Of External Links In Excel

Excel offers three types of external links:

1. Links from the same worksheet.
2. Links from a different worksheet within the same workbook.
3. Links from a different workbook.

#1 – Links Within the Same Worksheet

Suppose the requirement is to update a cell with the value in another cell of the same worksheet. Then, we can use links containing only the cell address for referencing the cells containing the source data.

For example, the following worksheet contains employees’ first and last names, with which we must update their full names in column C.

The steps to use the links referencing the cells containing the source data in the target cells within the same worksheet are as follows:

Step 1: Select cell C2, enter the formula =A2, and press Enter.

Step 2: Select cell C3, enter the formula =A3&” “&B3, and press Enter.

The Formula Bar shows the cell addresses referenced in the target cells. And any changes made to the data in the referenced cells will reflect in the target cells.

For instance, we change the last name in row 3. Then, the cell C3 value gets updated automatically.

#2 – Links from Different Worksheet but Within the Same Workbook

Suppose the requirement is to update values in one worksheet using data from different worksheets within the same workbook. Then, we can use links containing the worksheet name and cell address for referencing the cells in the corresponding worksheets containing the source data.

For example, consider the two worksheets in the Employee Names_Data workbook, Employee_ID, and Employee_Workstation_ID.

The steps to use links to reference cells of the Employee_ID worksheet to update the employee IDs in column C of the Employee_Workstation_ID worksheet using the Employee_ID worksheet data are,

Step 1: Select cell C2 in the Employee_Workstation_ID worksheet, and enter the VLOOKUP() formula =VLOOKUP(A2,

Next, go to the Employee_ID worksheet, and select the table_array range, A1:B11. And Excel will show the reference or link as shown below: =VLOOKUP(A2,Employee_ID!A1:B11

However, as the table array range should remain constant in all the target cells, make the cell references absolute. And add a comma as =VLOOKUP(A2,Employee_ID!$A$1:$B$11,

Next, complete the VLOOKUP excel formula as =VLOOKUP(A2,Employee_ID!$A$1:$B$11,2,

And after updating the range_lookup argument as an exact match, close the brackets. The complete formula will be: =VLOOKUP(A2,Employee_ID!$A$1:$B$11,2,FALSE)

Step 2: Press Enter, then the formula gets updated in the target cell in the Employee_Workstation_ID worksheet, and we can see the required data, as shown below.

Step 3: Using the fill handle in excel, update the formula in cells C3:C11.

#3 – Links from A Different Workbook

Suppose the requirement is to update values in a worksheet using data from a different workbook. Then, we can use links containing the workbook name, worksheet name, and cell address to reference the cells in the worksheet in the corresponding source workbook.

For example, we have two workbooks, Employee Names_Data, and Employee_Contact Numbers.

The requirement is to update the employee contact numbers in column C of the Employee Desig_Contact_Number worksheet in the Employee Names_Data workbook. And we must use the data provided in the Emp_Contact Numbers worksheet of the Employee_Contact Numbers workbook to update the target cells.

Then, the steps are as follows:

Step 1: Select cell C2 in the Employee Desig_Contact_Number worksheet of the Employee Names_Data workbook and enter the VLOOKUP() formula =VLOOKUP(A2,

Next, go to the Emp_Contact Numbers worksheet in the Employee_Contact Numbers workbook and select the table_array range as A1:B6.

Excel will show the selected range with the workbook name, worksheet name, and cell addresses referenced. And the reference will always be absolute when referencing cells from different workbooks as

=VLOOKUP(A2,'[Employee_Contact Numbers.xlsx]Emp_Contact Numbers’!$A$1:$B$6

And then, enter a comma and complete the VLOOKUP() formula as explained previously.

=VLOOKUP(A2,'[Employee_Contact Numbers.xlsx]Emp_Contact Numbers’!$A$1:$B$6,2,FALSE)

Step 2: Pressing Enter will execute the formula in the target cell in the Employee Names_Data workbook.

Step 3: Using the fill handle, enter the formula in cells C4:C6.

How To Find, Edit, And Remove External Links In Excel?

We can find, edit and remove or delete External Links in Excel using the following methods:

1. Find & Replace method with Operator symbol.
2. Find & Replace method with File Extension.
3. Edit link option in Excel.

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

The below image shows a workbook containing external links. And when we open workbooks containing external links, we will see the following warning message.

And if the links are trustworthy, click Update to proceed.

In this example, the worksheet Student_Status in the Student_Promotion_Status workbook references data from an external source. And the external data source is the Sheet1 worksheet in the Student_Score workbook, stored in the path mentioned in the Formula Bar.

The steps to determine all the cells containing the external references to view External Links in Excel worksheet Student_Status are as follows:

Step 1: Click Home – Find & Select – Find or press the shortcut keys Ctrl + F to open the Find and Replace window.

Step 2: Enter the Operator symbol, ‘[‘, in the Find what field in the Find tab in the Find and Replace window. And click Options.

Set the following Options settings to search the entire workbook, and click Find All.

Excel will list all the cells containing the external references in the Find and Replace window. The reason is that the external links include the workbook names of the worksheets containing the cells referenced enclosed in Bar brackets (‘[ ]’).

Thus, in this way, we can find and check External Links in Excel sheets.

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

Typically, one would use excel extensions such as .xlsx, .xls, and .xlsm to save Excel files. And when we use external links in a worksheet, the external source files’ extensions will appear in the target cells.

Thus, we can use the Find & Replace option from the Home tab in the workbook, where we must find the external links. And search for cells containing terms starting with the phrase “.xl”.

For example, the below workbook, Monthly Net Income_Data, includes a worksheet, Monthly_Net_Income, which references data from external sources. The two external sources are the Savings worksheet in the Monthly Savings_Data workbook and the Expenses worksheet in the Monthly Expenses_Data workbook.

The steps to find all the cells in the Monthly_Net_Income worksheet containing external links are as follows:

Step 1: Use the excel keyboard shortcut Ctrl + F to open the Find and Replace window.

Step 2: Enter the phrase, “.xl*”, in the Find what field in the Find tab in the Find and Replace window, and click Options.

And set the following Options settings, and click Find All.

Excel will list all the cells in the workbook containing the file extensions beginning with “.xl”.

However, in scenarios where we have to delete External Links in Excel, we should be careful while selecting the cells to remove the external links or replace them with values. The reason is that if the worksheet has cells with data containing ‘[‘ or “.xl” and the date is other than external links. Then, the above-mentioned methods will include those cells also in the found list.

Method #3 – Using Edit Link Option in Excel

We can use the Edit Links option from the Data tab to delete or break External Links in Excel.

The option will appear greyed out if the workbook contains no external links.

For example, the Stock Sale_Data worksheet in the Stock Sale_Data workbook contains external links to cells in the Stock_Volume worksheet. And the Stock_Volume worksheet is in a different workbook, Stock Volume Traded_Data.

Thus, the Edit Links option in the Data tab appears enabled.

The procedure to use the Edit Links option is shown below,

Select the data range D2:D6 to edit, delete or break External Links in Excel cells.

The Edit Links window will open.

We can now choose the required options to manage the links.

• Update Values: The option updates any changed values from the linked or referenced worksheet.
• Change Source: The option changes the source file location path.
• Open Source: The option opens the source file instantly.
• Break Link: The option permanently deletes the formula, removes the external links, and replaces the formulas with the values. Also, one cannot undo the changes.
• Check Status: The option checks the link status.

Let us consider a few of the options.

#Change Source Option

Click the Change Source option to change the source file location path.

The Change Source window will open, showing the source file name on the top for which we want to change the file location path. And once we update the source file location path, click Open.

And the Location in the Edit Links window gets updated.

#Check Status Option

We shall check the link status. And for that, click Check Status.

The above action shows the status as OK, as the link has no errors.

#Break Link Option

If we want to break the worksheet’s external links, click the Break Link option.

The following warning message will appear as the option permanently converts the formulas into values.

And clicking the Break Links option will replace the formulas with the values, as shown below.

And the Edit Links window does not show the external source file name. Finally, once done, click Close.

Important Things To Note

• The External Links in Excel worksheet to cells in different workbooks will include absolute cell references.
• If the worksheet contains objects, charts, data validation, or conditional formatting, then when checking the worksheet for external links, check the above-mentioned aspects manually. The reason is that the external links in such items will not show in the Find and Replace search results.
• The external links in functions such as SUMIF in excel and COUNTIF in excel will work only when the source file is open.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the External Links in Excel, with its formula and examples. We can download the template here to use it instantly.

Recommended Articles

This has been a guide to External Links In Excel. Here we learn to find, edit, remove links from different workbooks, examples & downloadable excel template. You may learn more from the following articles –

• Break Links In Excel
• Convert Numbers To Text In Excel
• Compare Two Lists In Excel