Chapter 7. Understanding Excel Files

IN THIS CHAPTER

Creating a new workbook

Opening an existing workbook

Saving and closing workbooks

Sharing workbooks with those who use an older version of Excel

Excel, of course, uses files to store its workbooks. This chapter describes the operations that you perform with workbook files: opening, saving, closing, deleting, and so on. It discusses how Excel uses files and provides an overview of the various types of files. Most of the file operations discussed here occur in the new Backstage view, the screen that you see when you click File on the Excel Ribbon.

This chapter also discusses the Excel 2007 and Excel 2010 file formats and describes how to determine what (if anything) will be lost if you save your workbook in an earlier file format.

As you read through this chapter, 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).

7.1. Creating a New Workbook

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 project from scratch, you can use this blank workbook.

While you're working in Excel, you can create a new (empty) workbook at any time. Excel provides two ways to create a new workbook:

• Choose File New, which displays a screen like the one shown see Figure 7.1. This screen 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 then click the Create button.

• Press Ctrl+N. This shortcut is the fastest way to start a new workbook if you're not using a template.

Figure 7.1. Choosing File New presents you with options for creating a new workbook.

7.2. Opening an Existing Workbook

Following are some of the ways to open a workbook saved on your hard drive:

• Choose File Recent and then select the file you want from the Recent Workbooks list. Only the most recently used files are listed. You can specify the number of files to display (maximum of 50) in the Advanced section of the Excel Options dialog box.

• Locate the Excel workbook file via 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.

• Use either of the following methods to display the Open dialog box:

  • Choose File Open.

  • Press Ctrl+O.

  From this dialog box, you can browse your computer for workbooks.

When you choose File Recent, each file in the recent workbooks list displays a pushpin icon on the right. Click the pushpin icon, and that file becomes "pinned" to the list and will always appear at the top of the list. This handy feature ensures that important files always appear on the recent workbooks list — even if you haven't opened the file recently.

Also, notice that you can right-click a workbook in the list and choose Remove from List. Or, choose Clear Unpinned Items to clear the list and start fresh.

The Open dialog box, shown in Figure 7.2, is resizable. To make it larger or smaller, click the lower-right corner and drag. Also, notice that it's virtually identical to a Windows Explorer window. The only difference is that it includes additional controls at the bottom.

Figure 7.2. Use the Open dialog box to open any of your Excel workbook files.

To open a workbook from the Open dialog box, use the folder tree display on the left to locate the folder that contains the file, and then select the workbook file from the list on the right. After you locate and select the file, click Open, and the file opens. Or, 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 with 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 named Copy(1)budget.xlsx.

• Open in Browser: Opens the file in your default Web browser. If the file can't be opened in a browser, this option is disabled.

• Open in Protected View: Opens the file in a special mode that does not allow editing. In this view, most of the Excel Ribbon commands are disabled. Read more about this new feature in the sidebar, "About Protected View."

• Open and Repair: Attempts to open a file that may be damaged and recover information contained in it.

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, delete, or rename the file, modify its properties, and so on.

7.2.1. Using the Favorite Links

Your Favorite Links list appears at the top of the left panel of the Open dialog box. It contains a list of shortcuts to folders on your system. Initially, it's just a partial list of folders, but you can click More to expand the list.

To add a new folder to the Favorite Links section, click the folder in the Open dialog box and drag it into the Favorite Links area. Customizing the Favorite Links section by adding shortcuts to folders that you use frequently can save you lots of clicking.

7.2.2. Filtering filenames

At the bottom of the Open dialog box is a button with a drop-down list. When the Open dialog box is displayed, this button shows All Excel Files (and a long list of file extensions). The Open dialog box displays only those 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 Enter after typing the filter): *.xlsx.

C:Users<username>AppDataRoamingMicrosoftExcelXLSTART

C:Program FilesMicrosoft OfficeOffice14XLStart

7.2.3. Choosing your file display preferences

The Open dialog box can display your workbook filenames in several different styles: as a list, with complete details, as icons, and so on. You control the style by clicking the Views icon and then selecting from the drop-down list. The style that you choose is entirely up to you.

7.3. Saving a Workbook

When you're working in Excel, your workbook is 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:

• Click the Save icon on the Quick Access toolbar.

• Press Ctrl+S.

• Press Shift+F12.

• Choose File Save.

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 your workbook has already been saved, it's saved again using the same filename. If you want to save the workbook to a new file, choose File 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. Select the desired folder in the folder list on the left. 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 .xlsx file extension.

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. For example, if your workbooks must be compatible with older versions of Excel, you can change the default format to Excel 97-2003 Workbook (*.xls). Doing so eliminates the need to select the older file type every time you save a new workbook.

If your workbook contains VBA macros, saving it with an .xlsx file extension will erase all the macros. It must be saved with an .xlsm extension (or saved in the XLS or XLSB format)). If your workbook has macros, Excel will still propose to save it as an XLSX file. It other words, Excel suggests a file format that will destroy your macros! It will, however, warn you that the macros will be lost.

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.

7.4. Using AutoRecover

If you've used computers for any length of time, you've probably lost some work. You forgot to save a file, or maybe the power went out and your unsaved work was lost. Or maybe you were working on something and didn't think it was important, so you closed it without saving — and later realized that it was important. A new feature in Excel 2010 called Versions might make these types of "D'oh!" moments less frequent.

As you work in Excel, your work is periodically saved, automatically. It happens in the background so you don't even know that it's happening. What's new in Excel 2010 is that you can access these autosaved versions of your work. And this even applies to workbooks that you never explicitly saved.

The Versions feature consists of two components:

• Versions of a workbook are saved automatically, and you can view them.

• Workbooks that you closed without saving are saved as draft versions

7.4.1. Recovering versions of the current workbook

To see whether any previous versions of the current workbook are available, choose File Info. The Versions section Versions lists the available old versions (if any) of the current workbook. In some cases, more than one autosaved version will be listed. In other cases, no autosaved versions will be available.

You can open an autosaved version by clicking its name. Remember that opening an autosaved version won't automatically replace the current version of your workbook. Therefore, you can decide whether the autosaved version is preferable to the current version. Or, you can just copy some information that may have been accidentally deleted, and paste it to your current workbook.

When you close the workbook, the autosaved versions are deleted.

7.4.2. Recovering unsaved work

When you close a workbook without saving your changes, Excel asks whether you're sure. If that unsaved workbook has an autosaved version, the "Are you sure?" dialog box informs you of that fact.

To recover a workbook that you closed without saving, choose File Info Versions, and choose Recover Draft Versions. You'll see a list of all draft versions of your workbooks. You can open them and (hopefully) recover something that you needed. These drafts are also listed in the recent file list, displayed when you choose File Recent.

Draft versions are deleted after four days, or until you edit the file.

7.5. Specifying a Password

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.

To set a password for a workbook, choose File Info, and click the Protect Workbook button. This button displays some additional options. Choose Encrypt With Password. Excel displays the Encrypt Document dialog box, shown in Figure 7.3. Enter the password, and then enter it again. Click OK, and save the workbook.

When you re-open the workbook, you will be prompted for a password.

Figure 7.3. The Encrypt Document dialog box is where you specify a password for your workbook.

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

7.6. Organizing Your Files

If you have hundreds of Excel files, you might have a problem locating the workbook that you need. Using descriptive filenames can help, and using folders and subfolders (with descriptive names) also makes it easier to find the particular file you need. In some cases, though, that's not enough.

Fortunately, Excel lets you assign a variety of descriptive information (sometimes known as metadata) to a workbook. These are known as document properties. This information includes such items as the author, title, subject, and so on.

When you choose File Info you can view (or modify) the document properties for the active workbook. This information is shown on the right side of the screen (see Figure 7.4).

If you aren't seeing all the properties for your workbook, click the down-arrow on Properties and select Show All Properties Another option on the Document Properties drop-down list is Show Document Panel. Choosing this command displays the properties in a panel below the Ribbon.

When you use the Open dialog box, you can specify additional columns to display. Start by clicking Views (at the top of the Open dialog box) and then choose Details. Right-click the column header to see a list of other properties to include. Click the More option for a longer list of properties (see Figure 7.5).

You can sort the file list in the Open dialog box by a particular column by clicking the column heading. Also, notice that each heading is actually a drop-down list. Click the drop-down arrow, and you can filter the list to show only files that match the selected properties.

Using document properties lets you work with files as if they were in a database. The key, of course, is taking the time to ensure that the document properties are actually assigned, and are accurate.

If you would like to be prompted to ensure that the document properties are correct, choose Developer Modify Document Panel. In the Document Information Panel dialog box, select the Always Show Document Information Panel on Document Open and Initial Save check box. If the Developer tab isn't visible, use the Customize Ribbon panel in the Excel Options dialog box to display it.

Figure 7.4. Use Properties to store additional information about your workbook.

Figure 7.5. The Choose Details dialog box, where you choose additional properties to display in the file list.

7.7. Other Workbook Info Options

The Info pane of Backstage View displays more file-related options, as shown in Figure 7.6. To display this pane, choose File Info. These options, described in the following sections, may be useful if you plan to distribute your workbook to others. Note that not all workbooks display all the options shown in Figure 7.6. Only the relevant options are shown.

7.7.1. Security Warning section

If the active workbook displayed a security warning when you opened it, the Info pane will display a Security Warning section, with an Enable Content button. Use this panel to find out more about why Excel triggered the security warning, and to enable the content if you're sure that it's safe to do so.

Figure 7.6. Choose File Info for additional options.

7.7.2. Compatibility Mode section

If the active workbook is an old workbook opened in compatibility mode, you'll see the Compatibility Mode section in the Info pane. To convert the workbook to the Excel 2007/2010 format, click the Convert button.

Be aware that this command deletes the original version of the file — which seems like a rather drastic measure. It's probably wise to make a copy of your workbook before you use this command.

7.7.3. Permissions section

In the Permissions section of the Info pane, click the Protect Workbook button to display the following options:

• Mark as Final

  Use this option to designate the workbook as "final." The document is saved as a read-only file to prevent changes. This isn't a security feature. Rather, the Mark as Final command is useful to let others know that you are sharing a completed version of a workbook.

• Encrypt with Password

  Use this command to specify a password that is required to open the workbook. See "Specifying a password," earlier in this chapter.

• Protect Current Sheet

  This command lets you protect various elements of a worksheet. It displays the same dialog box as the Review Changes Protect Sheet command. See Chapter 31 for more information about protecting worksheets.

• Protect Workbook Structure

  This command lets you protect the structure of a workbook. It displays the same dialog box as Review Changes Protect Workbook. See Chapter 31 for more information for more information about protecting the structure of a workbook.

• Restrict Permission by People

  Use this option to specify those who may open the document. This feature requires a fee-based service called Information Rights Management.

• Add a Digital Signature

  This command allows you to "sign" a workbook digitally. See Chapter 31 for more information about digital signatures.

7.7.4. Prepare for Sharing section

The Prepare for Sharing section of the Info pane contains a Check for Issues button. When clicked, the button displays three options:

• Inspect Document

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

• Check Accessibility

  This command checks the workbook for potential problems that might occur for people with disabilities. The results of the check are displayed in a task pane in the workbook. This feature works only with Excel 2007/2010 workbooks.

• Check Compatibility

  This command is useful if you need to save your workbook in an older file format. It displays a very helpful Compatibility Checker dialog box that lists potential compatibility problems. This dialog box also appears when you save a workbook using an older file format. For more information, see "Excel File Compatibility," later in this chapter.

7.7.5. Versions section

Clicking the Manage Versions button of the Versions section of the Info pane displays two options:

• Recover Draft Versions

  This command displays the Open dialog box, pointed to the folder where Excel displays AutoRecover versions of workbooks that were not saved. If you accidentally forget to save a file, this command may help you out.

• Delete All Draft Versions

  This command simply deletes all unsaved files on your drive. Alternatively, you can delete the files manually.

7.8. Closing Workbooks

After you're finished with a workbook, you can close it to free the memory that it uses. You can close a workbook by using any of the following methods:

• Choose File 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. This icon is visible only if the workbook window is not maximized.

• Press Ctrl+F4.

• Press Ctrl+W.

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.

When you close Excel, you are prompted to save each workbook that hasn't been saved. The dialog box has a Save All button, but it doesn't have a Don't Save Anything button. If you'd like a quick exit, without saving any workbooks, press Shift while you click the Don't Save button. Excel shuts down immediately.

7.9. Safeguarding Your Work

Nothing is more frustrating 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 discuss the AutoRecover feature that makes Excel save a backup copy of your workbook at regular intervals (see "Using AutoRecover"). AutoRecover is a good idea, but it certainly isn't the only backup protection you should use. If a workbook is 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. Although this option offers some protection if you make a mess of the workbook, it won't do you any good if the entire hard drive crashes.

• Keep a backup copy on a different hard drive. This method 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. This method 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 an Internet backup site. Several Web sites specialize in storing backup files. This is a safe method, as long as the company doesn't go out of business.

• Keep a backup copy on a removable medium. This is the safest method. Using a removable medium, such as a USB drive 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.

7.10. Excel File Compatibility

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.

7.10.1. Checking compatibility

If you save your workbook to an older file format (such as XLS, for versions prior to Excel 2007), Excel automatically runs the Compatibility Checker. The Compatibility Checker identifies the elements of your workbook that will result in loss of functionality or fidelity (cosmetics).

Figure 7.7 shows the Compatibility Checker dialog box. Use the Select Versions to Show button to limit the compatibility checking to a specific version of Excel.

Figure 7.7. The Compatibility Checker is a useful tool for those who share workbooks with others.

The bottom part of the Compatibility Checker lists the potential compatibility problems. To display the results in a more readable format, click the Copy to New Sheet button.

Keep in mind that compatibility problems can also occur with Excel 2007 even though that version uses the same file format as Excel 2010. You can't expect features that are new to Excel 2010 to work in earlier versions. For example, if you create a workbook with Sparkline charts (a new feature in Excel 2010) and send it to a colleague who uses Excel 2007, the cells that contain the Sparklines will be empty. In addition, formulas that use any of the new worksheet functions will return an error. The Compatibility Checker identifies these types of problems.

Perhaps one of the most confusing aspects of Excel is the nearly overwhelming number of file 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 file formats.

7.10.2. Recognizing the Excel 2010 file formats

Excel file formats (which were introduced in Excel 2007) 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" XML files, which means that other applications are able to read and write these types of files.

XML files are actually Zip-compressed text files. If you rename one of these files to have a ZIP extension, you'll be able to examine the contents using any of several zip file utilities — including the Zip file support built into Windows. Taking a look at the innards of an Excel workbook is an interesting exercise for curious-minded users.

7.10.3. Saving a file for use with an older version of Excel

To save a file for use with an older version of Excel, choose File Save As. In the Save As dialog box, 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.