'%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 Excel for the Web Queries Not Refreshing Correctly
Question details
The user needs to resolve an issue where refreshing data in Excel for the web causes formulas linked to a pivot table to return zero.
- Product
- Microsoft Excel for the Web
- Device & OS
- not provided
- Scenario
- Refreshing a pivot table and related formulas that rely on Power Query data sources while working in a web browser.
- Observed behavior
- After refreshing the data, linked formulas return zero instead of the correct calculated values, even though the pivot table itself visually appears normal.
Before troubleshooting, check your internet connection and verify if the same workbook refreshes successfully when opened in the desktop version of Excel.
Verify Power Query Data Source Support for Excel for the Web
Check if your workbook's Power Query data source is fully supported for refreshing in the web version of Excel, and use the desktop app if it is not.
Excel for the web has specific limitations regarding Power Query data source refreshes compared to the desktop version. It currently only supports refreshing data from tables or ranges within the workbook and OData feeds. If your query relies on an unsupported source, the refresh process may fail silently or cause dependent formulas to return zero.
Open your workbook in Excel for the web and navigate to the Data tab to view your current Queries & Connections.
Check the properties of your query to determine if it is connected to a supported source, such as a Table/Range or an OData feed.
If the source is not supported online, click 'Editing' and select 'Open in Desktop App' from the ribbon to launch the file in the full Excel desktop application.
In the desktop app, go to the Data tab and click 'Refresh All'. The data should update correctly, and your formulas will calculate as expected.
Use WPS Office for Reliable Desktop Data Management
If you frequently encounter data refresh limitations with web-based spreadsheet tools, consider switching to WPS Office. It provides a highly compatible, fully featured desktop alternative to Microsoft Office, ensuring complex data processing works seamlessly without web restrictions.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Install the software: Run the downloaded installer and follow the simple on-screen instructions to set up WPS Office on your computer.
- 3. Open your workbook: Launch WPS Spreadsheets and open your .xlsx file to manage, edit, and process your data locally without web browser limitations.
Frequently Asked Questions
Which Power Query data sources can be refreshed in Excel for the web?
Excel for the web currently supports refreshing Power Query connections that pull data from a Table or Range within the same workbook, as well as OData feeds. External sources like SQL databases or local file paths require the desktop version to refresh.
Why do my Excel formulas return zero after a web refresh?
This happens when a pivot table relies on a data source that fails to refresh online due to platform limitations. Although the pivot table layout might appear unchanged, the underlying data model fails to update, causing dependent formulas to evaluate to zero.
How can I refresh an unsupported Power Query connection?
To refresh a query connected to an unsupported source, you must open the workbook in the Microsoft Excel desktop application. Click 'Open in Desktop App' from the web ribbon, and then select 'Refresh All' from the Data tab.
Will older workbooks be affected by this web refresh issue?
Yes, older workbooks that previously worked fine may suddenly experience this issue if the web platform's data policies are updated or if the external data source's connectivity protocols change over time.
