Fix SharePoint Excel Power Query Not Refreshing Automatically
Question details
The user needs to resolve an issue where Power Query automatic refresh fails when an Excel workbook is opened in SharePoint or Excel for the web.
- Product
- Excel for the web / SharePoint
- Device & OS
- not provided
- Scenario
- Hosting and opening an Excel workbook in SharePoint or Excel for the web that relies on automatic Power Query refreshes.
- Observed behavior
- Power Query refresh settings work normally in the desktop application but fail to trigger automatically online, particularly when querying data from within the same workbook.
Ensure you have the desktop version of Microsoft Excel installed on your device, as you will need it to bypass the current limitations of Excel for the web.
Open the Workbook in the Excel Desktop Application
Use the desktop application to maintain the automatic Power Query refresh functionality, as Excel for the web does not fully support this background process.
Excel for the web currently has limited capabilities regarding background Power Query processing. When a query references data within the same workbook, automatic refresh settings (such as refreshing on open or at specific time intervals) will often fail online.
Navigate to your workbook in SharePoint or Excel for the web. Click the 'Viewing' or 'Editing' button at the top ribbon, and select 'Open in Desktop App'.
Once the file opens in your desktop version of Excel, click 'Enable Editing' and 'Enable Content' in the yellow security warning bar if prompted.
Leave the workbook open in the desktop application so that your Power Query automatic refresh settings can execute as scheduled.
Make sure AutoSave is turned on, or manually save the file so the refreshed data synchronizes back to SharePoint.
Submit Feature Feedback to Microsoft
Report this exact missing capability to Microsoft to help prioritize Power Query feature parity in Excel for the web.
Try WPS Office for Seamless Desktop Data Management
If web-based spreadsheet limitations are slowing down your workflow, consider using WPS Office. It provides a robust, desktop-first spreadsheet application that delivers high performance and full compatibility with your existing Excel files, completely free.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package.
- 2. Install the Suite: Run the installer and follow the simple on-screen instructions to set up the software.
- 3. Manage Excel Files: Launch WPS Spreadsheet and open your existing .xlsx files to edit and manage your data locally without web restrictions.
Frequently Asked Questions
Why does Power Query refresh work on my desktop but not in SharePoint?
Excel for the web and SharePoint currently lack the full background processing engine required to automatically execute certain Power Query data refreshes. This is especially true when queries reference tables or ranges located within the same workbook.
Can I manually refresh Power Query data in Excel for the web?
Yes, in many cases you can navigate to the Data tab and click 'Refresh All' to manually update connections in Excel for the web. However, automatic background triggers (like 'Refresh on open' or time-based intervals) are not fully supported online.
Does this issue affect queries pulling from external data sources?
While Excel for the web supports manual refresh for some external data sources (like SQL Server or OData), automatic background refreshes and queries querying internal workbook data face severe limitations in the browser environment.
