[Fix:] Edit Links in Excel Not Working

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.

Dataset-Edit Links in Excel not Working

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.


Download Excel Workbook


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.


3 Easy Ways to Deal with Edit Links Not Working in Excel

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

Edit links-Edit Links in Excel not Working

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.

Check status

Step 3: Clicking on Check Status displays the link status as Source not found. To fix the issue, Click on Change Source.

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.

Device directory

As soon as you select the source file, Excel shows OK in the Edit Links dialog box status.

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

After repetitions

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.

Fixed links-Edit Links in Excel not Working

Read More: How to Edit Hyperlink in Excel (5 Quick & Easy Ways)


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.

find and replace-Edit Links in Excel not Working

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.

.xl files

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.

Check file name

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.

Formula insertion

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.

find and replace

Step 4: After that click on Replace All as shown in the below picture.

Replace

Step 5: Excel serially opens the device directory. Choose the respective source file according to the Update Values file name. Click on OK.

Selecting source files

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.

Replacement

Excel’s Find and Replace feature fixes all the broken links and displays the exact amounts as the links are supposed to.

Final result

Read More: [Solve:] Cannot Edit a Macro on a Hidden Workbook (2 Easy Solutions)


Similar Readings


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.

External reasons-Edit Links in Excel not Working

Step 2: Select Options from the File menu options.

Selecting Options

Step 3: The Excel Options dialog box appears. In the dialog box, Select Advanced > Click on Web Options (under the General section).

Selecting Advanced

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.

Unselecting Files

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.

Read More: How to Enable Editing in Excel (5 Easy Ways)


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

auto update-Edit Links in Excel not Working

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

Selection

🔄 Clicking OK takes you to the Excel Options dialog box. Again, Click on OK.

Click on OK


Issues with Greyed-Out Edit Links

Users may encounter greyed-out Edit Links option though the worksheet contains inserted links.

Edit links issue-Edit Links in Excel not Working

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


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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo