The ability to link data from one Excel workbook to another is one of the spreadsheet application’s best features. However, managing links – including editing and deleting links – is frustrating for many users. In this article, you will learn the best ways to manage links in Excel workbooks.
Identifying And Managing Inbound Links
The most common need is to identify and manage inbound links from one or more “source” workbooks into a “destination” workbook. The need for this type of management arises from several situations, including troubleshooting calculations and auditing and verifying formulas in the destination workbook. Fortunately, there are three at least three simple ways to identify inbound links, and two of these methods facilitate editing and managing linked data with ease.
Use Excel's Find & Replace Feature To Identify Inbound Links
Perhaps the most direct method of identifying inbound links is to use Excel’s Find and Replace feature to search for all formulas containing “xlsx.” This technique works because when you link data from another workbook, the link’s text includes “xlsx.”
To utilize this technique, first open Excel’s Find and Replace dialog box using a CTRL + F keyboard sequence. Then enter “*.xlsx” in the Find what box and ensure that you enable Formulas in the Look in box as shown in Figure 1. Note that you can search for inbound links in the current worksheet only or the entire workbook. Finally, click Find All to search for all the links in the worksheet or workbook.
To utilize this technique, first open Excel’s Find and Replace dialog box using a CTRL + F keyboard sequence. Then enter “*.xlsx” in the Find what box and ensure that you enable Formulas in the Look in box as shown in Figure 1. Note that you can search for inbound links in the current worksheet only or the entire workbook. Finally, click Find All to search for all the links in the worksheet or workbook.
Figure 1 - Searching For Links With Excel's Find And Replace Feature
Upon executing the search, the Find and Replace dialog box displays all the links in the location searched, as shown in Figure 2. Once identified, you can manage the links – edit them or delete them – by editing the cells in which they reside.
Figure 2 - Identifying Inbound Links With Find And Replace
Managing Inbound Links With Excel’s Edit Links Feature
Another option for managing inbound links in a workbook is to use Excel’s Edit Links feature. To use this feature, access it by clicking Edit Links in the Queries & Connections group on the Data tab of the Ribbon. Alternatively, you can access Edit Links by clicking File, Info, and selecting Edit Links to Files near the window’s lower right corner. Regardless of how you choose to access it, the Edit Links window appears, as shown in Figure 3.
Figure 3 - Excel's Edit Links Tool
From the Edit Links window, you can choose to update the values linked into the current workbook, edit links by changing their source workbook or breaking them, open the source workbook, and check the status of a link. Note that Excel does not make these tools available if you choose the Find & Replace method of identifying links discussed previously.
Identifying Links With Excel's Inquire Add-In
A third option for identifying links to manage them is to take advantage of Excel’s Inquire add-in. Inquire is a Microsoft-provided add-in for Excel that offers tremendous capabilities for identifying risk and inconsistencies in Excel workbooks. Inquire add-in versions of Excel provided through Office Professional Plus and Microsoft 365 Apps for enterprise editions. For more information on activating Inquire, see the Microsoft-published article at https://bit.ly/3yoP2IE.
With the Inquire add-in activated, click Inquire followed by Workbook Relationship to create a Workbook Relationship report. This report, a sample of which Figure 4 displays, pictorially depicts the links from one workbook to another. Similarly, you could generate a Worksheet Relationship report to see how data flows from one worksheet to another.
With the Inquire add-in activated, click Inquire followed by Workbook Relationship to create a Workbook Relationship report. This report, a sample of which Figure 4 displays, pictorially depicts the links from one workbook to another. Similarly, you could generate a Worksheet Relationship report to see how data flows from one worksheet to another.
Figure 4 - Inquire's Workbook Relationship Report
For an even more detailed view of links, you can generate a Cell Relationship report that provides a granular view of all cells linking into a specific cell.
Figure 5 - Cell Relationship Report Created By Excel's Inquire Add-In
Once you identify the links with linked data, you can use any method desired to edit or deactivate the links, as necessary.
Summary
Linking data from one workbook to another is a common Excel practice. However, sometimes managing links can become unwieldy. In some cases, users end searching for linked data by inspecting each cell, one-by-one. Of course, there are better ways to manage links in Excel workbooks. For example, as outlined above, you can use Excel’s Find & Replace feature, Excel’s Edit Links tool, or the Inquire add-in to assist you in identifying linked data and managing it. So, if you need to manage links in Excel workbooks, explore the techniques discussed here to get better results in less time.
Are you interested in learning more about Excel? Consider a K2 Enterprises learning option focused on Excel. You can learn more at www.k2e.ca/training.
RSS Feed