K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

Manage Links in Excel Workbooks

10/4/2021

 
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.
Picture
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.
Picture
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. 
Picture
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. 
Picture
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.
Picture
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.

Tommy Stephens

    Categories

    All
    2022
    Accountant
    Accounting Software
    Accounting Solutions
    Adobe
    Advisory
    AI
    Artificial Intelligence
    Automation
    Backup
    Bitcoin
    Blockchain
    Business Automation
    Business Continuity
    Business Intelligence
    Business Management
    Cloud Computing
    Collaborate
    Collaboration
    Colonial
    Computer
    COVID 19
    COVID-19
    CPA
    CRM
    Cryptocurrency
    Customer Relationship Management
    Cybersecurity
    Cyptocurrencies
    Dext
    Doc.It
    Ecommerce
    Emerging Technologies
    Entrepreneurs
    Excel
    Forecasting
    Forecast Sheet
    Google
    Grammarly
    HR
    Internal Controls
    Internal Priorities
    KPIs
    LET
    Links
    Management Reports
    Microsoft 365
    Microsoft Office 2021
    Microsoft Teams
    Office
    Office 365
    Online Shop
    Outsourcing
    Pandemic
    Paperless
    Personal Computer
    PivotTables
    Power Automate
    Power BI
    PowerPoint
    Productivity
    QuickBooks
    Quickbooks Online
    Ransomware
    Receipt Bank
    Remote Work
    RPA
    Sage 50
    Security
    Small Business
    Small Business Accounting
    Small Business Accounting Software
    Stockhistory
    Technologies
    Technology
    Tech Update
    Windows
    Windows 10
    Windows 11
    Word
    Work At Home
    Workflow
    Work From Home
    Xcm
    Zoho
    Zoho CRM

    Authors


    Ward Blatch
    Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

    Tommy Stephens
    Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

    Randy Johnson
    Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com


    Bernie Smith
    Bernie coaches businesses to develop meaningful KPIs and present their management information in the clearest possible way to support good decision making. As the owner of Made to Measure KPIs, he has worked with major organisations including HSBC, Airbus, UBS, Barclays, Credit Suisse, Lloyds and many more.

    RSS Feed

Training & Education

Webinars
Road to Excellence Online Seminar
Seminars
Conferences
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact

K2E Canada Inc.  |  484 Scarlett Crescent  |  Burlington, ON L7L 5M2  |  (905) 633-9772
© 2023 K2E Canada Inc. ALL RIGHTS RESERVED.