Find and Remove External Link

What is called External Link in Excel?

Microsoft Excel is used in large communities and for wide purposes. Sometimes the user needs to work in multiple Excel sheets to retrieve calculations. Every time opening multiple sheets is a difficult one. Hence creating an external link is easier. An external link refers to another workbook's data range or cell range.

Why do we need to create an External Link?

Creating an external link is useful in various ways. Some of the points are described below as follows:

• While working with multiple workbooks, referring to the data in another workbook helps create an external link.
• Creating the external link is useful for analyzing and organizing a large amount of data in a worksheet.
• Creating an external link helps to save the user's time, as it is done manually and takes more time to copy and paste.
• Data updation is done easily, as the data change in the source workbook updates in the destination workbook. For example, if the product sale table is present in one worksheet and the chart is currently in another worksheet if the data is changed in the product sale table, it reflects in the worksheet where the chart contains.
• Data sharing is done easily if it is created with external links.

How to Find External Links in Excel?

To find an external link in Excel, the steps to be followed are:

1. To find the external link, open the required workbook. The workbook's name is Company Excel.
   
2. In the ribbon tab, click the option Data.
3. Choose the Edit Link option in the connection group.
   
4. The edit link dialog box appears where it displays the external link present in the workbook.
   
   In the worksheet, North Excel is displayed as the source workbook.
5. The Change Source present in the Edit Link option is to update the external link.
   

Find and Replace Method

The Find and Replace Method is used to find the external link. The steps to be followed are:

1. Usually, the worksheet with external links contains the file name with any of the extensions, namely .xlsx/.xls/.xlb/.xlsm.
2. Open the worksheet, which needs to be checked for external links.
   
3. Press Ctrl+A to select all the cells.
   
4. Click the Find & Select option in the Editing group in the Home tab.
5. There display several options that choose the "Find" option.
   
6. The Find and Replace dialog box will open. In that Find what dialog box enter the extension as *.xl*.
7. Click the Find All option.
   
8. It displays the worksheet name if an external link is present.
   
   Here twelve cells are found with an external reference. If the user uses many external links, it is shown in the Find and Replaces dialog box.

Why remove the external link in Excel?

Based on the calculation, the user sometimes needs to remove the external reference after the calculation process. Some of the reasons to remove external references are as follows:

• To reduce the size of the file, external references are removed. If the workbook contains multiple external links, the file size needs to be bigger and easier to save.
• If the respective worksheet is missed or deleted where the external link is liked with the worksheet, then the external link should be removed.
• To maintain data security and protection, the external link is removed

How to remove the external reference in Excel?

To remove the external reference in Excel, the steps to be followed are as follows:

1. Open the required workbook to remove the external reference.
   
2. Choose the Name Manager in the Defined Names group in the Formula tab.
   
3. The Name Manager dialog box will appear. It displays the list of workbook names with an external reference.
   
4. Select the respective workbook and click delete to delete the required workbook name.
   
5. The particular workbook containing the external reference is deleted. Similarly, to delete multiple workbooks, the steps are repeated.
   
   Here, cell D2 in Sheet 1 is deleted using the external reference.
6. After deleting the required workbook, click Close to exit the Name Manager Dialog box. After finishing, save the changes in the workbook.

How to find links in Excel charts?

The user can find the external link present in the Excel chart by following methods,

1. Open the worksheet containing the required chart or graph.
   
2. Click on the graph title or data series the user wants to check.
   
3. If the external link is present in the chart, it will be displayed in the formula bar if the data is linked to another worksheet. Here the external reference is shown in the formula bar.

How to find External Link in Excel using VBA?

Apart from formulas and functions, one can find the external link in Excel using VBA.

The code used for finding the external link in Excel is as follows:

The above code checks whether the external link is present in the worksheet. If any external link is present in the worksheet, a message will display indicating that a link is present.

The steps to be followed are:

1. Open the worksheet which contains the external data.
   
2. Right-click the Sheet Name and press the View Code option.
   
3. Paste the code in the VBA module.
   
4. Press F5 to run the code.
   

The link will display in the formula bar if the external data is present in the worksheet.

Summary

Finding the external link in the worksheet is an important concept. The user can find the link in the worksheet by following the explained functions and methods in this tutorial.