Microsoft

How to Restore External Links in Excel Effectively

Md Sojib Mia

— 3 min read
Share
An Excel spreadsheet displaying external links and data connections.

If you've lost external links in an important Excel report, you need a quick way to restore them. External links are references that connect your spreadsheet to data in other workbooks, and losing them can disrupt your analysis. Fortunately, you can identify and restore these links using a few straightforward steps.

External links in Excel refer to references that point to data in other Excel workbooks. For example, if you have a summary workbook that pulls data from multiple project workbooks, those references are external links. They help consolidate information from various sources into a single report, making analysis easier. Without these links, your data may be incomplete or outdated.

Prerequisites

Before you begin, make sure you have the following:

  • Access to the Excel workbook with lost links
  • The original source workbooks that the links refer to, stored in a known location
  • The necessary permissions to access these source workbooks if they are password-protected
  • An Excel version that supports external links (most recent versions do)

To find broken or missing external links in Excel, use the built-in features designed for this purpose. Here’s how:

  1. Open the workbook you suspect has broken links.
  2. Go to the Data tab on the ribbon.
  3. Click on Edit Links. This opens a dialog box that lists all the external links in your workbook.
  4. Look for any links marked as Error or Unknown. These indicate links that Excel cannot find or that are broken.
  5. Check for any #REF! errors in your cells, which often indicate that the source file has moved or been deleted.
An Excel screen displaying broken external link errors.

Restoring external links in Excel involves reconnecting to the source file or updating the link reference. Here's how:

  1. In the Edit Links dialog box, select the broken link you wish to restore.
  2. Click on Change Source. This allows you to locate the correct workbook.
  3. Navigate to the folder where the source workbook is stored and select the file, then click Open.
  4. After selecting the correct file, confirm by clicking OK in the dialog box.
  5. Repeat this process for any other broken links listed.

Be sure that the source files are in a stable location before attempting to change the source, as moving them again could break the links again.

A screenshot of the Edit Links dialog box in Excel for restoring links.

After restoring the external links, it’s crucial to verify that they are functioning correctly. Here’s how:

  1. Check the cells that were previously showing errors or broken links to see if the data is now displaying correctly.
  2. Return to the Edit Links dialog to ensure that the links no longer show as broken.
  3. To further verify, make a small change in the source workbook and check if that change reflects in your main workbook. This confirms the link is active and working as intended.

When restoring external links, you may encounter several common issues:

  • File Moved or Deleted: If the original source file has been moved or deleted, you will need to locate it and ensure it is accessible.
  • Wrong File Format: Ensure that the linked workbook is in a compatible format (e.g., .xlsx). Older formats may cause issues.
  • Protected Workbooks: If the source workbook is password-protected, you won't be able to access the links without the password.

For these issues, check the file location, confirm the format, or seek the necessary permissions to access protected workbooks.

Conclusion

Now that you know how to identify and restore external links in Excel, you can quickly recover your critical data connections. Make it a habit to check your links after moving files or making significant changes in your workbooks to prevent future issues.

Read more