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.