Excel 2003 for Starters: The Missing Manual

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 lets you 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 covered on Section 1.4.1). To use Save As, select File → Save As (or press F12) to display the Save As dialog box shown in Figure 1-14. Excel suggests you save your file in the My Documents folder, but you can save your document in whatever folder you like. To specify a folder, either click the icons on the left-hand side of the box, or choose from the drop-down "Save in" box. When you finish choosing a place to save your file, zip to the bottom of the Save As dialog box: here you type the file name and, finally, pick the file type.

Figure 1-14. At the bottom of the Save As dialog box, you can type in a file name and choose a file type.

• 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 if you tend toward paranoia, 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. (You may also want to check out Excel's AutoRecover option, covered on Section 1.4.3.)

Saving Your Spreadsheet in Other Formats

Excel saves spreadsheets as .xlsfiles (for example, AirlineSilverware.xls). As hardcore 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 different—and 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 format you want from the "Save as type" drop-down list and click Save. Excel lets you 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 programs on any operating system can read (comma-delimited means the information will have commas separating each cell). Figure 1-15 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.

Figure 1-15. 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.

Saving Your Spreadsheet with a Password

Occasionally, you might want to add confidential information to a spreadsheet—for 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.

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. Doing so is easy—just follow these steps:

1. Select File → Save As.

   The Save As dialog box appears.

2. In the Save As dialog box, choose Tools → General Options.

   The Save Options dialog box appears.

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

   

   Figure 1-16. To thwart eagle-eyed coworkers or family members, Excel masks your password (displays asterisks in the window as you type).

   Note

   You can use any sequence of letters and numbers as a password. Passwords are case-sensitive, which means that PanAm is different from panam.

4. Click Save to store the file.

   If you use a password to restrict people from opening the spreadsheet, Excel prompts you to supply the "password to open" the next time you open the file (Figure 1-17, 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 choice—shown in Figure 1-17 on the bottom—to open it in read-only mode (which requires no password) or to open it in full-edit mode (in which case you need to supply the "password to modify").

Figure 1-17. Top: Assign a "password to open" and you see this window when you open the file. Bottom: If you assign a "password to modify," you see the choices in this window.

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), you'll lose, at most, 10 minutes of work.

Note

There's really no danger in backing up too frequently. Unless you work with extremely complex or large spreadsheets—which may suck up a lot of computing power and take a long time to save—you can set Excel to save the document every five minutes with no appreciable slowdown.

AutoRecover comes switched on when you install Excel, but if you want to check, select Tools → Options (make sure you've got an open spreadsheet file that you've saved at least once), 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 folder Excel suggests—C:\Documents and Settings\(YourName)\Application Data\Microsoft\Excel\—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-18).

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-19.

Figure 1-18. Clicking Tools → Options → Save displays this window, which lets you configure how often (and where) AutoRecover saves backups.

In the Document Recovery window, [Recovered] means the file backup is ready and error-free, while [Original] indicates the file was saved by you and Excel didn't make a backup after your last save. (The only time you see the original file in the Document Recovery window is when Excel—or your computer—crashes, but there isn't any unsaved data or backup file. In this situation, you don't need to worry since you haven't lost any data.)

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.

Figure 1-19. Top: Every time Excel starts up, it looks for AutoRecover backups. Bottom: You can save or open an AutoRecover backup like an ordinary Excel file; simply click the item in the list.