In the sample dataset, data was entered using external links in formulas, but errors are displayed.
Solution 1 – Using the Edit Links Feature to Find and Edit Links that are Not Working
Step 1:
Go to the Data tab > Select Edit Links (in Queries & Connections).
Step 2:
In the Edit Links window, click Check Status.
The link status is displayed as Source not found.
- Click Change Source.
Step 4:
- Select the source file for each link and click OK.
OK is displayed as status in the Edit Links dialog box .
Step 5:
- Repeat Steps 3 and 4 for other links and click Close.
- Go back to the worksheet: all broken links work.
If you place the cursor on any of the links, you’ll see the link path:
Method 2 – Using the Find and Replace Feature
Step 1:
- Go to the Home tab > Click Find & Select > Select Replace.
Step 2:
- In the Find and Replace dialog box, enter .xl in Find What.
- Click Find All.
All xlsx files used as links display a #REF error. There are Underscores (_) in the names of the worksheets: New_York, Los_Angeles, which is not possible.
- Cross-check the formula used for the links and you’ll see the Underscore: the formula returns errors.
Step 3:
- Follow Step 1 to use the Find and Replace feature. In the Find and Replace dialog box, enter Underscore (_) in Find What >> Keep Replace With Blank >> Click Find All.
Step 4:
- Click Replace All.
Step 5:
Excel opens the device directory.
- Choose the updated source file.
- Click OK.
Step 6:
- In the confirmation window, click OK.
Links are working.
Read More: 7 Solutions for Greyed Out Edit Links or Change Source Option in Excel
Method 3 – Ensuring an External Cause is Not causing the Problem
Step 1:
- Go to the File tab.
Step 2:
- Select Options.
Step 3:
- In the Excel Options dialog box, select Advanced > Click Web Options (in General).
Step 4:
- In the Web Options dialog box, click Files >> Uncheck Update links on save >> Click OK.
Issues with Updating Links Values Automatically
- Go to the File tab > Options. In the Excel Options dialog box, select Trust Center > Click Trust Center Settings (in Microsoft Excel Trust Center).
- In the Trust Center dialog box, select External Content > Check Prompt user on automatic update for Workbook links (in Security settings for Workbooks Links).
- Click OK.
- In the Excel Options dialog box, click OK.
The Edit Links Command is Grayed-Out
To enable Edit Links:
- Make sure the inserted links are in the formula. Enter an Equal Sign (=) in front of the links to enable Edit Links in the Data tab.
- Check the referred file name and its extension (ixlsx, xlsm).
- Ensure the workbook is not in Compatibility Mode.
Download Excel Workbook
<< Go Back To Edit Links in Excel | Linking in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!