The Edit Links and Change Source buttons are very effective tools to transfer and update large datasets with important links from one Excel sheet to another without editing each and every data one by one manually. But sometimes the Edit Links or the Change Source option doesn’t work. In this article, we will discuss 7 reasons and solutions for greyed out Edit Links or Change Source option in Excel.
7 Solutions for Greyed Out Edit Links or Change Source Option in Excel
Following this section, you will know 7 the most common reasons why the Edit Links and Change Source feature remain greyed out and what are the solutions for them.
1. Workbooks Must Contain External Links to Enable Greyed Out Edit Links
The Edit Link function is used to control links between different workbooks, such as external links. So, if your workbook doesn’t have any external links then the feature will remain greyed out.
By external links, we meant links to different workbooks, not links from the internet.
Also, make sure that you are not working with the internal links, that means, you are not linking the existing workbook. If you do, then the Edit Links will remain greyed out.
Read More: [Fix:] Edit Links in Excel Not Working
2. Cells Must Carry Formula to Prevent Edit Links from Greying Out
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 are representing those names or directories, then Edit Links will remain greyed out.
To solve the problem, just enter the directory path or the name range like a formula; with an equal sign (=) before it.
3. File Name Must be Entered with Proper Extension to Enable Edit Links in Excel
The file name of the other workbook that you are linking to the current workbook, must have the proper extension. For instance, make sure you enter the extension as .XLSX instead of .XLS.
4. Modify Excel Built-in Options to Edit Links That Greyed Out
Sometimes, Excel’s built-in options prevent you from Edit Links in your workbook.
To modify Excel Options to enable the Edit Links feature:
- First, go to File -> Options.
- Then, click on Advanced.
- From the General group, uncheck Ask to update automatic links.
- Later, click OK.
This will enable the Edit Links tool in your Excel workbook.
5. Turn Off the Compatibility Mode to Enable Greyed Out Edit Links in Excel
If the file is open with compatibility mode, then the Edit Link will be greyed out.
To turn off the compatibility mode:
- First, go to File -> Info.
- You will see the required filename with Compatibility Mode written next to it. Click on the file.
- A Microsoft Excel pop-up window will appear. First click OK, then click Yes to convert the compatible file into the existing file format.
Once you turn off the compatibility mode from the Excel workbook, then you can Edit Links easily.
6. Source References Must be Untouched to Change Source in Excel
If you link another workbook in your existing workbook and later remove or modify the linked workbook, then the Change Source button will not be enabled as it won’t find the source workbook anymore.
So, to prevent this problem, you mustn’t modify the linked workbook once you are done linking it with your current workbook.
7. Update the File Format to Change Source in Excel
If the linked workbook has links to a closed workbook that is saved in the HTML file format, then the Change Source button will not be enabled. To avoid this problem, make sure you save the file in the appropriate format.
Here we have provided the example file that we use in this article.
To conclude, this article discussed 7 reasons and solutions for greyed out Edit Links or Change Source option in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.