How to Find External Links in Excel? (Step by Step)
For finding these external references you need to look in objects, defined names, formulas, objects, chart titles and chart data series.
#1 – Steps to Find External Links Used in Formulas
- Step 1 – Use Ctrl+F keys to start the Find and Replace dialog box.
- Step 2 – Click Options.
- Step 3 – 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.
- Step 4 – In the list box displayed, see the Formula column for formulas that contain .xl. For this example, Excel has found multiple cases.
- Step 5 – or selecting the particular cell with an external reference, in the list box, click on the cell address link for that row.
#2 – Steps to Find External Links Used in Defined Names
- Step 1 – Go to Formulas tab then, Click on Name manager in defined names group.
- Step 2 – 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 – Step to Find External Links Used in Excel Objects
- Step 1 – In the home tab of the Excel window, in editing group, under find & select option, click on Go To Special option.
- Step 2 – 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
- Step 3 – For a reference to another workbook look in the formula bar of an image, such as [Budget Excel Template.xlsx].
#4 – Steps to Find External Links Used in Chart Titles
- Step 1 – 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 – Steps to For Find External Links Used in Excel Chart Data Series
- Step 1 – 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.
- Step 2 – In the formula bar, look for a reference to another workbook, such as [Types of Charts Excel Template.xlsx] in the Series function.
#6 – Steps to Find External Links Using Edit Links Option
Following steps can be used for finding external links using Edit Links Option:
- Step 1 – Go to the Data Tab. In the Connections group, click on Edit Links.
- Step 2 – It will open the Edit Links dialog box which will be having a list of all the worksheets that are being linked.
- Step 3 – Click on Break Links excel option to convert all linked cells to values.
#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 worksheets 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 the Ctrl key and click each linked object.
- For selecting all links, use the 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 –