Exploring File Management Fundamentals

One of the advantages of working with computers is the convenience of electronic files. In this section, we describe both the usual and unusual ways you can manage your Excel files.

Creating Workbooks

To create a new workbook, click the Microsoft Office Button, and click New to display the New Workbook dialog box, as shown in Figure 2-20. When you click Blank Workbook and press Enter (or click the Create button at the bottom of the dialog box), a fresh workbook opens. Each new workbook you create in the current Excel session is numbered sequentially: Book1, Book2, and so on.

Figure 2-20: Click the Microsoft Office Button and then click New to display the New Workbook dialog box.

With the New Workbook dialog box, you can create all kinds of new workbooks from templates or existing workbooks. The category you select on the left side of the dialog box controls what appears on the right. The Blank And Recent category appears in the Templates group when you first open the dialog box. The categories on the left include the following items:

• Blank And Recent Clicking the Blank Workbook icon creates just that-a new, blank workbook. The Recently Used Templates area shows the last few templates you have opened.

• My Templates Clicking My Templates opens your own personal template treasure trove.

• New From Existing Clicking this icon displays the dialog box shown in Figure 2-21. This dialog box opens any existing Excel file as a template. This means two things happen differently than with the Open dialog box: First, instead of opening the actual workbook, it opens a copy of it. Second, when you save the workbook, it appends a number to the end of the file name and displays the Save As dialog box, making it virtually impossible to overwrite the original file.

• Installed Templates If you have used any templates, the New Workbook dialog box adds another group in the Blank And Recent area-Recently Used Templates, as shown in Figure 2-20. Selecting the Installed Templates category displays all the templates that are stored on your computer as part of your initial Excel installation, as shown in Figure 2-22.

• Microsoft Office Online While your computer is connected to the Internet, you can click items in the Microsoft Office Online area of the New Workbook dialog box to open the selected item or browse the Microsoft Web site for additional templates.

Figure 2-21: You create a copy of any file you select in the New From Existing Workbook dialog box.

Figure 2-22: A number of templates are ready and waiting on your computer when you first install Excel.

Note 

You can choose whether to let the Microsoft Office Online area of the New Workbook dialog box be active content, which is dynamically updated via a Web connection. For more information, see "Privacy Options" on page 108.

Office Excel 2007, being more Web connected than ever, takes advantage of this by storing most templates on the Microsoft Office Online Web site, instead of putting them on your computer. This not only saves space on your hard disk but also makes it possible to offer many more templates than would be possible on CDs and makes it easy for developers to keep them up-to-date. Figure 2-23 shows just a few of the templates available in one of the categories.

Figure 2-23: Microsoft Office Online provides a fortune in template treasures from which to choose.

Installing Your Own Templates

Installing your own templates is a great step to take with worksheets you use a lot. You can click New From Existing in the New Workbook dialog box to open any workbook as a template. Better yet, you can put any workbook in a special folder, and it automatically becomes an installed template. You can find this folder in the following locations:

• Windows XP C:\Documents and Settings\<your name>\Application Data\Microsoft\Templates

• Windows Vista C:\Users\<your name>\AppData\Roaming\Microsoft\Templates

Anything you put in this folder will appear in the New dialog box, shown in Figure 2-24, which opens when you double-click the My Templates icon in the New Workbook dialog box. Great trick.

Figure 2-24: You can add your own templates to the New dialog box.

Inside Out-Hidden Windows Folders Revealed

You will probably have to change a Windows setting to find the AppData folder (Application Data in Windows XP), because it is normally hidden. To reveal it, navigate to the folder that bears your name in Windows Explorer, as shown in the previous paths. Then from the Organize menu (the Tools menu in Windows XP), click Folder Options, and on the View tab, click Show Hidden Files And Folders. Note that <your name> is the user name you use when logging on to Windows.

Create Your Own Template Tabs

The New dialog box derives its contents from a special folder installed by the 2007 Microsoft Office system, as described in "Installing Your Own Templates" on the previous page. You can create subfolders in this special folder that become tabs in the New dialog box when they contain 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 opened 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.

Note that in previous versions of Excel, you could put any type of Excel file into the Templates folder or any of its subfolders, and Excel would treat them all as templates. Office Excel 2007 no longer allows this-only template files show up in the New dialog box.

Inside Out-Office-Wide Templates Tabs

When you create a custom folder within the Templates folder, it is available to other 2007 Microsoft Office applications too. Your custom folder appears as a tab in the Templates dialog box of every 2007 Microsoft Office application, but only if the folder actually contains a template in that application's native format. For example, if you create an Accounting folder in the Templates folder and you copy a Microsoft Office Word 2007 document into it, the Accounting folder will appear as a tab in the Templates dialog box of Office Word 2007. If the folder contains only Excel files, the tab will not appear in Office Word 2007.

Saving Files

Arguably, the most important function of any computer application is preserving data. In Excel, you can save your files in many ways, including clicking the Save, Save As, Publish, Save Workspace, Close, and Exit commands and-the easiest way to save-clicking the Save button on the Quick Access Toolbar.

One other command that saves your workbooks is the Share Workbook command in the Changes group on the Review tab. When you click this command, you save your workbook in shared mode. Besides saving the file, this command makes the workbook available to others on a network, who can then open it and make changes of their own.

For more information, see "Sharing Workbooks on a Network" on page 790.

The first time you save a file, the Save As dialog box opens, as shown in Figure 2-26.

Figure 2-26: The Save As dialog box opens when you save a file for the first time.

Note 

If you use the same folder most of the time, you can specify that folder as the default location that the Open, Save, and Save As dialog boxes use when you first open them. Click the Microsoft Office Button, click Excel Options, select the Save category, and type the full path and file name for the folder you want to use in the Default File Location text box.

The Windows Vista version of the Save As dialog box has a lot more bells and whistles than previous versions. This is also true with other dialog boxes that are provided by the operating system, including Save and Open. If you haven't upgraded to Windows Vista, you might as well. You're learning a lot of new stuff with 2007 Microsoft Office anyway, much of which was designed around Vista functionality, and the Save As dialog box is a good example. If you click the Browse Folders button, the dialog box expands to include a sophisticated new file interface that includes a number of useful Windows Vista features such as the ability to zoom the file display using a slider and the ability to customize the dialog box layout. For details, click the Help button (the question mark icon) in the Save As dialog box to display the corresponding Windows Help file.

Rules for File Naming

File names in Excel can have up to 218 characters. They can include any combination of alphanumeric characters, spaces, and the 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 my file are identical.

How Much Disk Space Do You Need?

To ensure that a new copy of a file is properly saved before the original is deleted, Excel makes a temporary file when it saves and then deletes the original and renames the temporary file to the original name. This prevents loss of both the original and the version being saved if something goes wrong in mid-save. Because of this, you can never open, make changes to, and then save a file that is bigger than half the amount of available space on the disk being used. For example, if you are working with a file on a 1.44 megabyte (MB) floppy disk (yes, people do still use these on occasion), you cannot open, make changes to, and then save it if it is larger than approximately 720 kilobytes (KB) (which is a pretty big file). In this case, you would need to make all your changes in a copy of the workbook located on your hard disk and then save it to the floppy disk when you're finished. It's unlikely you'll run into this problem on a hard disk or CD, but if you do see symptoms of insufficient space while saving an Excel file, it might be time for a little disk cleanup or to install another disk drive.

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 some of the Excel default extensions.

Table 2-1: The Excel Default Extensions
Open table as spreadsheet

Document Type	Extension
Add-in	.xlam
Macro-enabled template	.xltm
Macro-enabled workbook	.xlsm
Excel binary workbook	.xlsb
XML data	.xml
XML spreadsheet 2003	.xml
Template	.xltx
Workbook	.xlsx
Workspace	.xlw

Inside Out-Hidden File Name Extensions Revealed

Note that file name extensions might not appear with Windows file names, depending on your settings. To display file name extensions, click the Start button, and then click Computer (Control Panel in Windows XP). Next, click Folder Options (located on the Organize menu in Windows Vista), and on the View tab, scroll down and clear the Hide Extensions For Known File Types check box.

File Formats

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.

For more information about the Excel export formats, see "Importing and Exporting Files" later in this chapter on page 68.

Ensuring File Compatibility with Previous Versions of Excel

When you open a workbook in Office Excel 2007 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 will remain 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.

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 it. For example, Office Excel 2007 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 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.

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 will no longer open 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 Microsoft Office Button, click Prepare, and then click Run Compatibility Checker to display the same dialog box shown in Figure 2-28.

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 Office 2007 Converter: File Format Compatibility Pack from the Office Online Web site to open and edit Excel 2007 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 service pack (SP) installed. The easiest way to find the converters as well as the SPs is to press F1 to open the Excel Help window and click the File Conversion link (or type file conversion in the Search box). The File Conversion Help topic includes links to relevant topics containing the appropriate download locations on the Microsoft Office Online Web site.

For more information about the new, less-restrictive limitations of Excel, see "Increased Capacity and Speed" on page 9.

Understanding the "XL" Formats

Although Microsoft trimmed some of the lesser-used file formats from its list of file types, the volume of Excel 2007 file formats has swelled somewhat. Excel 2007 has 11 Excel-centric file formats; we'll explain the major differences here:

• Excel Workbook (XLSX) This is the new default Excel 2007 file format that is based on XME and uses ZIP compression for reduced file size and increased security. Note that unlike the default file format in previous versions of Excel (XLS), this format does not support VBA or XLM macro code.

• Excel Macro-Enabled Workbook (XLSM) Microsoft created a completely separate XML-based file type to be used for workbook files containing VBA or XLM macro code. This increases security by making it impossible to inject macro code into non-macro-enabled workbooks and makes for easier identification of files containing code.

• Excel Binary Workbook (XLSB) This is a lean and mean file format designed for the fastest possible loading and saving. It supports all the features of Office Excel 2007 and also supports macro code, but it is not XML based, does not use compression, and is less secure than XML-based formats.

• Excel Template (XLTX) This is the template version of the new Office Excel 2007 file format. Note that this format does not support VBA or XLM macro code.

• Excel Macro-Enabled Template (XLTXM) This is the template version of the Office Excel 2007 file format that does allow VBA and XLM macro code.

• Excel 97-2003 Workbook (XLS) This is the "legacy" file format used by previous versions of Excel.

• Excel 97-2003 Template (XLT) This is the "legacy" template format used by previous versions of Excel.

• Excel Add-In (XLAM) This is a special type of VBA-enabled workbook that can be loaded as a supplemental program in Excel.

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 shares the same extension but produces entirely different results: XML Data (XML) 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.

Inside Out-What Is XML?

The new Office Excel 2007 file format is based on XML, which was created as a way for structured data to be interpreted and was originally envisioned for use on the Web. Excel 2003 introduced XML as an optional file format; the new Excel 2007 uses XML as the default format, indicated by the new file name extension .xlsx. Based on a file format specification called SpreadsheetML, Microsoft's implementation of XML has undergone significant improvements since being introduced. At first, the format didn't have, shall we say, sufficient language skills to interpret all of what Excel could do, including objects such as charts and graphics. The latest incarnation of SpreadsheetML can handle everything Excel can dish out and does so in a much more efficient manner than the old XLS format, resulting in significantly smaller file sizes. The new XML formats also provide improved recovery of damaged files and better overall security. And, because SpreadsheetML is part of an overall extensible Open XML format initiative coming with the 2007 Microsoft Office system, it's easier for developers to create ways to hook things together.

For some additional information about XML, see "Working with XML Files" on page 755.

Specifying the Default File Format

Usually when you save a new workbook, you'll 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 Microsoft Office Button, click Excel 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.

Creating Automatic Backup Files

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 Microsoft Office Button, 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.

Figure 2-30: To display the General Options dialog box, click the Microsoft Office Button, click Save As, and then click Tools, General Options.

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.

Caution 

Keep in mind that Excel always uses an .xlk extension when creating backup files regardless of the file type. Suppose you work with a workbook named Myfile.xls as well as a template file on disk named Myfile.xlt and you select the Always Create Backup check box for both. Because only one Myfile.xlk can exist, the most recently saved file is saved as the .xlk file, and Excel overwrites the other file's backup if one exists.

Protecting Files

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.

• Password To Open Excel prompts you to supply the password before reopening the file.

• Password To Modify Anyone can look at the file, but they need the password to open or save it.

• Read-Only Recommended This politely suggests that the user open the file as read-only.

Inside Out-Create a Better Password

Although Microsoft has tightened the under-the-hood security measures of Excel over the years, some people delight in finding new and better ways to crack passwords. You can help by simply building better passwords. Make sure your password is eight or more characters long-the longer, the better-and try to use a healthy mix of capital and lowercase alphanumeric characters and nonalphanumeric characters.

Adding Summary Information to Files

When you click the Microsoft Office Button, click Prepare, and then click Properties, Excel displays a Properties Ribbon that you use to record general information about the active workbook, as shown in Figure 2-31.

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

If you juggle a lot of files, getting into the habit of adding properties can make it a lot easier to find something later. This is easy to do using the Open dialog box, as shown in Figure 2-32.

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

Click the Microsoft Office Button, click Open, select a file, and on the Organize menu, click Properties to display a dialog box full of information. All the summary information is visible on the Details tab. You can even edit much of the Description and Origin information shown on the Details tab-simply select the value you want to change and type, as shown in Figure 2-32. Also, Windows looks at these property values when you use the Search command to locate files on your computer.

Saving the Entire Workspace

Click the View tab, and in the Window group, click Save Workspace 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.

Note 

The Save Workspace command pertains to a different universe, as compared with the Create Document Workspace command, revealed by clicking the Microsoft Office Button and then the Publish tab. A document workspace is a 2007 Microsoft Office collaboration tool for people who work on projects in teams. For more information, see "Creating a New Document Workspace" on page 816.

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 445). Then open the Properties dialog box by clicking the words Document Properties in the title bar of the Properties Ribbon and then clicking Advanced Properties. The Custom tab in the Properties dialog box activates a Link To Content check box when a named cell is available in the workbook. When you select the Link To Content check box, the workbook's defined names appear in the Value drop-down list (whose name changes to 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 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.

Opening Files

Only slightly less basic than saving files is opening them. Click the Microsoft Office Button, and click Open to display the Open dialog box, shown in Figure 2-34.

Figure 2-34: Click the arrow next to the Open button for more choices when opening documents.

Click the Favorite Links icons on the left side of the dialog box to display the corresponding files on the right side. Clicking the Documents icon displays the contents of the folder of the same name when you first open the dialog box, unless you have changed the default file location (click the Microsoft Office Button, click Excel Options, and select the Save category).

• The Recent icon lists files you have opened and folders you have navigated to, in chronological order with the latest first. This view actually displays the contents of an Application Data folder named Recent, which is populated automatically with shortcuts to the files and folders you use in the Open and Save As dialog boxes.

• The Desktop icon brings you to the top level of your computer's file system so you can click your way down through the hierarchy.

• The Documents icon displays the contents of the Documents folder.

• The Computer icon displays all storage locations available on your computer, including disks, CD and DVD drives, and shared folders.

• The Network Shortcuts icon opens files in any available locations on your network or on the Web.

• The drop-down list to the right of the File Name text box at the bottom of the Open dialog box determines which files are available for selection. The default option is All Microsoft Excel Files, which displays file names whose extensions begin with xl. You can display specific file types or all files by clicking the arrow to the right of the menu.

  Note 

  To open several files at once, press the Ctrl key, and select each file name you want to open.

• With the Views button, you can select different ways to display files in the dialog box.

• The Tools button displays a drop-down list that contains a single command, Map Network Drive, which lets you connect to a location on your network.

Note 

You can right-click most files listed in the Open and Save As dialog boxes to display a shortcut menu that contains commands you can use with the selected file. For example, you can delete a file displayed in the Open dialog box by using this shortcut menu.

Notice that headings appear at the top of the file list in the Open dialog box. When you click one of these headings, you sort the files in order, based on that heading. For example, if you click the Date Modified heading, you sort the files in date order. You can further refine your quest using the hidden menus adjacent to each heading. When you rest the pointer on a heading, a downward-pointing arrow appears to the right of the heading; click it to display a menu of additional options pertinent to that heading, as shown in Figure 2-35.

Figure 2-35: You can specify additional criteria when looking for files in the Open dialog box, using the menu buttons adjacent to each heading in the file list.

Each heading has its own menu of options, some of which are based on the actual files contained in the current folder. For example, when you click the menu for the Type heading, you can select from the list of file types contained in the folder.

Special Ways to Open Files

To the right of Open at the bottom of the Open dialog box is a small arrow. Clicking this arrow (see Figure 2-34) displays a menu containing the following options:

• Open Read-Only This opens the file so you cannot save any changes made to it without renaming it.

• Open As Copy This creates a duplicate of the selected file, adds the words Copy of to the file name, and leaves the original untouched.

• Open In Browser This applies only to HTML documents and opens the selected file in your default Web browser.

• Open And Repair This is a powerful feature that you can use to try opening corrupted files. For more information, see "Recovering Corrupted Files" on the next page.

Opening Files When You Start Excel

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.

Note 

You can save workspace files in the XLStart folder so that all the files and the workspace setup are automatically loaded each time you start Excel. For more information about workspace files, see "Saving the Entire Workspace" earlier in this chapter on page 60.

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

• Windows XP C:\Documents and Settings\<your name>\Application Data\Microsoft\Excel\XLStart

• Windows Vista C:\Users\<your name>\AppData\Roaming\Microsoft\Excel\XLStart

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 Microsoft Office Button, click Excel Options, select the Advanced category, and 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.

Recovering Corrupted Files

Figure 2-34 shows the Open dialog box, where you'll find the Open Options menu (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 will pull all the data from your workbook, including all worksheets and tabs, in the same order they appeared 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.

Inside Out-Recover Data Using Links

You can recover data from a corrupted workbook by using another trick. It is essentially the same trick used by the Excel Open And Repair command, but it still might be worth a try if Open And Repair fails.

First, open two new workbooks. Select cell A1 in one of the workbooks, and then press Ctrl+C to copy. Activate the second workbook, and right-click cell A1. Click Paste Special, and then click the Paste Link button. Next, click the Microsoft Office Button, click Prepare, click Edit Links To Files (you might have to scroll down to see this command), click Change Source, and locate the corrupted workbook. Click OK, and then click Close to close the Edit Links dialog box.

If luck is with you, data from cell A1 in the lost workbook will appear in cell A1, thanks to the linking formula. If it does, press F2 to activate Edit mode, and press F4 three times to change the absolute reference $A$1 to its relative form, A1. Finally, copy the formula down and across until you can see all the data you need to retrieve. Repeat for each worksheet in the workbook. You will lose the formatting and formulas, of course, and zeros will appear in every blank cell, but at least you can get at the important stuff. Although you can save this worksheet with linking formulas, you might consider converting all the formulas to their underlying values, just in case the original corrupted file has any further degradation. To do so, select all the cells containing the formulas you just created, click Ctrl+C to copy, right-click, click Paste Special, select Values, and then click OK.

For more information about document recovery, see "Recovering from Crashes" later in this chapter on page 79. For more information about passwords, see "Hiding and Protecting Workbooks" on page 175.