8 Tips to avoid overwriting Excel files in SharePoint Online

Overwritten ExcelOne 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?

OverwriteExcel-readmode
In my experience, an Excel Online file ALWAYS opens in Read-mode.

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.

OverwriteExcel-checkout
How to set mandatory check-out before editing.

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.

OverwriteExcel-clientopening
This setting will always open the file in the client application.

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.

OverwriteExcel-versioning
This is the default setting for all document libraries created in SharePoint Online.

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.

OverwriteExcel-restoreversion
In this case, I restore version 4.

For more info about versioning:

10 things I learned about versions 

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.

OverwriteExcel-password
Here you can also protect your workbook or -sheet

 

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.

OverwriteExcel-pindocument
You can pin the instructions on top of your library so people can not miss it.

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

Advertisements

3 thoughts on “8 Tips to avoid overwriting Excel files in SharePoint Online

  1. Veronique Palmer October 22, 2017 / 7:05 pm

    Ridiculous that we have to go to lengths like this. I honestly don’t know who MS develop for anymore, because it’s not business users.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s