Unlike QuickBooks, there’s no single place you can go in Excel to say “always back-up my workbooks.” But, for key spreadsheets there is a hidden setting that you can enable on a workbook-by-workbook basis. You’ll access by way of the Tools button that appears in the Save As dialog box:
- Excel 2010 and later: Choose File, Save As, and then click the word Tools that appears to the left of the Save As button, and then choose General Options, as illustrated in Figure 1.
- Excel 2007: Click the round Office button in the top left-hand corner and then click Save As. Click the Tools button that appears on the bottom left-hand corner of the Save As window, and then choose General Options.
- Excel 2003: Choose File, Save As, click the Tools button in the upper right-hand corner of the Save As window, and then choose General Options.
- Excel for Mac: Choose File, Save As, click the Tools button, and then choose General Options.
Next, in any version of Excel check the Always Create Backup box, and then click OK, as illustrated in Figure 1. From this point on, whenever you save your workbook, Excel will automatically archive the previous version of the file. Keep in mind that this archive copy gets overwritten each time you save the primary copy of your file, so if you’re saving your work every 2 minutes, your back-up copy will only be two minutes old. However, this does give you a fallback position in case you do something to your Excel workbook that you can’t undo, such as deleting a worksheet.
Figure 1: Use the hidden General Options setting to create backups for specific workbooks.
To explore this further, let’s assume that your Excel workbook is called Cash Flow Projection.xlsx and you’ve saved it in the C:\Forecasts folder. The .xlsx extension may be unfamiliar to Excel 2003 users, as it was introduced in Excel 2007 to allow creating workbooks that have 16,384 columns and over one million rows. The .xls format for Excel 2003 and earlier limits spreadsheets to only 256 columns and 65,536 rows. No matter what document format you’re using, there are various ways to save an Excel workbook that you’re editing:
- Press Ctrl-S.
- Click the Save button on the Quick Access Toolbar (or press the Alt key to reveal its keyboard shortcut, which on most computers will be 1).
- Choose File, and then Save (or click the Office Button and choose Save in Excel 2007).
Any time you carry out one of these options, Excel will save the previous copy of your workbook as Backup of Cash Flow Projection.xlk. This archive copy will be located in the same C:\Forecasts folder as the original file, as illustrated in Figure 2. Unfortunately there’s no way to instruct Excel to save the archive copies in another location, so they’ll always exist in the same folder as your original file. Keep in mind that Excel only saves a single archive copy that gets overwritten each time you save. Conversely, QuickBooks gives you the option to specify how many back-up copies of your company that you’d like to preserve at any given time.
Figure 2: The.xlk back-up copy of your spreadsheet automatically saves to the same location as the “live” copy.
Due to this limitation, I often incorporate version numbers into my spreadsheets, such as Cash Flow Projection 1.01.xlsx. Whenever I make a significant update to the spreadsheet, I save the latest copy as Cash Flow Projection 1.02.xlsx, and so on. This in conjunction with the Always Create Backup can offer a higher level of fallback positions for key Excel workbooks.
These measures provide a first line of defense, but saving frequently is only part of the equation. For me, at least, there are times when I wish I could go back to how a spreadsheet looked a few minutes ago—before I last saved. This isn’t possible in Excel 2007 and earlier, but Excel 2010 and 2013 offer two helpful features:
- The ability to sometimes recover a workbook that you saved without closing
- The ability to sometimes open a previous version of your spreadsheet from the current work session
Note that I emphasized the word sometimes in the previous two sentences. The features I’m about to discuss don’t have your back 100% of the time, but I’ll show you how to change a setting that will improve your odds of having unexpected fallback positions:
- Excel 2010 and later: Choose File and then Options, as illustrated in Figure 3.
- Excel 2007: Click the Office button, and then choose Excel Options.
- Excel 2003 and earlier: Choose Tools and then Options.
- Excel 2011 for Mac: Choose Excel and then Preferences.
Select the Save section within Excel’s Options window and then change the Save AutoRecover Information option to every 2 minutes instead of every 10 minutes, as illustrated in Figure 3. As long as this option is enabled, all versions of Excel periodically create a back-up copy of your spreadsheet. In Excel 2007 and earlier, you’re presented with the option to access these temporary archives when you reopen Excel again after a program crash. Otherwise, the temporary copies are removed automatically.
It’s important to note that changing this setting to every 2 minutes does not mean that Excel will save your work every two minutes. However, in my experience it does significantly cause Excel to create these temporary copies more frequently. This, in turn, increases the odds of the Document Recovery Panel appearing after a program crash, and you might not lose as much work in Excel 2007 and earlier.
Figure 3: Change the AutoRecover information from 10 minutes to 2 minutes.
Now we’re ready to look at those two features I mentioned in Excel 2010 and 2013:
- Recover Unsaved Workbooks – This new command appears in two places in Excel 2010 and later. In Excel 2010 you will find it at the bottom of the Recent menu, and in Excel 2013 you’ll see a button for Recover Unsaved Workbooks on the Recent Workbooks menu. A second way to access this command is by choosing File, Info, and then clicking Manage Versions. The Recover Unsaved Workbooks command appears here, alongside an option to delete all unsaved workbooks. As shown in Figure 4, the Recover Unsaved Workbooks command displays an Open window that shows you files you worked on recently without saving. The names will be cryptic, but you can rely on the time stamp for a file to determine if one you worked on recently is still available. Based on my experience, files seem to stay in this folder for around four days before getting deleted automatically.
Figure 4: The Recover Unsaved Workbooks command allows you to view files you recently worked on without saving.
- Open a Prior Version – The aforementioned Info screen in Excel 2010 and later has a Versions section, as shown in Figure 5. This allows you to open prior copies of the workbook that you’ve been working on during the current Excel session. Keep in mind that these versions only exist during a given Excel session, but provide a fallback position for when you’ve been diligently saving your work and need to look back at how the document appeared prior to the last time you saved, or perhaps just earlier in your work session. Excel allows you to open these prior versions alongside your “live” copy of a workbook. This allows you to retrieve a worksheet that you deleted accidentally, recover from a wrong path that you might have headed down, or just compare two versions of the spreadsheet.
Figure 5: The Versions section allows you to view previous versions of a workbook that you are currently working on.
In summary, Excel puts the onus on backing up your work on you, which is different from QuickBooks. Do make sure that your QuickBooks data is set to back-up automatically either locally or online. Back-up copies of