One of the myriad changes announced at MSIgnite was the mention that all files in OneDrive will soon open in edit mode directly, so you can work faster.
I hope Microsoft will wait a bit before rolling that out for SharePoint as, since moving to SharePoint Online, we have had a number of incidents where users have inadvertently overwritten or otherwise messed up a shared Excel Online file, resulting in incorrect data.
I do not quite get how that has happened, as Excel Online files always open in read mode (in Internet Explorer and Edge, in any case, and only very few people have another browser) and you have to specify whether you want to edit in Browser or in Client.
But it has happened more than once, also with people who are quite SharePoint-savvy, so I guess it is a thing. Perhaps it is the “autosave” option when you are in Edit mode, so your changes are saved, even if you do not intend to?
This is a major annoyance, as we can not restore a single file from Office365. We can only restore the full site collection…
So more than ever before, prevention is key! Here are a few ideas to prevent and remediate incorrectly overwritten (Excel) files – pick the option(s) that suit your situation best:
1. Adjust permissions
Make sure only those people who really need to edit the file can do that.
How: Go to the library, folder or file and check and adjust the permissions.
2. Set mandatory check-out
If people have to consciously check out a file, they will be made aware they are going to edit it, and they can stop if they do not want that. It does not change the auto-save, however.
How: Gear Wheel > Library Settings > Versioning settings > At the bottom, check “Require documents to be checked out before they can be edited” and click OK.
This can be a pain for users, as they will have to remember to check the file in when it is finished (and preferably before they go on holiday 🙂 ). On the other hand, a checked out file can not be edited, so it may also be a blessing!
Remember that a Site Owner or site collection admin can always override the checkout or check the file in.
If many people need to edit the file in a short timeframe before a deadline (e.g. end of month), option 7 may be a better solution.
3. Always open the document in the Excel client
SharePoint Online allows you to select the opening behaviour of a file. If you set this to “Client” the file will always open in Excel desktop version, read mode, which will need a conscious effort to edit the file. (Unless you have “Autosave” enabled in your Excel client!)
How: Gear Wheel > Library settings > Advanced Settings > In the 3rd paragraph from the top, select “open in the client application” and click OK.
Please note there are differences between working in Excel Online and Excel client.
In general, the Online version is limited; it is useful when you just need to make a few simple content edits. The Client version is more powerful.
4. Set versioning
This is remediation, not prevention. Having versioning set means you can restore an older correct version if the current one has been corrupted. By default, SharePoint Online document libraries have 500 major versions already enabled, which should be sufficient. 🙂
How to set versioning: Gear Wheel > Library Settings > Versioning settings > Document Version History > make sure this is set as below (or use a smaller number) > Click OK.
How to restore a version: Select the document > Click version history from popup or command bar > Hover over date and time of version to restore and click the black triangle that appears > click Restore from the popup. Please note this version will be copied to the top as a new version.
For more info about versioning:
5. Create a dedicated document library
Options 2, 3 and 4 (and ideally, 1 as well) have to be set for the complete document library in which this document lives. If that is difficult or unpleasant, why not create a new document library especially for this document?
How: Gear wheel > Add an app > Document Library > Specify name > Create.
6. Use a password-protected workbook or worksheet
You can protect your Excel file with a password and only give the password to those people who need to change the data. You may need to rearrange your Excel for that, since you can view a password-protected sheet, but not a password protected workbook, in the browser.
This is never my preferred option, as I think we have SharePoint permissions for this scenario, but in some cases it can be useful.
How: Open the file > click File tab > Info > Protect Workbook > select “Encrypt with Password” (for the complete file – if you want to open in the client) or “Protect Current Sheet” > add password and options > OK.
7. Turn this Excel file into a SharePoint list
This can be a good option if your Excel file is relatively simple and does not contain complicated calculations or relationships between sheets etc.
You can use the SharePoint list to collect the data, export the data into Excel and do your advanced data processing in Excel. In case of many people having to process data before a deadline, e.g. end of month, this method is preferably over mandatory check-out of a file as everyone can work on their own lines without having to wait for others or messing up other people’s data.
How: The simplest way is to use the Import Spreadsheet app.
Then create good views so your audience can view or edit their data according to their needs with the least amount of hassle.
I have streamlined a lot of processes in this way, check out my Business Examples.
8. Instruct your users
Once you have taken your measure(s) of choice, let your users know how they should work with the file. For instance, how to disable Autosave in their Excel client, or how to properly check out and check in.
Add the info on your site’s homepage, create a document that you pin to the top of the library, record a short demo, etc.
Have you had this issue as well and if yes, how are you trying to prevent it?
Image courtesy of Stuart Miles at FreeDigitalPhotos.net