Linking one Excel workbook to another is common, but sometimes, you might have trouble finding the links even if Excel says they exist. Unfortunately, there’s no automatic way to locate all the external links in a workbook, but you can use a few manual methods. You should check formulas, defined names, objects like text boxes or shapes, chart titles and data.
In this article, we have discussed various methods to help you find the external links in an Excel worksheet.
How to find links in formulas?
Whenever you link to another workbook, the link will include the other workbook’s name with a file extension like .xls, .xlsx, or .xlsm. So, a good way to start is by looking for references that include .xl of the extension. To do that follow the steps below.
Step 1: Press Ctrl + F keys to launch the Find and Replace dialogue box in the Excel worksheet. In the dialogue box click on the Options button.
Step 2: In the Find What box, enter .xl, in the Within box, select Workbook, in the Look in box, select Formulas and then click on the Find All button.
Step 3: A list of cells with the formulas with the external links that contain .xl will be shown in the results. To select the cell with an external link, click on the cell address in the result.
Also read: How to enable macros in Excel?
How to find links in the defined names?
To find links in the defined names, follow the steps below.
Step 1: Click on the Formulas tab in the toolbar ribbon and then click on the Name Manager button in the Defined Names section. You can also press Ctrl + F3 keys to open the name manager directly.
Step 2: Check the Refers to box located at the bottom of the Name Manager window.
How to find links in objects?
To find external links in objects like shape and text boxes, follow the steps below.
Step 1: Press Ctrl + G to open the Go TO dialogue box and click the Special button.
Step 2: Select the Objects option and click the OK button.
Step 3: It will show you the objects with the external link if an object is linked to a cell, then you can check for the external link in the formula bar, and if it is lined to a text box or a file, then hover over the object to check the link.
How to find links in the chart title and chart data?
Finding the external link in the chart title is simple; you just have to click on the chart title or the data series you want to check.
Also read: How to remove table formatting in Excel?