Excel, of course, uses files to store its workbooks. This chapter discusses how Excel uses files and provides an overview of the various types of files.
It also discusses the new Excel 2007 file formats and describes how to determine what (if anything) will be lost if you save your workbook in an earlier file format.
This section describes the operations that you perform with workbook files: opening, saving, closing, deleting, and so on.
As you read through this section, remember that you can have any number of workbooks open simultaneously, and that only one workbook is the active workbook at any given time. The workbook’s name appears in its title bar (or in the Excel title bar if the workbook is maximized).
When you start Excel normally, it automatically creates a new (empty) workbook called Book1. This workbook exists only in memory and has not been saved to disk. By default, this workbook consists of three worksheets named Sheet1, Sheet2, and Sheet3. If you’re starting a new project from scratch, you can use this blank workbook.
Excel provides two ways to create a new workbook:
Choose Office
New (which opens the New Workbook dialog box (see Figure 8.1). This dialog box lets you create a blank workbook, a workbook based on a template, or a workbook based on an existing workbook. To create a new blank workbook, select Blank Workbook and click Create.Press the Ctrl+N shortcut key combination. This method is the fastest way to start a new workbook because it bypasses the New Workbook dialog box.
Cross-Ref
Refer to Chapter 9 for more information about using and creating templates.
Following are some of the ways to open a workbook saved on your hard drive:
Click the Office button and select the file you want from the Recent Documents list. Only the most recently used files are listed. You can specify the number of files to display (maximum of 50) in Advanced section of the Excel Options dialog box.
Locate the Excel workbook file using a Windows Explorer file list. Just double-click the filename (or icon), and the workbook opens in Excel. If Excel is not running, Windows automatically starts Excel and loads the workbook file.
Choose Office
Open to display the Open dialog box.Press the Ctrl+O shortcut key combination to display the Open dialog box.
The Open dialog box is shown in Figure 8.2. Note that this dialog box is resizable. To make it larger or smaller, click the lower-right corner and drag.
To open a workbook from the Open dialog box, you must provide two pieces of information: the name of the workbook file (specified in the File Name field) and its folder (specified in the Look In field).
Once you’ve located and selected the file, click Open, and the file opens. You also can just double-click the filename to open it.
Notice that the Open button is actually a drop-down list. Click the arrow, and you see the additional options:
Open: Opens the file normally.
Open Read-Only: Opens the selected file in read-only mode. When a file is opened in this mode, you can’t save changes to the original filename.
Open as Copy: Opens a copy of the selected file. If the file is named
budget.xlsx, the workbook that opens is namedCopy(1)budget.xlsxOpen in Browser: Opens the file in your default Web browser. If the file can’t be opened in a browser, this option is grayed out.
Open and Repair: Attempts to open a file that may be damaged and recover information contained in it.
Tip
You can hold down the Ctrl key and select multiple workbooks. When you click Open, all the selected workbook files open.
Right-clicking a filename in the Open dialog box displays a shortcut menu with many extra Windows commands. For example, you can copy the file, delete it, rename it, modify its properties, and so on.
The Look In field at the top of the Open dialog box is actually a drop-down list. Click the arrow, and the box expands to show your folders. You can select a different drive or directory from this list.
To move up one level in the folder hierarchy, click the Up One Level (Alt+2) icon to the right of the Look In box.
The left side of the Open dialog box is known as the My Places bar, and it contains a list of shortcuts to folders. To add a new folder to the My Places bar, activate the folder in the Open dialog box, then right-click the My Places bar, and click the Add option. It’s a good idea to customize the My Places bar by adding shortcuts to folders that you use frequently.
Right-click any of the My Places items, and you’ll have some additional options that enable you to rearrange the items, rename them, delete them, or change the icon size.
At the bottom of the Open dialog box is a drop-down list labeled Files Of Type. When the Open dialog box is displayed, it shows All Excel Files (and a long list of file extensions). The Open dialog box displays only the files that match the extensions. In other words, you see only standard Excel files.
If you want to open a file of a different type, click the arrow in the drop-down list and select the file type that you want to open. This changes the filtering and displays only files of the type that you specify.
You can also type a filter directly in the File Name box. For example, typing the following will display only files that have an XLSX extension (press Tab after typing the filter): *.xlsx
The Open dialog box can display your workbook filenames in several different styles. You control the style by clicking the Views icon and then selecting from the drop-down list (see Figure 8.3). The style that you choose is entirely up to you.
Tip
When you click the Office button, each file in the recent file list displays a pushpin icon. If you click the icon, that file is “pinned” to the list and will always appear. This handy feature ensures that commonly used files always appear on the recent file list—even if you haven’t opened the file recently.
Opening Workbooks Automatically
Many people work on the same workbooks each day. If this describes you, you’ll be happy to know that you can have Excel open specific workbook files automatically whenever you start Excel.
The XLStart folder is located within your Excel document folder. For example, the path may be
C:Documents and Settings<username>Application DataMicrosoftExcelXLStart
Another XLStart folder may be located here:
C:Program FilesMicrosoft OfficeOffice12XLStart
Any workbook files (excluding template files) stored in either of these XLStart folders open automatically when Excel starts. If one or more files open automatically from an XLStart folder, Excel won’t start up with a blank workbook.
You can specify an alternate startup folder in addition to the XLStart folder. Choose Office Excel Options and select the Advanced tab. Scroll down to the General section and enter a new folder name in the field labeled At Startup, Open All Files In. After you do that, when you start Excel, it automatically opens all workbook files in both the XLStart folders and the alternate folder that you specified.
When you’re working on a workbook, it’s vulnerable to day-ruining events, such as power failures and system crashes. Therefore, you should save your work often. Saving a file takes only a few seconds, but re-creating hours of lost work takes many hours.
Excel provides four ways to save your workbook:
Choose Office
Save.Click the Save icon on the Quick Access Toolbar.
Press the Ctrl+S shortcut key combination.
Press the Shift+F12 shortcut key combination.
If your workbook has already been saved, it’s saved again using the same filename.
Caution
Remember that saving a file overwrites the previous version of the file on your hard drive. If you open a workbook and then completely mess it up, don’t save the file! Instead, close the workbook without saving it and then reopen the good copy on your hard drive.
If you want to save the workbook to a new file, choose Office Save As (or press F12).
If your workbook has never been saved, its title bar displays a default name, such as Book1 or Book2. Although Excel allows you to use these generic workbook names for filenames, you’ll be better off using more descriptive filenames. Therefore, the first time that you save a new workbook, Excel displays the Save As dialog box to let you provide a more meaningful name.
The Save As dialog box is similar to the Open dialog box. Again, you need to specify two pieces of information: the workbook’s name and the folder in which to store it. If you want to save the file to a different folder, select the desired folder in the Save In field. If you want to create a new folder, click the Create New Folder icon in the Save As dialog box. The new folder is created within the folder that’s displayed in the Save In field.
After you select the folder, enter the filename in the File Name field. You don’t need to specify a file extension—Excel adds it automatically, based on the file type specified in the Save As Type field. By default, files are saved in the standard Excel file format, which uses an .xlst file extension.
Tip
To change the default file format for saving files, access the Excel Options dialog box. Click the Save tab and change the setting for the Save Files In This Format option.
If a file with the same name already exists in the folder that you specify, Excel asks whether you want to overwrite that file with the new file. Be careful: You can’t recover the previous file after you overwrite it.
The Excel AutoRecover feature automatically saves a backup copy of your work at a predetermined time interval. This feature can be a lifesaver if Excel crashes or you experience a power outage. You can turn this feature on and off and also specify the save time interval in the Save tab of the Excel Options dialog box. The default AutoRecover interval is 10 minutes.
Keep in mind that AutoRecover does not overwrite your actual file. Rather, it saves a copy of the file. Therefore, you should continue to save your work at frequent intervals, even if AutoRecover is turned on.
When you restart Excel after it crashes, you see a list of documents that were open at the time of the crash. You can then choose to open the original version or the AutoRecovered version.
In some cases, you may want to specify a password for your workbook. When a user attempts to open a password-protected workbook, a password must be entered before the file is opened.
For some reason, it seems that Microsoft wants to keep the password-protection feature a secret. Password-protecting a workbook isn’t at all obvious. Here’s how to do it.
Choose Office
Save As to display the Save As dialog box.In the Save As dialog box, click the drop-down button labeled Tools
In the Tools drop-down list, select General Options to display the General Options dialog box, shown in Figure 8.4.
Type the password to open and click OK. You’re asked to re-enter the password.
When you return to the Save As dialog box, click the Save button to save the file.
Caution
Passwords can be up to 15 characters long and are case sensitive. Be careful with this option because it is impossible to open the workbook (using normal methods) if you forget the password. Also, remember that Excel passwords can be cracked, so it’s not a perfect security measure.
Cross-Ref
For additional security, you may prefer to encrypt your workbook. See “Encrypting a document,” later in this chapter.
The General Options dialog box has a few other features.
Always Create Backup: If this option is checked, the existing version of the workbook is renamed before the workbook is saved. The new filename will be named “Backup of xxx.xlk,” where xxx represents the original filename. Creating a backup enables you to go back to the previously saved version of your workbook.
Password To Modify: This option enables you to specify a password that will be required before changes to the workbook can be saved under the same filename. Use this option if you want to make sure that changes aren’t made to the original version of the workbook.
Read-Only Recommended: If this option is checked, Excel presents a dialog box suggesting that the file be opened as read-only. This is just a gentle suggestion. The person opening the file can override this suggestion if he or she likes.
Choose Office Prepare, and you’ll find still more file-related options, as shown in Figure 8.5. These options, described in the following sections, may be useful if you plan to distribute your workbook to others.
The Office Prepare Properties command adds a new panel directly below the Ribbon. As you can see in Figure 8.6, Excel is able to store some additional “metadata” about the file. This information includes such items as the author, title, subject, and so on.
This file information can be useful if you remember to enter the data. For example, when you use the Open dialog box, you can specify Properties as your Views option. Then, the dialog box displays the metadata when you select a file (see Figure 8.7).
If you click the drop-down in the Properties title bar, you can choose Advanced, which displays the Properties dialog box. This dialog box has five tabs:
General: Displays general information about the file—its name, size, location, date created, and so on. You can’t change any of the information in this panel.
Summary: Contains nine fields of information that you can enter and modify. Some of these fields are the same as those in the Properties panel.
Statistics: Shows additional information about the file and can’t be changed.
Contents: Displays the names of the sheets in the workbook, as well as the named ranges.
Custom: This tab enables you to store, in a sort of database, a variety of information about the file. For example, if the workbook deals with a client named Smith and Jones Corp., you can keep track of this bit of information and use it to help locate the file later.
The Office Prepare Inspect Document command displays the Document Inspector dialog box. This feature can alert you to some potentially private information that may be contained in your workbook—perhaps information that’s contained in hidden rows or columns or hidden worksheets.
If you plan on making a workbook available to a large audience, it’s an excellent idea to use the Document Inspector for a final check.
Earlier in this chapter, in “Specifying a password,” I described how to set a password for a workbook. To provide a higher level of security, you may prefer to encrypt your workbook using Office Prepare Encrypt Document. When you choose this command, you’ll be prompted for a password, which you must enter twice.
Excel supports Information Rights Management (IRM), which allows you to restrict access to workbooks in order to prevent sensitive information from being printed, forwarded, or copied.
This feature, accessed by using Office Prepare Restrict Permission, is fairly complex and requires additional software. Consult the Help system for additional details.
The Office Prepare Add A Digital Signature command allows you to “sign” a workbook.
Cross-Ref
Refer to Chapter 31 for more information about digital signatures.
Choose Office Prepare Mark As Final to mark a workbook as finalized. A finalized document is marked read-only, and you can’t edit or modify it. When you open a finalized document, the status bar displays an additional icon, and all editing command are disabled.
When you save a workbook using an older file format, Excel displays its very helpful Compatibility Checker dialog box, shown in Figure 8.8. You can also display this dialog box at any time by choosing Office Prepare Run Compatibility Checker.
It’s important to understand the limitations regarding version compatibility. Even though your colleague is able to open your file, there is no guarantee that everything will function correctly or look the same. You can’t expect features that are new to Excel 2007 to work in earlier versions. For example, you’ll find that a SmartArt diagram is converted to a picture, table formatting is lost, and charts may look different. In addition, formulas that use any of the new worksheet functions will return an error.
The Compatibility Checker identifies the elements of your workbook that will result in loss of functionality or fidelity (cosmetics). To display the Compatibility Checker results in a more readable format, click Copy To New Sheet.
For more information about file compatibility, see “Excel File Compatibility,” later in this chapter.
After you’re finished with a workbook, you should close it to free the memory that it uses. You can close a workbook by using any of the following methods:
Choose Office
Close.Click the Close button (the X) in the workbook’s title bar.
Double-click the Excel icon on the left side of the workbook’s title bar.
Press the Ctrl+F4 shortcut key.
Press the Ctrl+W shortcut key.
If you’ve made any changes to your workbook since it was last saved, Excel asks whether you want to save the changes to the workbook before closing it.
Nothing is worse than spending hours creating a complicated Excel workbook only to have it destroyed by a power failure, a hard-drive crash, or even human error. Fortunately, protecting yourself from these disasters is not a difficult task.
Earlier in the chapter, I discussed the AutoRecover feature that makes Excel save a backup copy of your workbook at regular intervals (see “Using AutoRecover”). I also mentioned the Always Create Backup option in the General Options dialog box. These are good ideas, but they certainly aren’t the only backup protection you should use. If a file is truly important, you need to take extra steps to ensure its safety. The following backup options help ensure the safety of individual files:
Keep a backup copy of the file on the same drive. Essentially what happens when you select the Always Create A Backup option when you save a workbook file. Although this option offers some protection if you make a mess of the worksheet, it won’t do you any good if the entire hard drive crashes.
Keep a backup copy on a different hard drive. Assumes, of course, that your system has more than one hard drive. This option offers more protection than the preceding method, because the likelihood that both hard drives will fail is remote. If the entire system is destroyed or stolen, however, you’re out of luck.
Keep a backup copy on a network server. Assumes that your system is connected to a server on which you can write files. This method is fairly safe. If the network server is located in the same building, however, you’re at risk if the entire building burns down or is otherwise destroyed.
Keep a backup copy on a removable medium. The safest method. Using a removable medium, such as a CD-ROM, enables you to physically take the backup to another location. So, if your system (or the entire building) is damaged, your backup copy remains intact.
Perhaps one of the most confusing aspects of Excel is the nearly overwhelming number of files formats that it can read and write. With the introduction of Excel 2007, things got even more confusing because it has quite a few new files formats.
Excel’s new file formats are
XLSX: A workbook file that does not contain macros
XLSM: A workbook file that contains macros
XLTX: A workbook template file that does not contain macros
XLTM: A workbook template file that contains macros
XLSA: An add-in file
XLSB: A binary file similar to the old XLS format but able to accommodate the new features
XLSK: A backup file
With the exception of XLSB, these are all “open” XLM files, which means that other applications are able to read and write these types of files.
To save a file for use with an older version of Excel, choose Office Save As and select one of the following from the Save As Type drop-down:
Excel 97-2003 Workbook (*.xls): If the file will be used by someone who has Excel 97, Excel 2000, Excel 2002, or Excel 2003.
Microsoft Excel 5.0/95 Workbook (*.xls): If the file will be used by someone who has Excel 5 or Excel 95.
Note
If the workbook will be used by someone who has installed the Office 2007 Compatibility Pack, you don’t need to save it using an earlier file format (see “The Office 2007 Compatibility Pack” sidebar for details).