Disaster Recovery and File-Related Prompts

In this chapter, I’ll show you various ways that you can minimize or, hopefully, mitigate spreadsheet disasters. The only saving grace I’ve observed when losing a spreadsheet is that the work goes faster the second time around—cold comfort indeed. Fortunately, there are steps you can take to bolster your defenses against spreadsheet crashes no matter whether you’re saving your files locally to your computer or online to the cloud. In addition, repeatedly clicking through prompts can take a toll on your workday and your wrists. In this chapter, I’ll discuss how several prompts that serve as speed bumps can work when you are opening spreadsheets.

In this chapter, you’ll learn about the following topics:

• Undo and Redo
• The AutoRecover feature
• AutoSave with OneDrive
• OneDrive version history
• The Always create backup setting
• Warning prompts when opening workbooks

By...

Technical requirements

Everything that I share in this chapter will work as described in Excel for Windows. A couple of the techniques work differently in Excel for macOS, or not at all. I’ll point out these distinctions within my discussion of each feature.

The different workbooks and the CSV file that I used in this chapter are available for download from GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter02.

Undo and Redo

No matter how assiduous we are about saving our work, often, we’re just one keystroke or mouse click away from a spreadsheet catastrophe. For instance, Excel can, and does, crash without notice, making your work suddenly vanish from the screen. However, that’s an extreme example—it’s much more likely that you’ll delete the wrong data or accidentally paste over the wrong area of your worksheet. In such instances, pressing Ctrl + Z (or ⌘ + Z in Excel for macOS) will put things back. If you want to undo up to the last 100 actions in your workbook, you can click on the Undo drop-down menu, as shown in Figure 2.1, and select a series of consecutive actions to reverse:

Figure 2.1 – Available actions to undo

One hidden benefit of Undo is the ability to redo the actions that you’ve undone. This means that you can roll a spreadsheet back up to 100 steps to see how things looked at that point, and...

The AutoRecover feature

Both the Windows and macOS versions of Excel offer an AutoRecover feature that automatically creates backup copies of your workbooks while you work. Excel for Windows offers customization and additional functionality not offered in Excel for macOS, so I’ll discuss both versions separately.

Excel for Windows AutoRecover

In Excel for Windows, you can specify the interval you wish Excel to use for creating backup copies of your work. The default value is every 10 minutes, but in my experience, this means backups get made in between 20 to 25 minutes. I don’t know about you, but I can complete a lot in Excel tasks in that amount of time, so I like to shorten the interval:

1. Choose File | Options | Save.
2. Make sure that the Save AutoRecover information checkbox is turned on.
3. Change the Save AutoRecover information setting from every 10 minutes to every 2 minutes instead, as shown in Figure 2.3:

Figure 2.3...

AutoSave with OneDrive

Older versions of Excel used to have a built-in AutoSave command that would periodically backup Excel workbooks. This feature fell by the wayside in the massive overhaul with Excel 2007. Fortunately, AutoSave is back for documents saved to Microsoft’s OneDrive and SharePoint Online services. If you have a Microsoft 365 subscription, then you already have a OneDrive account. Beyond that, anyone can get 5 GB of free storage by simply creating a free Microsoft account. If you need more space, home users can buy 100 GB of storage for $19.99/year, while OneDrive for Business starts at $5/user/month. Learn more or subscribe at www.onedrive.com.

Typically, files that you save to OneDrive are saved every few seconds, which means that it is much harder to lose your work. As shown in Figure 2.6, you can toggle AutoSave on or off on a workbook-by-workbook basis by way of the slider in the Quick Access Toolbar:

Figure 2.6 – The AutoSave...

The Always create backup setting

You can instruct Excel for Windows to create an automatic fallback position for critical workbooks. There are a few caveats:

• You must enable this setting on a workbook-by-workbook basis.
• Your backup copy is the most previously saved copy of your workbook.
• Excel creates a file that has a .XLK extension that it will not recognize when you open your backup file, which we'll discuss.
• The backup file must reside in the same folder as the original document, as there is no provision for saving backup files elsewhere.
• This feature is not available in Excel for macOS.

Let’s see how to establish an automatic backup for key workbooks:

1. Choose File | Save As | Browse or More Options.
2. Click on Tools in the Save As dialog box, to the left-hand side of the Save button.
3. Click on General Options.
4. Click on the Always Create Backup checkbox, as shown in Figure 2.12, and then click on OK.
5. Click on Save:
...

Repairing damaged workbooks

The prompt that no one ever wants to see in Excel is the one that says a workbook has been corrupted and cannot be opened. Of course, there are varying degrees of workbook corruption. Low levels of damage can result in odd behaviors or prompts as you work on the document, while more severe damage can render the workbook inaccessible. In such situations, you might be able to repair the workbook in Excel for Windows. Make sure that you close the affected file because you cannot repair files that are currently open in Excel. Once you’ve done so, perform the following steps:

1. Choose File | Open.
2. Click on the arrow on the right-hand side of the Open button and choose Open and Repair..., as shown in Figure 2.14:

Figure 2.14 – The Open and Repair command

3. Click on Repair, as shown in Figure 2.15:

Figure 2.15 – The Open and Repair dialog box

4. Hopefully, Excel will display...

Warning prompts when opening workbooks

In 1999, the Melissa virus infected countless computers around the world. The virus spreads itself in the form of infected Microsoft Word documents. Over the years, Microsoft added several levels of defense to their products to prevent a recurrence of a similar virus. The trade-off is that users are often subject to warning prompts every time they open a spreadsheet.

In this section, I’ll discuss the most frequent prompts that appear and how you can manage the risks of working with spreadsheets of unknown provenance. First, let’s discuss the Protected View feature in Excel for Windows.

Protected View

Protected View first appeared in Excel 2007. It is designed to allow you to safely open and review a document in a sandbox environment that prevents it from making any changes to your computer. As shown in Figure 2.17, you can view but not edit the document when it is in Protected View:

Figure 2.17 –...

Summary

In this chapter, you learned about several ways to set up a series of defenses against mishaps in your Excel spreadsheets. Undo and Redo allow you to quickly respond at that moment, unless an Excel command or feature removes that safety net without notice. The AutoRecover and AutoSave commands enable you to for keeping backup copies of your work offline or in the cloud, respectively. The Always Create Backup setting provides an additional fallback position for critical workbooks that are saved locally.

Also, you saw how to dispatch the flurry of warning prompts that can appear when you open an Excel workbook that contains data connections, workbook links, or macros. Well-intentioned security prompts can interrupt your workflow or worse, stymy your work if you don’t understand the prompt.

We’ll continue our quest for improved productivity in Chapter 3, Quick Access Toolbar Treasures, where I’ll show you how to streamline common tasks with icons and...