'%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 Power Query Keeping an Excel Source Workbook Locked
Question details
The user needs to resolve an issue where Power Query locks a source Excel file during an active connection, which prevents other collaborators from editing or saving it.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Multiple users are collaborating on Excel files, and one workbook is extracting data from another using a Power Query connection.
- Observed behavior
- Power Query keeps the source workbook open or locked in the background while the query connection is active, blocking other users from saving changes to that source file.
Ensure that the destination workbook is completely closed by all users before testing to see if the source file lock has been released.
Allow Query Connection to Finish and Close File
The most straightforward solution is to ensure the query refresh operation completes and then close the destination file to sever the active connection lock.
Check the status bar at the bottom of the Excel window to confirm that the Power Query data refresh has finished completely.
Save any necessary changes in your destination workbook, then close the file entirely. This action ends the active query connection.
Have another user attempt to open, edit, and save the original source workbook to confirm that the background lock has been successfully removed.
Use an Intermediate Data File
Create a middle-man file to absorb the lock, allowing the primary source file to remain editable for other users.
Contact Microsoft 365 Administrator
If the file remains persistently locked even after closing workbooks, it may require administrative intervention or Microsoft Support.
Try WPS Office for Seamless Spreadsheet Collaboration
If you are frustrated by persistent file-locking issues and complicated data connection errors in Microsoft Excel, consider switching to WPS Office. It provides a fast, lightweight, and highly compatible spreadsheet experience without the heavy background processes that cause local file locks.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx or .csv data files directly.
- 3. Collaborate Freely: Use WPS Cloud collaboration tools to edit documents with your team simultaneously without encountering local file lock errors.
Frequently Asked Questions
Why does Power Query lock my Excel file in the first place?
Power Query places a read/write lock on the source workbook while a connection is active to ensure data integrity during the extraction and refresh process. This prevents the source data from changing mid-refresh.
Will saving the destination file release the source lock?
Not necessarily. Saving alone does not terminate the query connection. You must allow the query to finish refreshing and completely close the destination workbook to guarantee the background lock is released.
Does the intermediate file workaround permanently fix the locking issue?
No, it only shifts the problem. Your original source file becomes free to edit, but the intermediate file will now be locked when the final workbook queries it. You will also need to manually open and refresh the intermediate file to push updates through.
How can I force Excel to release a locked file if the query crashed?
If Excel crashes but leaves a background process running, the file may remain locked. You can press Ctrl + Shift + Esc to open the Task Manager, locate any lingering 'Microsoft Excel' background processes, and end the task to force the lock to drop.
