While opening an Excel spreadsheet, if you get an error message saying External Data Connections have been disabled, here is how to get rid of it. It appears when Excel fails to fetch a few settings, such as Data Connections, Linked Data Types, Dynamic Data Exchange, Microsoft Query files, etc. Here, you will learn how to enable those settings and troubleshoot the aforementioned issue in Excel.
What does External Data Connections have been disabled mean in Excel?
Excel often needs to fetch data from other sources to perform or execute a certain function or show the data in the spreadsheet. However, if the required setting is not enabled by default or changed recently to prevent Excel from obtaining such data from a third-party source or another app, Excel displays an error message saying External Data Connections have been disabled. It could appear while starting up the app or during work.
External Data Connections have been disabled in Excel
If External Data Connections have been disabled in Excel, follow these solutions:
- Change settings in Excel Options
- Disable Group Policy setting
- Delete or change Registry value
To learn more about these settings, continue reading.
1] Change settings in Excel Options
Excel provides two options that may cause this error while opening or working in the app. Therefore, follow these steps to verify the settings as they should be:
- Open Excel on your computer.
- Click on the File > Options.
- Switch to the Trust Center tab and click on the Trust Center Settings.
- Go to the External Content tab.
- Find the Security settings for Data Connections option.
- Choose the Prompt user about Data Connections option.
- Remove the tick from the Always block the connection of untrusted Microsoft Query files checkbox.
- Click the OK button.
2] Disable Group Policy setting
Although the Local Group Policy Editor doesn’t come with all the required options as above, you can tweak one of them as per your requirements. If you changed the specific setting by mistake in the past, you could go through these steps to disable the required Group Policy setting:
- Press Win+R to open the Run prompt.
- Type gpedit.msc and click the OK button.
- Go to this path: User Configuration > Administrative Templates > Microsoft Excel 2016 > Excel Options > Security > Trust Center > External Content.
- Double-click on Always prevent untrusted Microsoft Query files from opening setting.
- Choose the Not Configured option.
- Click the OK button.
Then, you need to restart the Excel app to apply the change.
3] Delete or change Registry value
The above-mentioned Group Policy setting can be changed or overwritten with the help of the Registry Editor. That is why it is suggested to check the Registry Editor as well. In case you find the below-mentioned value, you need to delete it or change the value accordingly. Follow these steps to verify the setting:
Search for regedit in the Taskbar search box.
Click on the individual search result.
Click the Yes button on the UAC prompt.
Navigate to this path:
If you want to delete, right-click on the enableblockunsecurequeryfiles REG_DWORD value > select the Delete option and click on the Yes button.
If you want to change the Value data, double-click on the enableblockunsecurequeryfiles REG_DWORD value, enter 0, and click the OK button.
Finally, close all the windows and restart your computer to apply the change. However, if you cannot find the aforementioned path, there is no need to do anything else, as it implies that you do not have the specific Registry value. For your information, like Local Group Policy Editor, you do not have any option to change the Prompt user about Data Connections setting via Registry Editor as well.
That’s all! I hope it helped.
How do I enable external data connection in Excel?
To enable external data connection in Excel, you need to open the Options wizard and go to the Trust Center Settings. Then, head to the External Content tab and find the Security settings for Data Connections. You need to choose the Enable all Data or Prompt user about Data Connections option. However, it is highly recommended to choose the second option instead of the first one. Finally, click the OK button to save the change.