Finding & Remove External Links in Excel (Table of Contents)
Finding External Links in Excel
Well, have you faced this situation where, when you open an excel, it displays a dialog box asking whether you want to update external linked information and it becomes frustrating particularly when you do not know what information is linked and where exactly it is linked externally in your Excel workbook? Well, if you want to know or get rid of links in your workbook, there are several things you can try for.
As there is no programmed way for finding these links or all the external references which are present in a workbook, but there are many manual methods which can be used for finding these references.
The recommended method for finding these external references is to look for all the references which have the .xl partial file extension as an excel sheet you have linked to will have that excel sheets file name in the link with .xl* file extension (such as .xlsx, .xls, xlsm)
How to Find External Links in Excel?
For finding these external references you need to look in objects, defined names, formulas, objects, chart titles and chart data series.
#1 – For Finding External Links Used in Excel Formulas
- Use Ctrl+F keys to start the Find and Replace dialog box.
- Click Options.
- Enter .xl in the Find what box, click Workbook in the Within box then and Formulas in the look in box then, click Find All.
- In the list box displayed, see the Formula column for formulas that contain .xl. For this example, Excel has found multiple cases.
- For selecting the particular cell with an external reference, in the list box, click on the cell address link for that row.
#2 – For Finding External Links Used in Defined Names
- Go to Formulas tab then, Click on Name manager in defined names group.
- Check for each entry in the list and for external references look in the Refers to the column. External references contain a reference to another workbook.
#3 – For Finding External Links Used in Excel Objects
- In the home tab of the Excel window, in editing group, under find & select option, click on Go To Special option.
- Select objects & click ok. All objects on the active worksheet will be selected. For moving between each of the selected objects, press the Tab key
- For a reference to another workbook look in the formula bar of an image, such as [Budget Excel Template.xlsx].
#4 – For Finding External Links Used in Chart Titles
- Click on the chart title of the chart that you want to check for external links. In the formula bar, look for a reference to another workbook, such as [Chart.xlsx].
#5 – For Finding Links Used in Excel Chart Data Series
- Select the chart that you want to check. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box then, click the data series that you want to check.
In the formula bar, look for a reference to another workbook, such as [Types of Charts Excel Template.xlsx] in the Series function.
#6 – For Finding External Links Using Edit Links Option
Following steps can be used for finding external links using Edit Links Option:
- Go to the Data Tab. In the Connections group, click on Edit Links.
- It will open the Edit Links dialog box which will be having a list of all the worksheets that are being linked.
- Click on Break Links excel option to convert all linked cells to values.
Things to Remember About Find Links in Excel
#1 – Things to Remember While Breaking Links in Excel
Once you break a link to the source worksheet of an external link, all the formulas which are using the values in the source worksheet are converted to their current values.
For example, once you break the link to the external reference =SUM([Budget.xls]Annual!C15:C30), the SUM formula will be replaced by the calculated value. As this action cannot be undone, it is better to save a version of the destination worksheet as a backup.
#2 – Things to Remember While Updating all the External Links
You can decide whether to update links automatically at startup or after startup.
- Go to the Data Tab. In the Connections group, click on Edit Links then, click Startup Prompt and Select the option that you want.
#3 – Things to Remember About Controlling Links to Other Worksheets
When the source worksheet and destination worksheet are open on the same computer, links get automatically updated.
When you open a destination worksheet and your source worksheet is not open, a dialog box of trust bar may pop up asking whether to update the links.
You can control the trust bar as to whether the Trust Bar alerts you or whether to update all links when the trust bar alert does not appear. You can also update only selected links if the worksheet contains more than one link.
For manually updating all links or none of the links in a worksheet
- Close all worksheets.
- For updating uniformly, all the source worksheet need to be opened as updates will not be uniform if one source worksheet is open and others are closed.
- Open the worksheet that comprises all the links.
- For updating the links on Trust Bar
- click Options
- Then Click Enable this Content.
For manually updating only some of the links to other worksheets
- Close all worksheets.
- Open the worksheet that comprises the links.
- Click on the Data tab and in the Connections group, click Edit Links.
- In the Source list displayed, click on the linked object that you want to update.
- For selecting multiple links, hold Ctrl key and click each linked object.
- For selecting all links, use Ctrl+A key.
- Click Update Values.
This has been a guide to Finding Links in Excel. In this article, we learn how to find external links in objects, defined names, formulas, chart titles and chart data series with practical examples. You may learn more about excel from the following articles –