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).
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.
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.”
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.
The list of available versions lists who saved the file and the time.
When you open a previous version, you have a choice to use Save As to save the version as a separate file.
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.
October 2017
