Chapter 19. Working with Prior Versions of Excel

In this chapter

Opening Excel 2007 Files in Previous Versions of Excel 372

Creating Excel 2007 File Formats in Excel 2003 376

Opening Excel 2003 Files in Excel 2007 377

Preparing to Save Excel 2007 Files for Excel 97 Through Excel 2003 378

The Digital Divide 381

With not just one new file format but three file formats in Excel 2007, you will face a number of problems as you try to share files with people who use previous versions of Excel.

If you are using Excel 2007 and want to open a file created in Excel 5 through Excel 2007, your copy of Excel will gladly open the file, but the file will be in a special Compatibility mode. In this mode, you cannot use more than 65,536 rows, and you cannot use more than 256 columns. When you attempt to save the file, the Compatibility Checker will tell you what functionality will be lost.

In order to unlock the larger grid in Excel 2007 for a file created in an earlier version of Excel, you need to upgrade the file.

If you start with a new spreadsheet in Excel 2007 and want to share the file with someone using Excel 5, 95, 97, or 2000, you have to use the Compatibility Checker and save the file in a previous version of Excel.

If you start with a new spreadsheet in Excel 2007 and want to share the file with someone using Excel 2002 or Excel 2003, you can encourage that person to download the Compatibility Pack for the Microsoft Office 2007 system. This converter allows Excel 2002 and Excel 2003 to open files stored in the new XLSB, XLSM, and XLSX formats.

Opening Excel 2007 Files in Previous Versions of Excel

If you attempt to open an Excel 2007 file in Excel 2002 or Excel 2003, you will see a message that the file was created in a newer version of Excel. Excel offers to allow you to download a converter so that you can open the file, as shown in Figure 19.1.

Figure 19.1. Your coworkers using Excel 2003 or Excel 2002 will be greeted with this message when they attempt to open one of your files.

You follow these steps to download and install the converter:

1. Click Yes on the dialog box shown in Figure 19.1. A browser window launches, showing the home page of the Microsoft Office Compatibility Pack.
2. The converter requires that you be running Windows Vista, Windows XP, or Windows 2000. If you are using Windows XP, you need to be using Service Pack 2. If you are using Windows 2000, you need to be using Service Pack 4. If you are not at these levels, follow the links on the browser page.
3. The converter requires Office 2003 to be upgraded to Service Pack 2 or Excel 2002 to be upgraded to Service Pack 3. Select Help, About in Excel to determine your current service pack level. If you do not have the required service pack, follow the links on the browser page to download the upgrade.
4. Close all Office programs, including Excel. (Keep your browser open.)
5. Download and install the appropriate Office 2007 File Format Awareness Update by following the link on the browser page. The download is approximately 18MB, and the installation takes an unusually long time, often appearing to stop between steps. Eventually, the process starts the Office Installer and installs several items.
6. Return to the Compatibility Pack webpage. Choose the link to download the Compatibility Pack. The download is approximately 54MB. Run O2007cnv.exe after you download it. At the end of the installation, if you are prompted to restart your computer, do so.

After you install the Compatibility Pack, you can directly open XLSM, XLSX, and XLSB files in Excel 2003 or Excel 2002.

Opening a Basic Excel 2007 File in Excel 2003

Figure 19.2 shows a simple workbook in Excel 2007. Figure 19.3 shows the same workbook opened in Excel 2003. There are minor changes in the workbook. For example, the custom color in Cell B12 is converted to the closest color in the standard Excel 2003 pallet of 56 colors.

Figure 19.2. This basic worksheet in Excel 2007 converts nicely to Excel 2003.

Figure 19.3. Some minor formatting changes occur, but everything else translates perfectly.

You can edit this workbook in Excel 2003, save it as an Excel 2007 file format, and successfully open it in Excel 2007.

Opening an Advanced Excel 2007 File in Excel 2003

The Excel 2007 workbook shown in Figure 19.4 contains several elements that seem to be incompatible with Excel 2003. For example, the range in E21:I25 is an Excel 2007 table. The function in Cell F27 uses the new SUMIFS function. An image in Cell C27 has a reflection effect applied to it. The shape in Cell C18 is a shape that was not available in Excel 2003. The WordArt in Cell B14 is a different style than the styles available in Excel 2003. The chart uses Excel 2007 formatting. The SmartArt uses a shape that is not available in Excel 2003 diagrams.

Figure 19.4. This file in Excel 2007 purposely uses many features that would seem to be incompatible with Excel 2003.

When you attempt to open this workbook in Excel 2003, Excel warns you that there is significant loss of functionality. Because of the loss of functionality, Excel forces the document to be opened as read-only. You have to use File, Save As to save the file with a new name. This prevents the original document from losing fidelity.

Figure 19.5. If too much functionality will be lost, Excel 2003 forces the file to be opened as read-only.

Excel 2003 does its best to deal with the incompatible features. As shown in Figure 19.6, the chart formatting comes across amazingly well. The SmartArt looks fine, but it is no longer editable. The table in E21:I25 has been converted to an Excel 2003 list. The formula in Cell F27 has been converted from table nomenclature to actually refer to the correct cells. However, because the formula uses a function that is new in Excel 2007, the function returns a #NAME? error. The WordArt converts horribly. The shape converts, but the glow is lost.

Figure 19.6. Various elements are converted okay, while others, such as the WordArt and Excel 2007 functions, fail miserably.

On the machine running Excel 2003, you have to save the file with a new name. If you then attempt to open that file in Excel 2007, much of the formatting returns. Figure 19.7 shows the file after it is saved in Excel 2003 and then opened in Excel 2007. The glow on the object returns. The WordArt looks good again. The SmartArt is again editable. The chart has some problems, although this varies by chart. The one real loss of fidelity is the table function in Cell F27. In the original document, shown in Figure 19.4, this formula used table nomenclature. In Excel 2003, the table nomenclature was replaced with actual cell references, and when the file is brought back to Excel 2007, those cell references remain.

Figure 19.7. After round-tripping back to Excel 2007, most of the formatting comes back.

Opening Worksheets by Using the Large Grid in Excel 2003

The one situation that Excel 2003 absolutely cannot deal with is data in rows 65,537 and below and data in Columns IW and beyond.

When you attempt to open a file that has data outside the Excel 2003 grid, data outside the bounds of the original grid is not converted. Further, any formulas inside the grid that point to cells in the big grid return a #REF! error.

Excel opens the file in read-only mode, encouraging you to save it with a new name, so as not to destroy the original file with data beyond Column IV, Row 65,536. When you save the file in Excel 2003, this data is permanently lost. Even if you save the file in Excel 2003 and reopen it in Excel 2007, everything beyond row 65,536 has been truncated.

Creating Excel 2007 File Formats in Excel 2003

After you install the Office 2007 Compatibility Pack on a machine running Excel 2002 or Excel 2003, you will be able to save files from Excel 2003 or Excel 2002 in one of the new Excel 2007 file formats. The advantage is that when the file is later opened on a machine running Excel 2007, the file will not be forced into the compatibility mode.

After you have the Compatibility Pack installed, you follow these steps to save files in an Excel 2007 format:

1. Open or create a workbook in Excel 2002 or Excel 2003.
2. Select File, Save As. The Save As dialog box appears.
3. Open the Save as Type drop-down and scroll to the bottom of the list. Choose one of the Excel “12” file types, as shown in Figure 19.8.

   Figure 19.8. After you install the converter in Excel 2003, you can save files in any of the Excel 2007 formats.

   

4. Type a filename
5. Click Save. Excel runs a converter to convert the workbook to the chosen Excel 2007 format.

Opening Excel 2003 Files in Excel 2007

The easiest way to get your old Excel files into Excel 2007 is to open them directly in Excel 2007. Excel 2007 will be able to import files saved in any version from Excel 5 through Excel 2003. However, files opened in this manner open in a bizarre Compatibility mode.

When a legacy Excel file is opened in Excel 2007, the title bar isays [Compatibility Mode]. In this mode, Excel 2007 allows only 65,536 rows and 256 columns. It is impossible to insert more columns or rows in the file. If you add additional sheets, the sheets will have only 65,536 rows and 256 columns.

You can add new features while the file is in Compatibility mode. For example, you can use the AVERAGEIFS function and use colors beyond the 56-color palette. However, when you attempt to save the file, the Compatibility Checker warns you about the fidelity that will be lost. When a file is in the hampered Compatibility mode state, you cannot access any cells in the expanded grid area of the worksheet. In order to access the additional rows, you have to upgrade the file

To upgrade the file, you follow these steps:

1. From the Office icon menu, select Convert. As shown in Figure 19.9, this icon features a green up arrow, indicating that you will be upgrading the file.

   Figure 19.9. The Convert icon is available only when the active workbook is in Compatibility mode.

   

   A dialog box explains that the file will be upgraded to the newest file format, as shown in Figure 19.10. The most important part of this dialog is that your original XLS file will be deleted and replaced with the new XLSX or XLSM file. I suppose this is to prevent anyone from accidentally opening the XLS version of the file. However, taking this action will break any existing Excel workspaces that relied on the old version of the file.

   Figure 19.10. Note that the conversion process will replace the original file with an Excel 2007 file.

   

2. Press End+Down Arrow. Frustratingly, the file still has only 65,536 rows.
3. Choose the Office icon menu and then select Close to close the file.
4. Choose the Office icon menu and then select 1 to reopen the converted file. Your file now has 1.1 million rows.

Preparing to Save Excel 2007 Files for Excel 97 Through Excel 2003

You are likely to need to send your Excel 2007 workbooks to someone who is using an older, unsupported version of Excel, such as Excel 97 or Excel 2000. There is no Compatibility Pack available for those versions. You may also run into people using Excel 2003 who for some reason are not able to download the Compatibility Pack. In these cases, you will have the possibly frustrating situation of needing to downgrade a file in order to save it as an XLS format that the other person can open.

Your first stop should be the Compatibility Checker. This utility analyzes which Excel 2007 features you have used that will not be available in prior versions of Excel. In some cases, this is only a minor problem. For example, if you have used a color outside the palette of 56 colors, Excel simply substitutes another color. Excel considers this a minor loss of fidelity. In other cases, your workbook will extend beyond either Row 65,536 or Column IV, and some of your data will be truncated if you downgrade the file.

To run the Compatibility Checker, you follow these steps:

1. From the Office icon, select Finish, Compatibility Checker. Excel displays a list of problems with the workbook. Note that the Compatibility Checker dialog is a bit too small and displays only two items at a time. You might need to scroll through several screens of problems. If the workbook has no compatibility problems, the message “No Compatibility Issues Were Found” is displayed. To the right of each problem, Excel displays the number of cells affected by the problem. Some simple problems have a Fix hyperlink. Other problems cannot be automatically fixed. If you click the Find hyperlink next to the problem, Excel closes the dialog and changes the active cell to the first cell that has the problem.
2. To fix a problem cell, take the required action. In many cases, you don’t have to fix the problem. For example, if it is merely a formatting issue, you can take your chances in trusting that Excel will apply the closest available format.
3. Repeat steps 1 and 2 for additional problems.

The Compatibility Checker dialog, shown in Figure 19.11, classifies problems in two groups. Major problems appear first, in a section called Significant Loss of Functionality. Minor problems appear later, in a section called Minor Loss of Fidelity.

Figure 19.11. A list of problems appears in the dialog box.

The following are examples of some problems that might appear in the Significant Loss of Functionality section:

• “This workbook contains data in cells outside of the row and column limit of the selected format. Data beyond 256 (IV) columns by 65,536 rows will not be saved. Formula references to data in this region will return a #REF! error.”
• “Some formulas contain references to tables that are not supported in the selected file format. These references will be converted to cell references.”
• “One or more functions in the workbook are not available in earlier versions of Excel and the results of these functions will not be displayed.”
• “Some cells contain types of conditional formatting that are not supported in earlier versions of Excel.”
• “Some cells contain conditional formatting with the ’Stop if True’ option unchecked. Earlier versions of Excel do not recognize this option and will stop after the first true condition.”
• “Some cells contain conditional formatting with cell references to other worksheets. These conditions will not be evaluated in earlier versions of Excel.”

The following are examples of some of the problems that might appear in the Minor Loss of Fidelity section:

• “This workbook contains a table with table style applied. Table style formatting will not be displayed in previous versions of Excel.”
• “Some cells or styles contain formatting that is not supported in the selected file format. These formats will be converted to the closest format available.”

If you are attempting to fix all the problems listed, it can be frustrating bouncing between the Compatibility Checker dialog and the worksheet. If you click the Copy to New Sheet button in the lower-left corner of the dialog, Excel copies the complete report, along with hyperlinks to all the offending cells, to a compatibility report in the workbook, as shown in Figure 19.12.

Figure 19.12. You can click Copy to New Sheet to produce the compatibility report on a new worksheet.

Saving Excel 2007 Files for Excel 97 Through Excel 2003

After checking for compatibility problems, you follow these steps to save a file as an XLS file:

1. From the Office icon, choose Save As. The Save As dialog appears.
2. In the Save as Type drop-down, select Excel 97-2003 Workbook (*.xls).
3. If necessary, type a new filename.
4. Click the Save button. Excel displays the Compatibility Checker one more time.
5. In the Compatibility Checker, click the Continue button. If you have unresolved formula problems, Excel asks “Do you want to recalculate all formulas when this workbook is opened?” If you want the recipient of the workbook to see the current values, choose No.

The Digital Divide

Compatibility problems are frustrating. If you are reading this book, you are presumably the owner of Excel 2007. As you send files to others, you will hear the excuse, “I received an email, but the file was in some bizarre format that my copy of Excel couldn’t open.” As I consider the fantastic new features in Excel 2007, my main concern is the compatibility of the file formats with those people who don’t upgrade. Although the Compatibility Checker can handle many problems, it simply cannot deal with situations in which the workbook contains more than 65,536 rows. Although there are 40 billion workbooks with fewer than 65,536 rows in use today, people will quickly start moving datasets to Excel instead of Access, and the rift between the Excel 2007 customers and everyone else will be the digital divide of 2007 and 2008.