'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Queries and Connections Properties Greyed Out in Excel
Question details
The user is unable to access the Properties option in the Queries and Connections pane after encountering a web query error.
- Product
- Microsoft Excel, Microsoft Dynamics
- Device & OS
- not provided
- Scenario
- Exporting a Dynamics worksheet from Dynamics Sales and clicking 'Refresh All', which triggers a 'This web query returned no data' error. The user attempts to edit the query definition as advised, but the properties menu is inaccessible.
- Observed behavior
- The 'Properties' option under Data > Queries and Connections is greyed out and cannot be clicked.
Verify if your workbook or worksheet is protected with a password, and check whether the data connection is managed by a specialized add-in like Power Query or a Dynamics connector.
Manage the Connection via Power Query Editor
Use this solution if your data source is managed through Power Query or Power Pivot, which operate outside the standard Excel connection properties dialog.
Modern Excel connections, especially those involving external platforms like Microsoft Dynamics, are often built using Power Query rather than legacy web queries. Because of this, the standard 'Properties' button is disabled to prevent conflicts.
Navigate to the 'Data' tab on the Excel ribbon.
Click on 'Get Data' and select 'Launch Power Query Editor' from the dropdown menu.
In the Power Query Editor, locate your query on the left pane, click on the 'Source' step under Applied Steps on the right, and click the gear icon to modify connection details or credentials.
Remove Worksheet and Workbook Protection
Use this method if the workbook structure is locked, which automatically disables external data connection editing.
Re-export the Worksheet from Dynamics Sales
Use this solution when the exported Dynamics query connection is corrupted, expired, or inherently locked by the system administration.
Try WPS Office for Seamless Spreadsheet Management
If complex Excel add-ins and locked connections are slowing down your workflow, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet experience without the heavy overhead of proprietary query engines.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package.
- 2. Install the Software: Run the installer and follow the on-screen prompts to set up WPS Office on your computer.
- 3. Open Your Spreadsheets: Double-click your existing .xlsx files to open them instantly in WPS Spreadsheet and resume your work.
Frequently Asked Questions
Why does Excel say 'This web query returned no data'?
This error typically occurs when the source URL requires authentication that hasn't been provided, the login session has timed out, or the data structure in the source platform (like Microsoft Dynamics) has been modified.
Can I edit a Power Query connection in the standard Properties dialog?
No, Power Query connections are generally managed inside the dedicated Power Query Editor rather than the basic Properties window. You must go to Data > Get Data > Launch Power Query Editor to modify them.
Does workbook protection affect Queries and Connections?
Yes. If the workbook structure or the specific sheet containing the data is protected, Excel will grey out the Properties option in the Queries and Connections pane to prevent unauthorized structural changes to the data model.
What should I do if a Dynamics worksheet fails to refresh permanently?
If manual editing is restricted and the file continually fails to refresh, you should consult your Microsoft Power Platform or Dynamics administrator. They may need to update the connector settings or provide you with a fresh data export template.
