Excel allows you to refer to external documents from your current spreadsheet. As Excel does not apply special formatting on data sourced from a different file, you will have issues locating them. When you cannot locate your links, you will fail to make the necessary changes to the reference.
You can find links in Excel depending on where they’ve been stored. For starters, you can use Sort and Filter tool to find links from an Excel column. If you’ve used an external reference to use features such as Conditional Formatting and Data Validation, you can make changes to your entered data to edit links.
Using Sort and Filter
This tool is amazing if you haven’t used a different display text for your link. You can filter all existing data except for workbooks that usually have the .xlsx extension.
- Select your data in the workbook.
- Head to Insert > Table.
- Click on the drop-down menu on your table header.
- Deselect the box for Select All.
- Type .xlsx in the search bar, then click OK.
The Sort and Filter tool will sort all workbook links with the .xlsx extension from the rest of your data. You can right-click on the cell you wish to edit and choose Edit Hyperlink.
Use Edit Links Tool
You can use Edit Links to view all external links you’ve added to your workbook. This also includes hidden and broken links. All links you’ve embedded to retrieve data from external sheets charts, name manager, and even a cell is displayed on the Edit Links window.
- Open your worksheet.
- Head to the Data tab.
- Select Edit links from the Connections section.
- You can view and make changes to each of your links in this view:
- Update Values: If you have changed the location of your embedded document, Excel will open File Explorer where you’ll have to navigate to the new location of your document. Else, it will update the status of the selected source.
- Change Source: If you wish to change the document, select this button. You can select your new document from File Explorer.
- Open Source: You can head to the source document by clicking on this button.
- Break Link: If you no longer wish to update the values according to the source document, you can select the Break Link option. Your reference will change to static values and any changes you make in the source document will not affect your destination document.
- Check Status: You can check the status of your source file after you click this option.
- After making the changes, select Close.
Why is Edit Links Option Grayed Out?
The most common for the Edit Links option to be grayed out is that you do not have a cell or object referring to an external link.
The Edit Links window is only available to check and make changes in external links. This option will not work if you’ve embedded your link as a hyperlink. If you’re looking to edit hyperlinks, you can right-click your cell with the hyperlink and select Edit Hyperlink.
