Home > Articles > Software Development & Management

  • Print
  • + Share This

Managing Files

Exchanging Files Between Different Versions of Excel

You will have no trouble bringing Excel 97, 95, or even Excel 5.0 files into Excel 2000. Files created in earlier versions of Excel open easily in Excel 2000. When you open a workbook in Excel 2000 that was created in an earlier version, the file is not converted to Excel 2000.

However, when you attempt to save the file (using the File, Save command or the Save button), an alert displays reminding you that the file was created in an earlier version of Excel (see Figure 3.1). You can either save the file in Excel 2000 or save the file in its original versions format.

Surprisingly, you can easily take files originally created in Excel 2000 and open them in Excel 97, 95, and 5.0. When you want to open an Excel 2000 workbook in Excel 97, you do not have to save it in an Excel 97 format. You can open it directly in Excel 97.

If you want to be able to share an Excel 2000 file with people who are still using Excel 95, 5.0, or an earlier version, you need to save it in the earlier format using the File, Save As command. There is a Save As Type option 1 for Excel 5.0 & 95 (see Figure 3.2).

When you open the Excel 2000 workbook in an earlier version, however, some of the Excel 2000 features either use the earlier version's closest equivalent or the features will not function.

For example, suppose you create a PivotTable and PivotChart in an Excel 2000 workbook. The data plotted in the PivotChart is linked to the PivotTable data. When you open this workbook in Excel 97, the PivotTable is intact. However, the PivotChart is converted to a normal Excel chart. Additionally, the link between the data is broken--changing the data in the PivotTable will not change the data plotted in the chart. The good news is that when you open the workbook in Excel 2000 again, the link is re-established as long as you didn't save the file in the Excel 97 format.

If you routinely need to open a workbook in Excel 5.0, 95, 97, and 2000, use the special combined file format option 2 enabling you to do this. When you choose a combination file format option, any new formatting or functionality that the earlier version of Excel does not recognize is ignored by the earlier version.


When you use the Excel 97-2000 & 5.0/95 Workbook dual file format, the workbook is saved as a single file, but the 97-2000 formatting is saved separately from the 5.0/95 formatting. Therefore, the file size will be larger because it contains both sets of data.


When a workbook saved in the dual file format is opened in either Excel 5.0 or 95, a warning displays suggesting that the file be opened Read Only. If the warning is ignored and the file is saved in the earlier version of Excel, the features and formatting available only in Excel 2000 or Excel 97 are lost.

Additionally, Excel 95 and earlier versions allow fewer characters in a cell and have fewer rows in a worksheet. If your data extends beyond the limits in these versions, the data will be truncated.

A complete and detailed description of what happens when you save an Excel 2000 workbook in Excel 97, 95, and 5.0 is available through Excel help.

You should have little trouble converting basic macros or VBA code created in Excel 97. However, changes to the Excel architecture (expanded rows in worksheet and characters in a cell) may cause problems with macros written in Excel 95 and earlier versions.

If you are converting VBA and XLM macros from Excel 5.0, you should be able to run them in Excel 2000. However, you cannot record new XLM macros in Excel 2000. New macros can be recorded in Excel 2000 and edited in the Visual Basic Editor (VBE), or you can write them directly in the VBE (Tools, Macros, Visual Basic Editor).


Developers who design Excel application solutions that will be used across several versions of Excel typically write in the lowest-common-denominator code. Hence, some developers are still using Excel 4.0 macro functions. Help for these macros is not available through the Excel 2000 help screens. However, you can download the Macro Function Help files (Macrofun.hlp) from the Microsoft Knowledge Base article titled "XL:Macrofun.exe File Available on Online Services." You can see this article at the following Web location:


If you used dialog sheets in Excel 5.0 and Excel 95, most dialog sheets should run in Excel 2000 without the need to change them. New custom dialog boxes, called user forms, are created in the VBE. In the VBE, choose Insert, UserForm.

Likewise, files you saved as HTML files in earlier versions of Excel should convert seamlessly into Excel 2000. The File, Save As HTML command has been replaced by the File, Save As Web Pag e command and a new command--File, Web Page Preview--has been added so that you can preview a worksheet as a Web page before you save it.

FIG. 3.1 If the Office Assistant is active, the same message is displayed in a typical Office Assistant window.

FIG. 3.2 Excel 2000 Save As dialog box.

Using the Built-In Template Files

With each new version of Excel, the set of built-in templates is expanded to include additional useful templates and forms. To access the worksheet templates, you must use the File, New command; the New dialog box appears. The Excel templates are divided into three categories: General, Spreadsheet Solutions, and Business Planner Templates.

The default Workbook template is on the General tab 1, which contains the default Workbook template. This is the template used to create new files when you click the New button on the Standard toolbar.

The Spreadsheet Solutions tab (shown in Figure 3.3) contains three predesigned templates: Expense Statement, Invoice, and Purchase Order. The Village Software template is a marketing tool by the company who made these Excel templates for Microsoft.


The templates on the Spreadsheet Solutions tab contain macros. When you use one of these templates, you will see a warning message indicating the template contains macros. Choose the Enable Macros button to proceed with displaying a copy of the template.

The Business Planner Templates tab contains 13 new templates including balance sheet templates, income statement templates, and even an asset depreciation template. Each of these templates provides general guidance and information, and an appropriate layout for the document (see Figure 3.4). If an article is listed in the template, click the name of the article 2 to learn more about the topic for which the template is used. Many templates also contain a sample for you to view as shown in Figure 3.5.

FIG. 3.3 Spreadsheet Solutions tab of the Excel 2000 New dialog box.

FIG. 3.4 The Break-Even Analysis worksheet template.

FIG. 3.5 The example provided for Break-Even Analysis worksheet template.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.