'%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 Refresh an Excel Workbook in Read-Only Mode
Question details
A user wants to know if and how they can refresh data in an Excel workbook that is locked in read-only mode because another user is currently editing it on a shared network drive.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening a shared Excel file while another user has it open, resulting in read-only access.
- Observed behavior
- The workbook cannot retrieve new updates from the original file while in read-only mode, and external data refresh operations may be restricted.
Check if your Excel workbook is stored on a traditional local shared network drive or a cloud service like OneDrive or SharePoint, as this dictates whether real-time co-authoring is supported.
Enable Cloud Co-Authoring via OneDrive or SharePoint
Move the file to OneDrive or SharePoint to allow multiple users to edit and refresh the workbook simultaneously without being locked in read-only mode.
If the file is stored on a local network drive, Excel restricts simultaneous editing by locking the file for the first user who opens it. Utilizing cloud storage unlocks the co-authoring feature.
Save or upload your Excel workbook to OneDrive, OneDrive for Business, or SharePoint.
Open the file from the cloud location and ensure the 'AutoSave' toggle in the top left corner of the Excel window is turned on.
Click the 'Share' button in the upper right corner to invite others to edit the document. Updates will now sync in real time instead of locking the file in read-only mode.
Save a Local Copy to Refresh Data Sources
If you only need to pull the latest external data connections and not the manual updates made by the current editor, save a local copy to bypass the read-only restriction.
Ask the Current Editor to Close the File
The simplest way to gain full edit and refresh access on a traditional shared drive is to wait for the other user to finish their work.
Co-Author Spreadsheets Smoothly with WPS Office
Avoid annoying read-only locks on shared files by using WPS Office. With WPS Cloud, you can co-author spreadsheets in real time, auto-save changes, and collaborate with your team without data refresh conflicts.
- 1. Download and install: Download and install WPS Office, then open your Excel workbook in WPS Spreadsheet.
- 2. Save to cloud: Click on the 'Share' button in the top right corner and save the file to WPS Cloud.
- 3. Adjust permissions: Copy the sharing link and set the access permission to 'Anyone with the link can edit'.
- 4. Collaborate in real time: Share the link with your team to edit the document simultaneously, allowing everyone to view changes instantly without read-only restrictions.
Frequently Asked Questions
Why does my Excel file open as read-only on a shared drive?
Excel locks the file as read-only to prevent conflicting data overwrites when another user is already editing the file on a traditional local network drive.
Will a read-only copy automatically update if the other person saves their changes?
No. If you have the file open in read-only mode, it will not automatically refresh to show the manual edits being made by the other user. You must close and reopen the file to see their latest saved changes.
Can I refresh external data connections like Power Query in a read-only workbook?
Depending on the workbook's security and connection settings, data refresh operations are often restricted in read-only mode. Saving a local copy is the best workaround to refresh external connections if you don't have write access to the original.
How do I stop Excel from locking files as read-only for other users?
To prevent read-only locks, move the file from a local shared network drive to a cloud service like OneDrive, SharePoint, or WPS Cloud, which natively supports simultaneous co-authoring.
