'%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)
Fix Shared Excel Power Query Workbook Not Refreshing for Other Users
Question details
A shared Excel workbook using Power Query to fetch SQL Server data fails to display refreshed data for other users, and occasionally gets stuck during saving or closing.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Multiple users are collaborating on a shared Excel workbook stored in SharePoint that relies on Power Query to pull updates from a SQL Server.
- Observed behavior
- Updates made via Power Query are not visible to other users viewing the shared file. Additionally, the workbook often hangs or gets stuck while saving or closing.
Before making structural changes, ensure all users have the necessary database credentials to access the SQL Server and verify that SharePoint synchronization is fully active on their devices.
Separate the Power Query Connection from the Main Workbook
Move the Power Query logic into a dedicated workbook to prevent SharePoint synchronization limits and sharing conflicts.
When multiple users access a shared workbook in SharePoint, Excel can hit a synchronization or change limit, especially when heavy Power Query refreshes from external databases are involved. Separating the data extraction process from the presentation layer effectively resolves these sharing conflicts.
Open Excel and create a new blank workbook. This file will be dedicated purely to fetching data.
Navigate to 'Data' > 'Get Data' > 'From Database' > 'From SQL Server Database'. Recreate your original Power Query connection here.
Load the queried data into an Excel Table in this new workbook. Save the file to a secure, accessible location such as your SharePoint document library.
Open your original shared workbook and delete the heavy SQL Power Query connections. Instead, go to 'Data' > 'Get Data' > 'From File' > 'From Workbook' and connect to the new data source workbook you just saved.
Click 'Refresh All'. The shared workbook is now only reading a static Excel table rather than processing the heavy SQL query directly, significantly reducing sync and save freezes.
Experience Seamless Collaboration with WPS Office
If Microsoft Excel's sharing and synchronization limits are slowing down your team's workflow, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly environment for handling your spreadsheet data without the bloat.
- 1. Install WPS Office: Download and install WPS Office on your computer or mobile device.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and click 'Open' to directly load your existing .xlsx files without losing formatting.
- 3. Collaborate Seamlessly: Save your files to WPS Cloud to enjoy real-time, conflict-free collaboration with your team.
Frequently Asked Questions
Why does my shared Excel workbook get stuck when saving?
Shared workbooks heavily rely on network synchronization. When combined with complex Power Query data refreshes, Excel can hit a change limit or lock up, causing the application to hang while trying to upload the processed file to SharePoint.
Do other users need access to the SQL database to see the refreshed data?
Yes, if the Power Query connection is set to refresh upon opening, every user opening the file needs the proper database credentials to pull the latest data from the SQL Server. Splitting the workbook allows one authorized user or system to refresh the data centrally.
Can I use Power Automate to refresh the Power Query data instead?
Yes, you can set up a Power Automate flow in combination with Excel Online to refresh the dataset on a schedule. This prevents individual users from needing to manually trigger the refresh, reducing sync conflicts.
Is it better to use Excel Online for shared Power Query files?
Excel for the Web has limited support for running certain local or complex Power Query connections. It is generally recommended to use the desktop client for refreshing complex SQL connections, making the split-workbook approach the most reliable workaround.
