|

Excel: The Pitfalls of AutoSave

By Bill Jelen
October 1, 2017
1 comments
Excel_web_image

Excel 2016 users with an Office 365 subscription will soon notice a new AutoSave button in the top-left corner of their Excel screen. For most people, this icon will be greyed out. If you ever see this icon light up, you need to be aware of the pitfalls of AutoSave. The AutoSave feature is an unfortunate byproduct of the new Co-Authoring feature.

 

MULTIPLE USERS, ONE WORKBOOK

 

You’ve probably encountered certain workbooks where multiple people need to access the same workbook. Until now, only one person could edit a workbook at a time. Accounting departments would handle this by taking turns. One person would open the workbook for editing, and everyone else was forced into read-only mode. This often worked, until one coworker forgot to close the file and left for a long lunch break. Then everyone else was locked out of the file needlessly.

 

The former solution, Shared Workbooks, wasn’t practical. If turned on file sharing, you were prevented a number of key actions, including inserting rows or pivot tables.

 

The Excel team has been working on a better solution. Their solution, which they call Co-Authoring, is now rolling out to Office 365 Subscribers. For those cases where multiple people have to access the same workbook at the same time, Co-Authoring is a welcome addition. In order to introduce Co-Authoring, Microsoft also had to add a feature called AutoSave. This feature is going to cause far more headaches.

 

CAN’T CLOSE WITHOUT SAVING

 

I regularly open Excel files with no intention of ever saving them. I might open a file and change a few numbers to see the results—knowing that I plan to click “Don’t Save” when Excel asks if I want to save changes (see Figure 1).

 

10Excel2017Figure1
Figure 1

 

Other times, I might inadvertently wipe out formulas by copying instead of cutting before a paste. Or I delete a worksheet and then discover a horde of #REF! errors appear. Rather than trying to step back through Undo using Ctrl+Z or Alt+Backspace, it’s easier to simply close the file without saving. And if you regularly run macros, you’ll find that Undo is not available to reverse any changes caused by the macro. Without Undo available, closing the file without saving isn’t an option.

 

With AutoSave, that option is no longer possible. Co-Authoring works by saving your workbook to the server after every change. With multiple people working in the same file, this is great news. Excel will use various color cell outlines to show who is editing each cell. In Figure 2, I’m editing cell A1 and someone else is editing C2. You can hover above any cell to see who is editing that cell.

 

10Excel2017Figure2
Figure 2

 

This also creates an issue if you regularly open an existing workbook, make changes, and then Save As to save the file with a new name. If AutoSave is enabled, you will need to change your work flow. As soon as you open the original file, you need to save a copy before making changes. Otherwise, as soon as you change the “October” heading to “November,” the changes will be AutoSaved in the October version of the file.

 

You will notice that options to Save and Save As are missing from the File Menu. The former Save As command is now “Save a Copy.”

 

10Excel2017Figure3WebOnly
Figure 3

 

ALWAYS DEFAULTS TO ON

 

Unfortunately, you can’t permanently turn off AutoSave. Many people have asked Microsoft to disable this feature, but there’s no global setting to turn it off. Instead, you have disable it in each file. To do this, there’s an On icon next to Autosave (see Figure 2). Click that to slide it to Off, and AutoSave is disabled for that file.

 

If you regularly access 500 Excel workbooks and only three of them benefit from Co-Authoring and AutoSave, this means that you will have to remember to disable AutoSave the first time that you open each of the 497 other files. This is nearly impossible—who will remember to add the step of disabling AutoSave every time you open a file?

 

There is a campaign on Excel.UserVoice.Com to ask Microsoft to allow AutoSave to be turned off. Visit this link to add your support to the campaign. Every vote counts.

 

IF YOU FORGET TO DISABLE AUTOSAVE

 

There is a recovery process if you forget to disable AutoSave and lose data, but it isn’t perfect. Look in the Excel title bar to the right of the file name. A new dropdown appears next to the title. Open that dropdown, and choose See All Versions.

 

10Excel2017Figure4WebOnly
Figure 4

 

The list of available versions lists who saved the file and the time.

 

10Excel2017Figure5WebOnly
Figure 5

 

When you open a previous version, you have a choice to use Save As to save the version as a separate file.

 

10Excel2017Figure6WebOnly
Figure 6

 

But this sets up a recipe for losing work:

 

Say that you open the OctoberBudget.xlsx and forget to immediately save a copy as NovemberBudget.xlsx. You spend an hour entering November numbers in the file. Once you discover your error, if you revert to a previous version, you will lose the hour of work.

 

In this case, you’ll have to share the October version with a coworker before reverting to an older version. Open the workbook with the November figures on the other computer and save a copy as November.

 

With the November numbers safe, return to your computer and use Manage Versions to go back to the original version of October.

 

For people who rarely use Co-Authoring with a workbook, having the global AutoSave always set to on will be very annoying.

SF SAYS

 

AutoSave will require you to break old habits in Excel.

 

Save

Save

Bill Jelen is the author of MrExcel XL: 40 Greatest Excel Tips. Send questions for future articles to IMA@MrExcel.com.
1 + Show Comments

1 comment.
    Mitchell Allen November 18, 2017 AT 9:27 am

    Hi Bill,

    My Version of Excel has not yet updated to the new Autosave (I still have Save-AS option in the File Menu.) I wonder if the following scenario has anything to do with Excel:

    A client gave me some test files. I copied one to my development folder, made some changes to test a bit of code and then saved my copy.

    After I was satisfied that the code was working, I wanted to revert to the client’s original data file. So, I deleted mine and copied the original to my development area.

    Within seconds, the copy of original file was *replaced* with my altered version!

    I had just read your article a week ago, so I came back to ask. I hope I am off-base, because otherwise, that means I no longer have control of Excel.

    By the way, Would a read-only attribute help prevent unwanted autosaves?

    Cheers,

    Mitch

You may also like