Use AutoRecover in Excel to Save Your Spreadsheet From Yourself

Posted on by David Ringstrom
Ed. note: this is second in an ongoing series to help you make the most out of Excel setting-by-setting. If you have a specific Excel demon to slay, you can get in touch for our resident white knight and Excel-slayer David Ringstrom to help you out.
 
In Part 1 of this series I noted that most Excel users tend to use Excel in its default state. This is akin to time-traveling back to the early 70’s—a world before stonewashed jeans when we endured board-stiff Levi’s for a couple months until they broke-in. Been there, done that. Have a chuckle at my expense, and then realize that you’re likely doing the same thing in Excel.
 
In case you missed it, Part 1 details how to get to the options area in any desktop version of Excel. Go catch up, and then come back here for more tweaks. Today, as illustrated in Figure 1, I’ll focus on the Save section of Excel’s Options dialog box of the desktop versions of Excel:
 
Figure 1: The Save section of Excel’s Options dialog box allows you to customize how you save your workbooks.
 
Save AutoRecover Information: This particular setting resides in the Save section of any Windows-based version of Excel.  Up through Excel 2007, it served as a means by which Excel might, maybe offer to recover what you were working on should the program crash. However, beginning with Excel 2010 this feature offers insurance against things going awry with almost any spreadsheet you may be working on.
As long as this option is enabled, Excel surreptitiously and periodically makes back-up copies of the active workbook you’re using. This is helpful in two ways:
  • In Excel 2010 and later, if you close this workbook without saving, you may be able to pull a Lazarus and retrieve your work from the dead. For Excel 2007 and earlier, you’re SOL.
  • Even if you diligently press Ctrl-S after every keystroke to make sure that you don’t lose a single character of your work, you might still want to be able to time-travel back to how the workbook looked a few minutes ago. In Excel 2010 and later, choose File, Info, and then look in the Recent Versions section, as illustrated in Figure 1. No guarantees here, but you might just see one or more recent versions of the current file, which you can open alongside your live version. This can help you recover from a rash action that you can’t just press Ctrl-Z to undo, such as deleting a worksheet you realize you didn’t mean to expunge.
So, with that background in mind, up your odds of success in Excel and change this setting to 2 minutes, down from its default setting of 10 minutes, as illustrated in Figure 1. You’ll want to do this even if your software dates back to the eighties. In the modern versions of Excel, you’ll have the recovery capabilities I described, while the antique versions of Excel will at least give you a better chance at recovering from a program crash.
 
Moving on, if you’re using Excel 2013 there are two new options that I recommend you adjust, as illustrated in Figure 1:
  • Don’t Show the Backstage When Opening or Saving Files – Apparently Microsoft thinks Excel is theater, and that the File menu is the backstage. In short, click this option on to have a better experience when opening or saving files in Excel 2013. If this setting is off, pressing Ctrl-O won’t display the Open dialog box that you’re accustomed to, instead it will show you a portion of the File menu. The same will occur if you press Ctrl-S in an unsaved workbook—you won’t get a Save As dialog box, but will again be routed through the File menu to click and browse your way to the Save As dialog box, which pretty much defeats the purpose of a keyboard shortcut. Click this setting on to restore the classic functionality of Ctrl-O and Ctrl-S.
  • Save to Computer by Default – Microsoft really, really wants you to save your documents to their cloud-based service called OneDrive. Yes, back in the day this was called SkyDrive, but apparently someone else owned the trademark, and Microsoft couldn’t prevail in court. Click this checkbox on if you prefer to save your documents elsewhere, such as your local hard drive. How quaint. 
Finally, as noted in Part 1 of this series, in all versions of Excel I recommend that you change the Default File Location, or Default Local File Location as it’s referred to in Excel 2013. This typically defaults to the Documents folder on your C: drive, but should be changed to whatever location you use most for opening and saving documents. Thus, it might be the F:\ drive on your network, or in my case, my Dropbox folder on my C: drive—sorry, Microsoft, I’m just not into your OneDrive.
 
About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.

Related Posts

The Technology Productivity Bureau Accounts for All Stakeholders

We all know about getting a credit rating. Whether it’s for a personal credit card, a supply chain vendor authorization, or the much maligned oligarchy who rate public companies and entire nations. Based on alion, a score is developed that (attempts) to capture the inherent risk of a credit failure.

How much could firms benefit from getting a Technology Productivity Rating?

What is the risk of a technology failure?

If an objective ratings agency existed that scored a company’s use of technology, how well would other people score your company? Who is the ‘Greece’ of technology?


To rate technology productivity, the rating has to encompass the entire organization and the way in which technology extends to external stakeholders (customers, suppliers, staff, etc). Optimal productivity from technology doesn’t simply mean newest technology. It’s not just about what technology a company uses that matters. It’s about how the technology is used. I met with a colleague in the technology industry recently who went so far as to say there’s still times when a FAX is the optimal technology for a task. It depends on the potential outcomes and workflows.

To date, I think the focus of technology productivity has been too inwardly focused in companies. Companies say, ‘How can this technology benefit us?’ instead of looking at the workflow effects for external stakeholders too. Granted, most organizations are completely overwhelmed simply by this one-sided approach. But if you look closely at some productivity software, part of the “technology” benefit is actually a workflow transfer to external parties. If I had to rate the technology, the score would decline in the event of workflow transfer being masqueraded as technology.

For example, look at productivity tools around supply chain management and recruitment:

Supply Chain Management
As a means to increase productivity, big companies implement supply chain management systems that effectively transfer the burden for account administration to the vendor companies (sometimes they even charge a fee!). For the implementing company, it is great. All the vendor information is keypunched and filed away into the database for free.

The system integrates with the ERP for invoice approvals all the way to point of payment. The internal technology productivity score is high. For the vendor, every new customer could conceivably mean a similar routine resulting is a productivity loss and therefore would rate the technology lower. A vendor with a lot of customers practically needs a Mechanical Turk just for the data entry!

Seeing these scores could be really beneficial when vendors are choosing what customers to prioritize.

Recruitment
Recruitment technology can be burdensome to external stakeholders while being helpful to internal stakeholders in a similar way. The key to recruitment technology is capturing candidate data to enable filtering and search. Some technology in this field is simply transferring the data entry task to the candidate. Each candidate types out their life story field by field, row by row. From the company standpoint, they see the output of the technology. It is good. From the candidate standpoint, they see a time sink.

Taken in isolation, this candidate time commitment is not a big deal. One candidate typing their qualifications one time in response to one job posting is fine. But what happens when the candidate is applying at a dozen jobs? Two dozen? At what point does the opportunity cost of doing a whole bunch of data entry deter the brightest candidates from these particular employers?

The brightest candidates will apply to the companies that DON’T require a massive typing drill first, selecting away from this less productive technology until it’s unavoidable. The overall technology productivity score would take this into account.

For a company purchasing new technology, understanding the opportunity costs both from your perspective and that of external stakeholders and developing a Technology Productivity Rating may not become a formal process. There is no Technology Productivity Bureau, or least, there isn’t anymore. There was… for a short time… an idea before its time… may it rest in peace.

Perhaps it’s enough to look at it from a more macro-level. Ask yourself, is my business technology liberating for stakeholders or, or are they being repressed? Then, act accordingly.

Geoff Devereux as been active in Vancouver’s technology start-up community for the past 5 years. Prior to getting lured into tech start-ups, Geoff worked in various fields including a 5 year stint in a tax accounting firm. You can see more of his posts for GC here.