Creating your own templates is a great idea for worksheets you use a lot. You can click New From Existing in the Available Templates window to open any workbook as a template. Better yet, you can create, organize, and format a workbook the way you want (minus the data), then click the File tab, click Save As, and choose Excel Template in the Save As Type list. When you click the Save button, Excel automatically installs your new workbook in a special folder as a template. You can find this folder in the following locations:

Templates in this folder appear in the New dialog box when you click My Templates in the Available Templates screen, as shown in Figure 2-24.

Figure 2-24. Your own templates appear in a dialog box when you click My Templates in the Available Templates screen.

When you click the File tab, click New, and then click My Templates, the New dialog box appears, which derives its contents from a special folder installed by the Microsoft Office system, as described in Installing Your Own Templates on the previous page. You can create your own personalized subfolders in this special folder that in turn become tabs in the New dialog box—but only when they contain valid template files. The name of each folder becomes the title of each new tab. Figure 2-25 shows the New dialog box with a new tab, which appears after creating a folder in the Templates folder and adding an Excel template file.

Figure 2-25. It’s easy to create your own tabs in the New dialog box.

File names in Excel can have up to 218 characters. They can include any combination of alphanumeric characters, spaces, and special characters, with the exception of the forward slash (/), backslash (), greater-than sign (>), less-than sign (<), asterisk (*), question mark (?), quotation mark (“), pipe symbol (|), colon (:), and semicolon (;). Although you can use any combination of uppercase and lowercase letters, keep in mind that Excel does not distinguish case in file names. For example, to Excel the names MYFILE, MyFile, and myfile are identical.

The old familiar MS-DOS three-character file name extensions, which now come in a four-character version as well, help identify your Excel files, and they are added automatically when you save a file. Table 2-1 lists Excel extensions in the order in which they appear in the Save As dialog box’s Save As Type list (except for Workspace, which doesn’t work there—see Saving the Entire Workspace on page 60).

In addition to providing the file name and location, you can specify a different file format in the Save As dialog box. Click the Save As Type drop-down list, which expands to reveal all the formats in which you can save your files.

The default format is Excel Workbook (XLSX), and you’ll almost always use this option. If you want to export an Excel file to another program, however, you can use one of the other options to convert the file to a format that is readable by that program.

When you open a workbook in Excel 2010 that was created in a previous version of Excel, it automatically opens in Compatibility mode, a condition that is indicated in the Excel title bar, as shown in Figure 2-27. You can always tell by looking at the title bar whether you’ve converted a file to the new format.

Figure 2-27. Files created by any previous version of Excel open in Compatibility mode.

You can work normally with Compatibility mode, and when you save the file, it remains in the old file format. If, however, you make any changes using features that are not compatible with the older version, the Excel Compatibility Checker intervenes when you save and displays a dialog box like the one in Figure 2-28.

Figure 2-28. The Compatibility Checker opens when you try to save an old-format workbook containing features not supported by the older version of Excel.

The Compatibility Checker lets you know exactly what is causing the problem, so you can click Cancel and rework your worksheet using a different approach or save it anyway. You can click the Find or Fix links in the Compatibility Checker dialog box to highlight or correct each item that is causing compatibility problems. Clicking the Copy To New Sheet button adds a new worksheet to the current workbook entitled Compatibility Report, containing a copy of the information displayed in the dialog box—sort of a compatibility paper trail. If you clear the Check Compatibility When Saving This Workbook check box, this dialog box no longer opens when you save the current workbook. You might prefer this if you plan to repeatedly edit and save without updating the workbook to the new file format. But fear not, you can always look for problems at your convenience; click the File tab, click the Check For Issues button, and then click Check Compatibility to display the same dialog box shown in Figure 2-28.

Tip

INSIDE OUT What Doesn’t Work in Compatibility Mode?

When Compatibility mode is on, Excel disables a number of features that produce results that cannot be transferred to older versions of Excel. You can still click disabled commands, but when you do, Excel displays a ScreenTip explaining why you can’t use them. For example, Excel 2010 allows 64 levels of nesting in formulas (parenthetical expressions within expressions), but previous versions allowed only 7. If you are working on a worksheet where you try to exceed the previous limitation, Excel prevents it and displays an error message:

Here are some of the things that can trigger compatibility error messages:

• A workbook contains too many rows and/or columns.

• A formula exceeds the maximum allowed number of characters.

• A formula exceeds seven nested levels.

• A formula exceeds the maximum number of arguments in a function.

• A formula contains more than 40 operands.

• A workbook contains too many cross-sheet array formulas.

• A workbook has more than 4,050 unique cell formats.

• A PivotTable field has more than 32,500 unique items.

• A PivotTable item has a string length greater than 255 characters.

• A PivotTable caption has more than 255 characters.

• A PivotTable field list has more than 1,024 fields.

In case you’re wondering how to get out of Compatibility mode, all you have to do is use the Save As command to save the workbook in one of the new file formats, such as XLSX or XLSM, and then close and reopen the new converted file you just saved.

If you need to save files in other formats, the Save As Type drop-down list in the Save As dialog box includes a number of special formats you can choose, including Excel 97-2003 Workbook and Microsoft Excel 5.0/95 Workbook. Users of any of these versions of Excel can open a file saved in this format, but if someone using Excel 95 or Excel 5 saves changes to this file, any features from Excel versions 2000 through 2003, as well as the formatting, are lost. To alleviate this and other problems, you can download the Microsoft Office Compatibility Pack from the Office Online Web site to open and edit Excel 2007 and Excel 2010 files using earlier versions of Excel. When you use these converters, you don’t have to save files in the old file format, and you don’t have to upgrade the old Excel installation. You do, however, have to make sure you have the latest software updates installed on both ends of the transaction.

Although Microsoft trimmed some of the lesser-used file formats from the last release, the volume of native Excel file formats has swelled somewhat, largely due to accommodating “legacy” file formats—that is, file formats employed in previous versions of Excel. We’ll explain the major differences here:

In addition to these “XL” file types, there are two XML file types that are somewhat related. XML Spreadsheet 2003 (XML) is the previous XML file format provided as an option in Excel 2003. Another format, XML Data (XML), shares the same extension but produces entirely different results. This format is a proprietary XML format that requires specific programmatic data maps to be present before you can even save the file. You’ll know if you need it.

Usually when you save a new workbook, you save it in the Excel Workbook format (XLSX). You can specify a different format as the default for saving files. This might be helpful, for example, if you share files regularly with users of Excel 2003. To do so, click the File tab, click Options, and select the Save category, shown in Figure 2-29.

Figure 2-29. You can specify the default format to use when saving.

The Save Files In This Format drop-down list contains all the same file formats as the Save As Type drop-down list in the Save As dialog box.

You can have Excel create a duplicate copy of your file on the same disk and in the same folder as the original every time you save. Click the File tab, click Save As, click Tools at the bottom of the Save As dialog box, and click General Options to display the dialog box shown in Figure 2-30. Then select the Always Create Backup check box.

The backup file is a duplicate file that carries the same name as your original, but the name is preceded by Backup of and has the file name extension .xlk.

Figure 2-30. To display the General Options dialog box, click the File tab, click Save As, and then click Tools, General Options.

You can password protect your files by using options in the General Options dialog box shown in Figure 2-30. Choose from two types of passwords: Password To Open and Password To Modify. Passwords can have up to 15 characters, and capitalization matters. Thus, if you assign the password Secret to a file, you can’t reopen that file by typing SECRET or secret.

When you click the File tab, the Backstage view screen that first appears includes a thumbnail view of the current workbook. Just below the thumbnail, click Properties, and then click Show Document Panel to display the Document Properties panel, which appears below the ribbon, as shown in Figure 2-31. Use this to record general information about the active workbook. If you juggle a lot of files, getting into the habit of adding properties can make it a lot easier to find something later. Windows looks at these property values when you use the Search command to locate files on your computer.

You can quickly view properties for the active workbook by clicking the File tab and looking on the right side of the Info screen. Several properties are displayed there beneath the thumbnail of the workbook. Click Properties, and then click Show All Properties to expand the list visible on the Info screen, as shown in Figure 2-32. It’s a lie, though; not all properties are displayed there. For the rest of the story, you must click Properties, then click Advanced Properties to display a dialog box full of information, also shown in Figure 2-32. Most of the information visible on the Document Properties panel is available and editable on the Summary tab.

Figure 2-31. Use the Document Properties panel to add descriptive information you can use later when looking for that needle in a file stack.

Figure 2-32. View properties about any file before you open it.

Click the View tab, and then click Save Workspace in the Window group to save a snapshot of your current Excel environment. When you save a workspace, Excel notes the locations of all the workbooks that are currently open, as well as many of the workspace settings, so you can retrieve your files and settings in the same configuration they were in when you saved. Settings that are saved with workspaces include many display and calculation settings. The default file name suggested for a workspace file is Resume.xlw, but you can rename it.

Linking Custom Properties to Cells

You can link your own custom-built properties to named cells on your worksheet. When you do, the value of the custom property becomes whatever the named cell contains and changes whenever the value in the cell changes. First you must name a cell (see Rules for Naming on page 487). Then click the File tab, click Properties (under the worksheet thumbnail), and then click Advanced Properties. On the Custom tab in the Properties dialog box, selecting the Link To Content check box displays the workbook’s defined names in the Value box (which changes to a drop-down list named Source when linking content), as shown in Figure 2-33.

Figure 2-33. Use named cells to create dynamic properties based on worksheet cells.

Select or create a name for the custom property in the Name box. (The Type drop-down list becomes unavailable when you’re linking to content.) When you’ve specified a source (the named cell to which you want to link), click Add, and the custom property appears in the Properties list.

If the link is broken (the defined name is later deleted, for example), the Properties dialog box stores the last value recorded for that property.

If the name defines a range of cells, only the value in the cell in the upper-left corner of the range appears as the property value.

To the right of Open at the bottom of the Open dialog box is a small arrow. Clicking this arrow displays a menu containing the following options:

If you have files you need to work on every day, you can store them in a special folder called XLStart. Every time you start Excel, any files in the XLStart folder automatically open.

The XLStart folder is created when you install Excel and is located in the following place:

If you want to start Excel and simultaneously open files that are in a folder other than the XLStart folder, you can specify an alternate startup folder. Click the File tab, click Options, and select the Advanced category. In the At Startup, Open All Files In text box, under General options, type the full path of the folder. This feature is particularly useful if your computer is connected to a network and you want to open files from a shared folder.

Figure 2-34 shows the Open dialog box, where you can find the Open Options menu (click the arrow next to the Open button) containing the Open And Repair command. This command gives you a fighting chance at either repairing a corrupted file or extracting the data from it if it doesn’t respond to a repair attempt. When you select a file and click the Open And Repair command, the message box shown in Figure 2-36 opens.

Figure 2-36. The Open And Repair command gives you a ray of hope for recovering lost data.

Try the Repair button first, and if Excel still has no luck opening the file, try the Extract Data button, which displays the message box shown in Figure 2-37.

Figure 2-37. The Extract Data button offers two ways to recover your data.

You’ll have to make a judgment call here—if you think your formulas will be OK after extraction, click Recover Formulas; otherwise, click Convert To Values. Recovering formulas will probably work unless the formulas include references to cells that were lost in corruption. Whatever you choose, the Extract Data feature pulls all the data from your workbook, including all worksheets and tabs, in the same order in which they appear in the original file. Unfortunately, the recovery process ignores all formatting, charts, and other objects—you can recover only the actual cell contents (the important stuff).

Note that unless the part of the file that became corrupted was the part storing passwords, you probably won’t be able to use this technique to retrieve data from a password-protected file.