Section 1.4. Saving Files | Excel 2010: The Missing Manual

1.4. Saving Files

As everyone who's been alive for at least three days knows , you should save your work early and often. Excel is no exception. You have two choices for saving a spreadsheet file:

Save As . This choice allows you to save your spreadsheet file with a new name. You can use Save As the first time you save a new spreadsheet, or you can use it to save a copy of your current spreadsheet with a new name , in a new folder, or as a different file type (alternate file formats are discussed below). To use Save As, select File Save As, or press F12. Figure 1-15 shows you the Save As dialog box.

Figure 1-15. The Save As dialog box lets you jump to common folders, or you can browse a folder tree using the drop-down "Save in" menu. Type the file name at the bottom of the window, and pick the file type Finally, choose Tools General Options from the top-right of the window for additional options.

Save . This option updates the spreadsheet file with your most recent changes. If you use Save on a new file that hasn't been saved before, it has the same effect as Save As: Excel prompts you to choose a folder and file name. To use Save, select File Save, or press Ctrl+S.

Tip: Resaving a spreadsheet is an almost instantaneous operation, and you should get used to doing it all the time. After you've made any significant change, just hit Ctrl+S to make sure you've stored the latest version of your data.

1.4.1. Saving Your Spreadsheet in Other Formats

Excel saves spreadsheets as .xls files (i.e., AirlineSilverware.xls). As hard- core Excel programming gurus know, this file format has a codename, BIFF8 (which, obviously, stands for Binary Interchange File Format). This is the format used by Excel 2003, Excel 2002, Excel 2000, and Excel 97. The codename is useful to know because earlier versions of Excel also store spreadsheets as .xls files, but the underlying format is actually differentand sometimes maddeningly irreconcilable.

For example, Excel 95 uses the incompatible BIFF7 file format, which means it can't open a BIFF8 spreadsheet at all. And while Excel 97 uses BIFF8, it doesn't support a feature called pivot charts , which were introduced in Excel 2000. Excel 97 can still open Excel 2000 workbooks that contain pivot charts, but it doesn't let you manipulate them.

The good news is that if you need to exchange spreadsheet files with somebody who's saddled with a Paleolithic spreadsheet application, you can save a copy of your spreadsheet in an older format. To do so, select File Save As. Then choose the desired format from the "Save as type" drop-down list and click Save. Excel allows you to save your spreadsheet using a variety of different formats, including the classic Lotus and dBase formats from the DOS world. If you're looking to view your spreadsheet using another program, use the CSV file type, which produces a comma-delimited text file that almost all spreadsheet applications on any operating system can read (comma-delimited means the information will have commas separating each cell ). Figure 1-16 shows the Save As dialog box.

Tip: When you save your Excel spreadsheet in another format, make sure you keep a copy in the standard .xls format. Why bother? Because other formats aren't guaranteed to retain all your information, particularly if you choose a format that doesn't support some of Excel's newer features.

1.4.2. Saving Your Spreadsheet with a Password

Occasionally, you might want to add confidential information to a spreadsheetfor example, a list of the airlines from which you've stolen spoons. If your computer is on a network, the solution may be as simple as storing your file in the correct, protected location. But if you're afraid that you might inadvertently email the spreadsheet to the wrong people (say, executives at American Airlines), or if you're about to expose systematic accounting irregularities in your company's year-end statements, you'll be happy to know that Excel provides a tighter degree of security. It allows you to password-protect your spreadsheets, which means anyone who wants to open them has to know the password you've set.

Figure 1-16. Excel offers a wide variety of file type options in the "Save as type" list, including options to save your Excel data as an HTML Web page or as an XML file that you can use in another application. You'll learn more about these features in Chapter 24 and Chapter 23, respectively.

Excel actually has two layers of password protection that you can apply to a spreadsheet:

You can prevent others from opening your spreadsheet unless they know the correct password. This level of security, which scrambles your data for anyone without the password (a process known as encryption ), is the strongest.
You can let others read a spreadsheet, but you can prevent them from modifying it unless they know the correct password.

You can apply one or both of these restrictions to a spreadsheet. Applying them is easy. Just follow these steps:

Select File Save As.

The Save As dialog box appears.
In the Save As dialog box, choose Tools General Options.

The Save Options dialog box appears.

Type a password next to the security level you want to turn on (as shown in Figure 1-17). Then click OK.

Figure 1-17. You can use any sequence of letters and numbers as a password. Passwords are case-sensitive (which means that PanAm is different from panam), and they are masked (which means that all that appears in the window as you type is a series of asterisks ).

POWER USERS' CLINIC
Is Your Encryption Strong Enough?

With Excel's normal level of encryption, you're not securing your data all that well. While the temp working in your office probably won't be able to see anything you've protected, people who really want to crack into your spreadsheets can use third-party programs to extract your data and password from a password-protected, encrypted file. Some of these third-party tools are expensive, but they are available.

If this risk disturbs you, consider stepping up to a stronger level of encryption. Excel offers several types of cryptographic services that encrypt spreadsheet data with industrial-strength algorithms. If you use these stronger forms of encryption, you won't be able to open your file in a version of Excel earlier than Excel XP. And if you lose your password, your data is also lost forever. The payoff, however, is near-bulletproof protection.

To use enhanced encryption, choose File Save As, and in the dialog box that appears, click the Advanced button next to the "Password to open" text box. In the new window that appears (shown below), you can choose the type of cryptography. The best type for compatibility and security is "RC4, Microsoft Base Cryptographic Provider v1.0." You can also choose a key length. (A key is a secret, random sequence of binary information that's used to encode the message. The longer the key, the harder it is to break your document open using a brute force attack).

Keep in mind that encryption is a complex topic, and even this approach won't necessarily stop a dedicated hacker with abundant computing resources. For best results, combine strong encryption with a password made up of at least 12 characters that incorporates letters and numbers and doesn't use any dictionary words.

Click Save to store the file.

If you use a password to restrict people from opening the spreadsheet, you are prompted to supply the "password to open" the next time you open the file (Figure 1-18 top).

If you use a password to restrict people from modifying the spreadsheet, the next time you open this file you'll be given the choiceshown in Figure 1-18 on the bottomto open it in read-only mode (which requires no password) or to open it in full edit mode (in which case you'll need to supply the "password to modify").

Figure 1-18. Top : You can give a spreadsheet two layers of protection: assign a "password to open" and you'll see this window when you open the file.
Bottom : If you assign a "password to modify," you'll see the choices in this window. If you use both passwords, you'll see both windows , one after the other.

1.4.3. Disaster Recovery

The corollary to the edict "Save your data early and often" is the truism "Sometimes it's not possible to catch everything before a sudden software, hardware, or power failure ends your Excel session early." Fortunately, Excel includes an invaluable safety net called AutoRecover.

AutoRecover periodically saves backup copies of your spreadsheet while you work. If you suffer a system crash, you can retrieve the last AutoRecover backup even if you never managed to save the file yourself. Of course, even the AutoRecover backup won't necessarily have all the information you entered in your spreadsheet before the problem occurred. But if AutoRecover saves a backup every 10 minutes (the standard), then at most you'll lose 10 minutes of work.

AutoRecover comes switched on when you install Excel, but if you want to check, select Tools Options, and then in the dialog box that appears, click the Save tab. Make sure that "Save AutoRecover info " is turned on. You can then choose a folder where you'd like Excel to save backup files (the standard folder works fine for most people, but feel free to pick some other place). Unfortunately, there's no handy Browse button to help you find the folder, so you need to find the folder you want in advance (using a tool like Windows Explorer), write it down somewhere, and then copy the full folder path into this dialog box. You can also adjust the backup frequency in minutes (see Figure 1-19 for tips on timing).

Figure 1-19. You can configure how often AutoRecover saves backups . There's really no danger in being too frequent. Unless you work with extremely complex or large spreadsheetswhich might suck up a lot of computing power and take a long time to saveyou can set Excel to save the document every five minutes with no appreciable slowdown .

If your computer does crash, when you get it running again, you can easily retrieve your last AutoRecover backup. In fact, once you restart Excel, it automatically checks the backup folder, and, if it finds a backup, it opens a special Document Recovery window on the left of the Excel window, as shown in Figure 1-20.

If you attempt to open a backup file that's somehow been scrambled (technically known as corrupted ), Excel automatically attempts to repair it. You can choose Show Repairs to display a list of any changes Excel had to make to recover the file.