It’s convenient to fetch data from existing files using external links to save a lot of time and effort. As a result, edit links in Excel not working is common among users. Excel in build has multiple features such as Edit Links and Find and Replace to deal with edit links.
Let’s say we have a dataset that fetches or inserts data using external links in formulas in Excel. But for some reason the fetched data results in error (i.e., Not Working). We want to fix them using Excel features.
In this article, we use Excel’s Edit Links, Find and Replace, and disabling Update links on save to edit links in Excel not working.
Basics of Broken Links
In Excel files, users link cells to various external files to fetch data to use them in further calculations. Also referring to cell values from external files is very common among Excel users. However, on occasions when we Delete, Rename, Relocate or assign Incorrect Names in formulas of referred files, Excel results in broken links or #REF error.
Edit Links Not Working in Excel: Fixed
While working in Excel, we instantly identify the broken links as they result in errors. But for numerous entries, it’s quite hard to figure it out manually. In that case, we use Excel features to first find broken links and then repair them. Excel Edit Links and Find and Replace features allow links workability checking and a way to fix them.
Method 1: Using Edit Links Feature to Find and Edit Not Working Links
Excel provides the Edit Links feature in the Data tab. Edit Links dialog box offers multiple options to check link status, change link sources, etc.
Step 1: Go to the Data tab > Select Edit Links (from the Queries & Connections section).
Step 2: The Edit Links window appears as shown in the following picture. Click on Check Status to check the workability of the inserted links.
Step 3: Clicking on Check Status displays the link status as Source not found. To fix the issue, Click on Change Source.
Step 4: Selecting Source Change makes each inserted link to reassign the source path. Excel opens the Computer Directory to choose from files as the source file for each individual link. Since you first select New York Sale.xlsx, Excel opens the directory to assign it to desired files.
Select the desired file and click on OK.
➤ As soon as you select the source file, Excel shows OK in the Edit Links dialog box status.
Step 5: Repeat Steps 3 and 4 for other links and afterward the Edit Links dialog box shows OK for all links’ Status. Click on Close.
➤ Return to the worksheet. You see all previously broken links now work perfectly. You can place the cursor on any of the links and Excel shows you the link path as depicted in the following image.
Method 2: Fix Not Working Links in Excel Using Find and Replace Feature
Sometimes, users insert characters in formulas that are non-existent in source file names or Worksheet names. We can find and replace those discrepancies to make inserted links work just fine.
Step 1: Hover to the Home tab > Click on Find & Select > Select Replace.
Step 2: The Find and Replace dialog box appears. In the dialog box,
Type .xl in the Find What command box.
Click on Find All.
You see all the xlsx files used as links stacked below the Find and Replace dialog box. And all of them result in #REF error in their values. Also, you see there are existing Underscores (_) in worksheet’s names such as New_York, Los_Angeles, etc.
➤ Check whether linked Excel worksheets contain Underscore in their name or not. Obviously, the worksheets don’t have underscores in their names.
➤ Cross-check the applied formula we use for the links and you see Underscore exists in the worksheet name also. As a result, the formula results in errors.
Step 3: Again, execute the Find and Replace feature following Step 1. In the Find and Replace dialog box,
➤ Insert Underscore (_) in the Find What command box >> Keep the Replace With command box Blank >> Click on Find All.
Step 4: After that click on Replace All as shown in the below picture.
Step 5: Excel serially opens the device directory. Choose the respective source file according to the Update Values file name. Click on OK.
Step 6: Excel offers the selection 3 times as you have 3 links to assign source files. Excel displays a confirmation window saying it has already replaced 3 entries. Click on OK.
➤ Excel’s Find and Replace feature fixes all the broken links and displays the exact amounts as the links are supposed to.
Read More: 7 Solutions for Greyed Out Edit Links or Change Source Option in Excel
Method 3: Ensuring External Cause Does Not Result in Links Not Working
Most of the time the issue of Excel links not working occurs due to the user’s improper handling of source files. However sometimes an enabled option in the Advanced menu causes external links to malfunction.
Step 1: Go to the File ribbon.
Step 2: Select Options from the File menu options.
Step 3: The Excel Options dialog box appears. In the dialog box, Select Advanced > Click on Web Options (under the General section).
Step 4: Excel brings up the Web Options dialog box. In the Web Options dialog box,
➤ Click on the Files section >> Untick Update links on save >> Click OK.
Disabling Update links on save option restricts Excel to check links every time you apply save to the Workbook. And this bar on checking the links keeps inserted links workable after the user’s usage until their recreation.
Issues with Update Links Values Automatically
It’s natural that we change the values in source files. And we can ensure that every time the values in the source file change, Excel displays a prompt notification to automatically update values in inserted ones.
🔄 Go to File tab > Options. In the Excel Options dialog box, Select Trust Center > Click on Trust Center Settings (under Microsoft Excel Trust Center).
🔄 The Trust Center dialog box opens up. In the dialog box, Select External Content > Check Prompt user on automatic update for Workbook links (under Security settings for Workbooks Links). Click on OK.
🔄 Clicking OK takes you to the Excel Options dialog box. Again, Click on OK.
Issues with Greyed-Out Edit Links
Users may encounter greyed-out Edit Links option though the worksheet contains inserted links.
🔄 The reasons behind the greyed-out Edit Links are many. You can follow the below way-outs to enable Edit Links options.
🔼 Make sure the inserted links are in the formula. Insert an Equal Sign (=) in front of the links to enable the Edit Links option in the Data tab.
🔼 Check the referred file name whether it has the proper file extension (i.e., xlsx, xlsm, etc.) or not.
🔼 If the user opens the Workbook in Compatibility Mode, Excel grayed out the Edit Links option. Ensure the workbook is not in Compatibility Mode.
Download Excel Workbook
Conclusion
In this article, we use Edit Links and Find and Replace feature to resolve edit links in Excel not working issues. However, we discuss Broken Links, the way to Auto Update link’s values. Hope these discussed methods clarify your understanding regarding edit links not working in Excel. Comment, if you have further inquiries or have anything to add.
<< Go Back To Edit Links in Excel | Linking in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
