Formatting and features that are not transferred in Excel file format conversions

The Microsoft Office 2003 Resource Kit is the definitive guide to installing, configuring, and supporting Microsoft Office in your organization. Designed for system administrators, consultants, and power users, this guide offers complete coverage whether you're running Microsoft Office on Microsoft Windows 2000 or later, or the Macintosh.

You can obtain the Office Resource Kit wherever computer books are sold or order direct from Microsoft Press, or online at the Microsoft Office Resource Kit Web site.

To locate your nearest source for Microsoft Press products worldwide, visit the Microsoft Press Web site or contact your local Microsoft office.

When you save a workbook in the Microsoft Excel 97-2003 & 5.0/95 Workbook format, the workbook is saved in a single file that includes the Excel 97-2003 and Excel 5.0/95 file formats. (Excel version 5.0 and Excel 95 have identical file formats, as do Excel 97, Excel 98 for the Macintosh, and Excel 2003.) The file size will be larger because the file contains both sets of data.

Users of Excel 2000 can continue to work in a workbook saved in the dual format without losing any features or formatting unique to this version. When users of Excel version 5.0 or Excel 95 open the workbook, a message appears recommending that they use the file read-only. However, if users ignore the recommendation and save the workbook in an earlier version of Excel, features and formatting available only in Excel 2003, Excel 2002, Excel 2000, or Excel 97 are lost.

To avoid losing work performed in Excel 2003, you can protect a workbook from changes by requiring a password to change the workbook or by making the workbook read-only.

The following features of Excel 2003, are not preserved if you open and then save an Excel 2003 workbook in Excel 97.

The following features of Excel 2003 are not preserved if you save an Excel 2003 workbook in the Excel 5.0/95 Workbook (*.xls) format.

Workbook properties and settings

Formatting and layout features

Chart features

Analysis, PivotTable, and data access features

OLE objects and graphics

If you use the Excel 5.0/95 Workbook file format as the default when you save a workbook, and you insert in the workbook an OLE object that contains Excel 2003 features, those features will not be saved if you open and then save the workbook in either Excel 5.0 or Excel 95. However, if you open the workbook in Excel 2003, insert a workbook object that contains Excel 2003 features, and then save the workbook, the Excel 2003 OLE object features are saved.

For example, you can open a workbook in Excel 5.0 or Excel 95 that was saved in the Excel 5.0/95 format. However, if you insert a workbook object that contains features unique to Excel 2003— such as merged cells or a bubble chart— and then save the workbook, those features are lost.

Workgroup and Internet features

Programmability features

Microsoft Visual Basic modules are converted to module sheets. All text is preserved in the module; however, not all new objects, methods, and properties in Excel 2003 are supported in the Excel 5.0/95 format. In some cases, you must edit the converted code to run the macros. Excel macro sheets (.xlm sheets) are preserved with no loss of function.

The Microsoft Excel 2003 template file format preserves all worksheet and chart data, formatting, macros, and other features available in Excel 2003.

You cannot save a workbook in the template file format of an earlier version of Excel.

The Lotus 1-2-3 Release 4.0 (WK4) format saves sheet data and formatting in a single file.

The following features of Microsoft Excel 2003 are not preserved if you open and then save an Excel workbook in Lotus 1-2-3 Release 4 (WK4).

Workbook properties and settings

Formatting and layout features

Chart features

Embedded charts are saved on the sheet in the closest available Lotus 1-2-3 chart format.

Analysis, PivotTable, and data access features

OLE objects and graphics

Workgroup and Internet features

Programmability features

The Lotus 1-2-3 Release 3 (WK3) format saves only text, values, and formulas.

The following features of Microsoft Excel 2003 are not preserved if you open and then save an Excel workbook in Lotus 1-2-3 Release 3.

Workbook properties and settings

Formatting features

The following table lists the effect of saving a workbook in WK3 format without an associated FMT3 file. To save most formatting, save workbooks in the Lotus WK1,ALL (1-2-3) or WK1,FMT (1-2-3) format.

Chart features

Embedded charts are saved as a separate chart sheets in the closest available Lotus 1-2-3 chart format.

Analysis, PivotTable, and data access features

OLE objects and graphics

Workgroup and Internet features

Programmability features

When you save a Microsoft Excel workbook in the Lotus 1-2-3 Release 3 (WK3, FM3) (*.wk3) format, data is saved in the resulting WK3 file, and formatting information is saved separately in the FM3 (*.fmt3) file. If you reopen the WK3 file in Microsoft Excel, the formatting in the associated FM3 file is applied automatically. The formatting files are used in Lotus 1-2-3 by the WYSIWYG or Impress add-in to format worksheets.

The Lotus 1-2-3 Release 2.x (WK1) format saves text, values, and formulas of the active worksheet.

The following features of Excel 2003 are not preserved if you open and then save an Excel 2003 workbook in Lotus 1-2-3 Release 2.x.

Workbook properties and settings

Formatting features

The following table lists the effect of saving a workbook in WK1 format without an associated ALL or FMT file. To save most formatting, save workbooks in the Lotus WK1,ALL (1-2-3) or WK1,FMT (1-2-3) format.

Chart features

Embedded charts are not saved on the sheet if you save the workbook in WK1 format.

Analysis, PivotTable, and data access features

OLE objects and graphics

Workgroup and Internet features

Programmability features

When you save a Microsoft Excel workbook in the Lotus 1-2-3 Release 2.x (WK1, FMT) format, data is saved in the resulting WK1 file, and formatting information is saved separately in the FMT file. If you reopen the WK1 file in Microsoft Excel, the formatting in the associated FMT file is applied automatically.

If you save a Microsoft Excel 2003 workbook in the Lotus 1-2-3 file format WK1,ALL (1-2-3), formatting is saved in a separate ALL file. The ALL file is used in Lotus 1-2-3 by the ALLWAYS add-in to format worksheets. If you reopen the WK1 file in Excel, the formatting in the associated ALL file is applied automatically.

Lotus 1-2-3 Release 1.x (WKS)

The Formatted Text (*.prn) format saves only the text and values as they are displayed in cells of the active worksheet.

All rows are saved. If a row of cells contains more than 240 characters, characters beyond 240 wrap to a new line at the end of the converted file. For example, if the rows 1 through 10 all contain more than 240 characters, the remaining text in row 1 is placed in row 11, the remaining text in row 2 is placed in row 12, and so on.

Columns of data are separated by commas, and each row of data ends in a carriage return. If cells display formulas instead of formula values, the formulas are converted as text. All formatting, graphics, objects, and other worksheet contents are lost. The euro symbol will be converted to a question mark.

Before saving a worksheet in this format, make sure all data that you want converted is visible and that there is adequate spacing between the columns. Otherwise, data may be lost or not properly separated in the converted file. You may need to adjust the column widths of the worksheet.

The Text (*.txt) file format saves only the text and values as they are displayed in cells of the active worksheet. All rows and all characters in each cell are saved. Columns of data are separated by tab characters, and each row of data ends in a carriage return. If a cell contains a comma, the cell contents are enclosed in double quotation marks. All formatting, graphics, objects, and other worksheet contents are lost. The euro symbol will be converted to a question mark.

If cells display formulas instead of formula values, the formulas are saved as text. To preserve the formulas if you reopen the file in Microsoft Excel, select the Delimited option in the Text Import Wizard, and select tab characters as the delimiters.

Note  If your workbook contains special font characters, such as a copyright symbol (©), and you will be using the converted text file on a computer with a different operating system, save the workbook in the text file format appropriate for that system. For example, if you are using Windows and want to use the text file on a Macintosh computer, save the file in the Text (Macintosh) format. If you are using a Macintosh computer and want to use the text file on a system running Windows or Windows NT, save the file in the Text (Windows) format.

The Text (*.txt) format saves all text and values as they appear in cells of the active worksheet.

However, if you open a file in Text (Unicode) format by using a program that does not read Unicode, such as Notepad in Microsoft Windows 95 or a Microsoft MS-DOS-based program, your data will be lost.

Note  Notepad in Microsoft Windows NT does read files in Text (Unicode) format.

The CSV (*.csv) file format saves only the text and values as they are displayed in cells of the active worksheet. All rows and all characters in each cell are saved. Columns of data are separated by commas, and each row of data ends in a carriage return. If a cell contains a comma, the cell contents are enclosed in double quotation marks.

If cells display formulas instead of formula values, the formulas are converted as text. All formatting, graphics, objects, and other worksheet contents are lost. The euro symbol will be converted to a question mark.

Note  If your workbook contains special font characters such as a copyright symbol (©), and you will be using the converted text file on a computer with a different operating system, save the workbook in the text file format appropriate for that system. For example, if you are using Windows and want to use the text file on a Macintosh computer, save the file in the CSV (Macintosh) format. If you are using a Macintosh computer and want to use the text file on a system running Windows or Windows NT, save the file in the CSV (Windows) format.

The DIF (*.dif) format saves only the text, values, and formulas on the active worksheet.

• All rows and all characters in each cell are saved.
• If worksheet options are set to display formula results in the cells, only the formula results are saved in the converted file. To save the formulas, display the formulas on the worksheet before saving the file. To display the formulas, select the Formulas check box on the View tab of the Options dialog box (Tools menu).
• Column widths and most number formats are saved, but all other formats are lost.
• Settings specified in the Page Setup dialog box (File menu) and manual page breaks are lost.
• Cell comments, graphics, embedded charts, objects, form controls, hyperlinks, data validation settings, conditional formatting, and other worksheet features are lost.
• The data displayed in the current view of a PivotTable report is saved; all other PivotTable data is lost.
• Visual Basic code is lost.
• The euro symbol will be converted to a question mark.

The SYLK (*.slk) format saves only the values and formulas on the active worksheet, and limited cell formatting.

• All rows are saved; up to 255 characters are saved per cell.
• If a Microsoft Excel function is not supported in SYLK format, Microsoft Excel calculates the function before saving the file and replaces the formula with the resulting value.
• Most text formats are saved; converted text takes on the format of the first character in the cell. Rotated text, merged cells, and horizontal and vertical text alignment settings are lost. The font color might be converted to a different color if you reopen the converted SYLK sheet in Microsoft Excel. Borders are converted to single-line borders. Cell shading is converted to a dotted gray shading.
• Settings specified in the Page Setup dialog box (File menu) and manual page breaks are lost.
• Cell comments are saved; you can display the comments if you reopen the SYLK file in Microsoft Excel.
• Graphics, embedded charts, objects, form controls, hyperlinks, data validation settings, conditional formatting, and other worksheet features are lost.
• Visual Basic code is lost.
• The data displayed in the current view of a PivotTable report is saved; all other PivotTable data is lost.
• If you are converting the file for use in Microsoft Excel for the Macintosh version 1.5 or earlier, clear the 1904 date system check box on the Calculation tab of the Options dialog box (Tools menu), and then make sure that all values are visible before you save the file.

Note  You can use the SYLK (Symbolic Link) format to save workbook files for use in Microsoft Multiplan. Excel 2003 does not include file format converters for converting workbook files directly into the Multiplan format.

The WQ1 (Quattro Pro for DOS) file format saves only the text, values, and formulas on the active worksheet.

• Microsoft Excel saves only rows 1 through 8192; up to 240 characters are saved per cell.
• If a Microsoft Excel function is not supported in WQ1 format, Microsoft Excel calculates the function before saving the file and replaces the formula with the resulting value.
• Text formatting is converted to 12-point Courier font; all other text formats, such as bold and italic, are lost.
• Column widths are saved, but merged cells, rotated text, and other cell formats are lost.
• Most number formats are saved.
• Borders are converted to the closest available border style in Quattro Pro. Cell shading and color border line styles are not supported.
• Settings specified in the Page Setup dialog box (File menu) and manual page breaks are lost.
• Cell comments, graphics, embedded charts, objects, form controls, hyperlinks, data validation settings, conditional formatting, and other worksheet features are lost.
• The data displayed in the current view of a PivotTable report is saved; all other PivotTable data is lost.
• Visual Basic for Applications code is lost.
• The euro symbol will be lost.

Quattro Pro graphs    Because Quattro Pro for Windows stores its graphs in a different way than Excel stores its charts, graphs in Quattro Pro for Windows are not imported when a Quattro Pro for Windows file is imported into Excel.

Quattro Pro Scenario Manager    The Microsoft Excel Scenario Manager is not directly equivalent to the Quattro Pro for Windows Scenario Manager. Therefore, Excel will import only the data from the scenario that was displayed the last time the file was saved in Quattro Pro for Windows.

External data links    Hyperlinks to external database tables or to the Data Modeling Desktop are not imported because they are not compatible with ODBC, Microsoft Query, or Microsoft Excel PivotTable reports.

OLE objects    Excel will not import embedded OLE objects from a Quattro Pro for Windows file.

Gradient fills    For gradient fills, the object is formatted by using the primary color from the fill.

Macros    Excel 2003, Excel 2002, and Excel 2000 do not run Quattro Pro macros. You can rewrite any macros that you need in Visual Basic for Applications. For information about writing Excel macro code, see Visual Basic Help.

The dBASE file formats DBF 2 (dBASE II), DBF 3 (dBASE III), and DBF 4 (dBASE IV) save only the text and values as they are displayed in cells of the active worksheet. All cell formatting, page layout settings, graphics, objects, and other Microsoft Excel features are lost. The data displayed in the current view of a PivotTable report is saved; all other PivotTable data is lost.

• All rows are saved; the following numbers of columns are saved:

  Format	Columns
  DBF 2 (dBASE II)	32
  DBF 3 (dBASE III)	128
  DBF 4 (dBASE IV)	255

• If a range of cells is named "Database," only that range of cells is converted. If you have added new records after naming the database range, you must redefine the range to include the new records before you save the file in dBASE format. If a database range is not defined and a single cell within the range is selected, the single cell is converted. If a range of cells is selected, the cells in the current region will be converted and saved. Any blank cells, or cells containing numbers in row one of the selected range will be converted to N1, N2, and so on.
• Text values in the first row of the range are used as dBASE field names, which are limited to 10 characters; subsequent characters are truncated. If the first row contains all numbers, automatic field names (N1, N2, and so on) are inserted in the converted file.
• Data types of values in the first row of data in the converted range determine the data types of each field (column) of data. If the first row of data contains a blank value for a field, the field is converted as a text field, even if subsequent rows contain numbers in that field; the numbers are converted as text.
• Only data visible in the worksheet cells is saved. Widen the columns in Excel, and make sure all the data is visible before you save the sheet in dBASE format.
• If cells contain decimal fractions but are formatted in Excel with the General number format, the values are rounded to the closest whole number. To preserve numbers to the right of the decimal point, format the cells with the Number format, and then specify the number of decimal places you want saved.
• If cells in the converted range contain formulas, only the resulting values of formulas are saved, even if the sheet display options are set to show formulas in the worksheet cells instead of values. To ensure that the formula values are converted correctly, click Options on the Tools menu, click the View tab, and clear the Formulas check box. Then make sure that all values are visible before you save the file. If you want to save a formula as text, insert an apostrophe (') before the formula.
• The euro symbol will be lost.