In our everyday usage of Excel, it is very common to use multiple ones at a time. It is also very common to link them together and update them on a regular basis. But while doing that, the updating option or the Edit Links option is not available always. This can occur for many reasons. In this article, we will focus on the problem of the manually update links option greyed out in Excel.
Overview of the Problem
The links are where a cell in a spreadsheet is connected to a different spreadsheet, either of the same workbook or a different one. By default, we have to update them manually through the Edit Links option from the Queries & Connections group of the Data tab.
If you don’t have any links in any of the spreadsheets of the workbook, the option will be grey obviously. But in some cases, even though they are linked with external data sources, the option may still be greyed out.
We will focus on what can cause it to be greyed out and how we can make the option available here again to update the links manually in Excel.
If Update Links Manually Is Greyed Out in Excel: 5 Possible Solutions
If the manually update links option is greyed out in your Excel workbook you can try these solutions. Keep in mind, that there can be many causes for this problem. So try each one out to find the one that is working for you. We will be going over five different solutions and workarounds for the problem.
Solution 1: Make Sure Links Are External
The manually update link can be simply unavailable because there are no external links in any of the spreadsheets in the workbook. Take a look at the following dataset.
It is in Sheet1. If we copy it and link it to Sheet 2, the Edit link option will still be grey.
This is because the links are within the same workbook. The option will no longer be greyed out if we import the same dataset from a different workbook with a link.
So check if you have linked within the same Excel workbook or a different one, before working with the Edit Links option.
Read More: How to Update Hyperlink in Excel Automatically
Solution 2: Check for Appropriate Formula
The Edit Link feature works only on formulas in cells, not to range names or other values. So, if you have range names or just a direct path linked to other workbooks, but you have no formulas in cells that represent those names or directories, then Edit Links will remain greyed out.
Take a look at the following figure.
The formula after saving for cell links from external workbooks should look like this. There should be file directories (which also should be appropriate), workbooks, and sheet names within the formula followed by an equal sign (=). There should be no reference with named ranges.
Read More: [Fix]: Excel Automatic Update of Links Has Been Disabled
Solution 3: Inspect Proper File Extension
If you type out the directory of the formula for links manually and don’t put in the exact file extension, the link won’t be complete. As a result, the manually update links option will be greyed out in your Excel workbook.
Microsoft Excel supports many file formats, the two most used ones are .xlsx and .xlsm. Make sure you have the correct extension in your formula.
Read More: [Fixed!] Excel Links Not Updating Unless Source Is Open
Solution 4: Uncheck Permission for Auto-Updating Links
Microsoft Excel has a built-in Ask to update automatic links option that enables you to control when and whether to update a link. Sometimes it can also prevent you from using the manually update links option and make it greyed out in Excel.
To change this option, follow these steps.
Steps:
- First of all, select the File tab on your ribbon.
- Then select Options from the left side of the backstage view.
- As a result, the Excel Options box will open up. Now select Advanced from the left of the box.
- Then scroll down on the right to go to the General section.
- Under it, uncheck the Ask to update automatic links option.
- Finally, click on OK.
This can enable the Edit Links option in some cases in Excel.
Solution 5: Turn Off Compatibility Mode
Another reason that can cause the Edit Links option to grey out is the Excel file is running in compatibility mode. The compatibility mode is very helpful to share a file with users of previous versions. But it can also block out some features. The compatibility mode may make links in a workbook unworkable. And thus causing the manual update links option to grey out in the Excel ribbon.
To turn off the compatibility mode, follow these steps.
Steps:
- First, select the File tab on your ribbon.
- Then select Info from the left of the backstage view.
- Now click on Check for Issues beside the Inspect Workbook section on the right.
- After that, select Check Compatibility from the drop-down menu.
- Then uncheck the Check compatibility when saving this workbook option from the Compatibility Checker box.
- Finally, click on OK.
Now save the file and reopen it again. The file should not open in compatibility mode now and may fix the issue of manually updating links greying out in Excel.
Download Practice Workbook
You can download the workbook used for the demonstration from the link below.
Conclusion
These were all the possible fixes for update links manually option greyed out in Excel. Hopefully, one of these solutions has worked out for you. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
