How to find external links in Excel
While working with Microsoft Excel, it is a common practice to use external links and references in the active workbook. However, sometimes we need to find out which cells are being linked externally and which are using external references to update these or for any other reasons.
In this tutorial we’ll learn how to find the external links in the active workbook. There are 5 ways in which the external link can be found in an active workbook which are as follows;
- Use Find Command to Search External Links Used in Formulas
- Use Edit Links Command to Find and Remove External Links in Excel
- Use Name Manager to Find Named Range with External Links
- Find External Links in Series Chart in Excel
- Find External Links in Pivot Table in Excel
Method 1 – Use Find Command to Search External Links Used in Formulas
Step 1 – Use Find to find external links
- Press CTRL+H to open the Find and Replace dialog box.
- In the Find what box, type “[”.
- Click Options.
- Choose Sheet for Within options.
- For Search and Look in options, select By Rows and Formulas respectively.
- Press Find All. Then press CTRL+A to select all those cells which have external links.
Method 2 – Use Edit Links Command to Find and Remove External Links in Excel
Step 1 – Use Edit Links option on Data Tab to find external links
- Go to the Data tab on the main list of tabs.
- Now click on the Edit Links option from the Queries & Connections group of commands.
- A dialog box named Edit Links will open up.
- We’ll see the external link present in the workbook here.
Method 3 – Use Name Manager to Find Named Range with External Links
Step 1 – Use Names Manager option on Formula Tab to find external links
- Go to the Formulas tab first.
- Select Name Manager from the Defined Names group of commands.
- In the Name Manager dialog box, you’ll notice the external links present in the workbook. The reference address of the named range will be found under the Refers To tab.
Method 4 – Find External Links in Series Chart in Excel
Step 1 – Use Chart Series in the charts to find external links
- To find the links used inside the charts you just need to select any of the series and the link will be visible in the formula bar.
Method 5 – Find External Links in Pivot Table in Excel
Step 1 – Use Change Data Source in Analyze PivotTable to find external links
- To find the links used inside the pivot table, click any cell of pivot table. Then go to Analyze PivotTable tab.
- Click on the Change Data Source option. A new dialog box will open showing the presence of any external data sources, if present.