AutoSave is one of the most confusing and frustrating features of Microsoft Excel, primarily because most people don't understand it properly. So I'm writing this post to clarify how it works for future reference.
What is AutoSave?
AutoSave is Microsoft's attempt to replicate the way Google Sheets automatically saves changes in real time, thereby preventing the need to ever Save or Save As.
This is great in theory but not so great when building complex financial models.
You can read all about it here: What is AutoSave?
How do I turn AutoSave on and off?
AutoSave is switched On or Off on a workbook specific basis via the AutoSave switch, which is located in the top left of the Excel window, as shown below:
So you can have some workbooks with AutoSave On, and others with AutoSave Off. And this setting is stored in the workbook when you save and close it, so it should be retained when you next open it.
In recent versions of Office, AutoSave is turned on by default, meaning that AutoSave will automatically be switched on whenever workbooks are first saved into OneDrive / SharePoint folders.
You can change this default behaviour via the Save tab within the Excel Options dialog (File tab, Option button), as shown below:
Switching this off setting will mean AutoSave is not automatically turned on for new workbooks, but it can still be turned on whenever you want for any specific workbook via the AutoSave switch in the top left of the Excel window.
When would I want to use AutoSave?
Microsoft states that AutoSave aims to mitigate the risk of data loss due to things like power outages, but this is subterfuge, Microsoft's real goal is to move everyone's data into Azure and AutoSave and OneDrive are how they're doing this without really asking you.
From our experience, the only time you ever really need to use AutoSave is when you're co-authoring. Co-authoring is Microsoft's term multiple people editing the same Excel workbook via the cloud, so basically doing what people do in Google Sheets.
You can read all about it here: Collaborate on Excel workbooks at the same time with co-authoring
Co-authoring is actually pretty cool, but based on our experience isn't yet as reliable as Google Sheets, and editing some things - such as drop down boxes and other controls - causes the co-authoring session to fall apart. But it's great for multiple users editing model assumptions at the same time.
Does Modano support AutoSave?
When you turn on AutoSave for modular workbooks, you can only edit assumptions. So, basically, AutoSave can be used on Modano modular workbooks, but not while doing anything other than entering assumptions.
If you do try to change anything other than assumptions, you'll see a dialog like this one:
This means you can co-author modular workbooks, but you can't use any Modano tools (e.g. add categories, edit formulas, etc.) while co-authoring.
So the basic rule is leave AutoSave off when using Modano modular workbooks, unless you're co-authoring.
What settings does Modano recommend?
Modano modular workbooks will work regardless of your default settings, but if AutoSave is on by default you may find it annoying switching it off every time you save a new workbook into OneDrive / SharePoint folders.
For this reason, and because we rarely co-author, we recommend turning OFF the Autosave files stored in the Cloud by default in Excel option in your Excel Options, as shown below:
It's worth noting that if you have IT administrators, they are able to control whether you can change this setting via group policies, so you may need to speak with them if you can't change this setting yourself.
Here's some information for your IT administrators: What IT administrators should know about AutoSave
Request Support
It's important to understand the implications of your AutoSave settings, and set up your computer so that it's most suited to your use case.
If this article hasn't helped you resolve your issue, please request support from our team and we'll help you.
Onwards.
Michael Hutchens
Managing Director | Modano
Hi Michael,
This is all really good background info as a bit of defence as the user base gets used to auto save I'm now finding a lot of resistance to turning it off "incase they forget to save"(how they survied up to 2019 is beyond me!).
The other issue is that if people are opening the model through the browser, turning autosave off is not an option. So today during testing we came accross a scenario where one user had the model open in Modano on the desktop version while two other users had the model open through the browser and what seemed to happen when the Modano user mirrored a module, the mirrored moduled appeared in the browser but becuase it must have been saving multiple times in the background in the browser while mirroring it obviously corrupted the workbook.
Therefore, is it possible to add a second condition to the restriction you outlined above where when there are multiple users having the workbook open, no Modano tools can be used?
The model I'm rolling out is a multi user model which has very tight deadlines on month end and I need to find a way to give co-authoring access but at the same time I need to ensure the Modano integrity is intact with a lot of completely green Modano users.
For now, our plan is to "check out" the file by a select few Modano users while using Modano tools (mirrorring, adding categories and changing time series in particular) and then check it back in so the analyst can do their updates (in the browser)
Your thoughts on this is much appreciated
T
Hey Tarjei,
Interesting problem and I would love to hear Mike's thoughts on it as well.
We have had similar issues in the past and the way we tried to mitigate this was to lock the files down using the Modano protection settings. The aim was to differentiate model use, versus model development, and any updates needed to occur offline by users that have the right access to make the updates. We just made sure notes were added to the file, in the issues register, or just separately.
Otherwise, yeh it can be a bit of a problem.
Jun
Hi Jun,
The file is locked down with password, the issue is that while yiou're working on it as a developer, someone else might open it through the broser and if you don't notice, go and use any modeno tools that takes time (like mirroring), the webside save during this process will corrupt the workbook.
The check out is the only safe way we have found to do it thus far.
T
Hey Tarjei,
I mean lock down the file so they can't use Modano functionality, ie limit it to Edit assumptions only, or allow Add and remove work in progress fill color. You may also want to allow Add and edit comments.
It is really just another layer in the "check out" process you've mentioned, where the file won't even allow users to attempt any Modano functionality.
Jun
Hi Jun,
I see what you mean now but I do actually need to allow Modano tools for the model to run (I'm making the most of our site licence!) and I don't want to give away the password so that wouldn't do it unfortunately.
I appreciate your suggestions as always Jun:-)
T
Thanks for the suggestion, I appreciate it.