Excel Queries and Connections Are Not Working: 4 Possible Reasons with Solutions

Method 1 – The Source Has Not Been Saved Properly

If you don’t save the source file correctly, the Excel Queries and Connections won’t work correctly.

Solution: Save the Source Properly

Before establishing connections, go to your source file and ensure that you have saved the file correctly.

Method 2 – Existing Connections Are Obsolete

If your connections are obsolete or not up to date, you may face problems in Excel Queries and Connections. Maybe the items displayed in the Queries are from the cache memory rather than the direct source.

Solution: Refresh the Connections

Steps:

• Go to the Queries & Connections sidebar.
• Click the Refresh icon.

• The Query’s data will be refreshed.

Method 3 – The Query Includes Incompatible Expressions

If your source file has some expression that is not supported by Excel, then this may create problems in Excel Queries and Connections.

Solution: Change the Expressions

To solve the issue, we need to remove the portion of the data that contains incompatible data or use an Excel-recognized expression in place of it. If your source is a Microsoft Access Database, then you need to be careful about some expressions, like the nz() function, which is incompatible with Excel.

Method 4 – Power Query Close and Load to Feature Greyed out

There are some occasions where the Close & Load option in the Power Query window is greyed out. (See the following picture)

Solution: Change the Type of Connection

Steps:

• Check the type of Query Connection. You can do that by going to the Queries & Connections sidebar.

• If the type is Connection only, then you cannot use Close & Load to. To change the type of connection, right-click on the sidebar and then go to the Load To option.

• A new dialogue box named Import Data will open like this.

• You can choose Table instead of Only Create Connection and load this data to the Existing worksheet or a New worksheet. Finally, click OK.

You can load it into any of our desired worksheets.

Things to Remember

To solve your own problem regarding Excel Queries and Connections, you need to properly identify what kind of problem you are having among the 4 types mentioned above. Then, you can take the appropriate approach to solve the problem.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• How to Create a Data Source in Excel
• How to Refresh Data Connection in Excel Without Opening File
• How to Create Excel Data Connection to Another Excel File
• External Data Connections Have Been Disabled in Excel 
• Data Connection Not Refreshing in Excel
• Excel VBA: How to Refresh All Data Connections

<< Go Back to Excel Data Connections | Importing Data in Excel | Learn Excel