Troubleshoot converting file formats

Excel 2000 or later versions can't open some password-protected files.

1. Open the file in Lotus 1-2-3.
2. Save the file without password protection, or save it as a WKS or WK1 file.
3. Open the file in Excel.

Notes

• Excel can open password-protected WKS and WK1 files and prompts you for the password before the worksheet is opened.
• Excel cannot open WK3 or WK4 files that are protected with a file-level password.
• Excel can open Lotus 1-2-3 files that are protected at the worksheet level.

Excel 2000 or later versions cannot directly open files created in Microsoft Works version 3.0 or later. To open a Works file in Excel, first open it in Works and use the Save As command to convert it to one of the file formats listed below. Then close the Works file and open the converted version in Excel.

• Works for Windows 2.0/Works for DOS SS
• Excel SS
• Lotus 1-2-3

To open a Microsoft Works database file in Excel, first save the file in Microsoft Works as a dBASE file, and then open the dBASE file in Excel.

Excel 2000 and later versions cannot open Quattro Pro files that are password protected. Before you open the file in Excel, make sure you have removed any password from the file. You may need to install the Quattro Pro converter. This is available on the Microsoft Office Web site.

When you open a workbook that was created in an earlier version of Microsoft Excel, the workbook is completely recalculated to ensure that all formulas are fully optimized for the current Excel version. After you save the workbook in the new version, the next time you open it, opening will take less time.

When you worked on the file in Lotus 1-2-3, you may have formatted entire columns. Microsoft Excel converts all formatted cells, even if they're blank, making the worksheet large and slow to open. You can correct the problem in Lotus 1-2-3 or in Excel.

You may have opened a file from another program, applied Excel formatting, then saved in the original format. This removes all of the Excel formatting.

If you changed the extension of the file to .xls but didn't select the Microsoft Excel Workbook format in the Save As dialog box, the Excel formatting is not saved.

Excel calculates some operators and functions in a different order from other spreadsheet programs. To calculate formulas using the same rules as Lotus 1-2-3, click Options on the Tools menu, and then click the Transition tab. Under Sheet options, select the Transition formula evaluation check box.

When Excel opens a file created in another spreadsheet program and encounters an operator or function that it cannot convert, it uses the result of the formula— not the formula itself— for the contents of the cell. You can reenter the formula on your Excel worksheet by using an equivalent operator or function.

Some Lotus 1-2-3 functions don't have equivalent Excel functions. Cells containing unconverted formulas are identified by comments and show the last values that were calculated in Lotus 1-2-3. To correct this problem, view comments, locate the unconverted formulas, and rewrite them. To display all of the comments, click Comments on the View menu.

You formatted the date or phone number cells with a Text number format, and you used hyphens to separate the numbers (for example, 2-10-98 or 555-1212) in your Lotus 1-2-3 worksheet. You can correct the problem in Lotus 1-2-3 or in Excel.

Note  To display a date with hyphen separators, click the cell, and then click Cells on the Format menu. Click the Number tab, and click the date format you want in the Type box.

Excel 2000 and later versions do not run Lotus 1-2-3 or Quattro Pro macros. You can rewrite any macros that you need in Visual Basic for Applications (VBA). For information about writing Excel macro code, see Visual Basic Help.

1. Select the range of cells that contains the text values. The range can be any number of rows tall, but no more than one column wide.

   Note  There must be enough blank columns to the right of the selected column to accommodate the columns of data you want to create or the data to the right of the selected column will be overwritten.

2. On the Data menu, click Text to Columns.
3. Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

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 Excel features are lost. The data displayed in the current view of a PivotTable report is saved; all other PivotTable data is lost.

Depending on the dBase format, only the following numbers of columns in the active worksheet are saved:

Only data in the named range or current region is saved    When you save an Excel worksheet in dBASE (DB2, DB3, or DB4) format and the worksheet contains a range named "Database," only data in the named range is saved in the dBASE file. If you add new records after naming the range, you must redefine the database range to include the new records before you save the sheet in dBASE format.

If there is no range named "Database" on the sheet, only data in the current region is converted. If the first row of data contains text, Excel uses it as the header row to define field names. If the first row contains all numbers, Microsoft Excel creates field names such as N1, N2, and so on.

Make sure character strings are shorter than column width    When saving data in dBASE format, Excel assigns a data type to each field (column of data) that is based on the field data in the first record of the database range or current region.

If a field in the first record contains text, that field is assigned the character data type, and any numbers contained in the field in other rows become character strings. The column width of the field determines the string length; character strings longer than the column width are truncated in dBASE. To prevent losing data, select the range you want to convert in Excel, and apply a monospaced font such as Courier. To size the columns to show all the data, point to Columns on the Format menu, and then click AutoFit Selection. Numeric data fields cannot contain text; any text in a numeric data field becomes null.

Use a number format other than General    If decimal numbers have the General number format, the decimal places are truncated in dBASE. Before you save the data in dBASE format, apply a different number format to all data in that field. On the Format menu, click Cells, and then click the Number tab. In the Category box, click Number or Scientific, and then specify the number of decimal places you want.

Change time data to text data    Fields that contain time data cannot be converted. Before you save data in dBASE format, you can change time data to text data by using the TEXT worksheet function. For example, to change the time 12:34 PM to text data, use the function =TEXT("12:34", "hh:mm AM/PM").

When you open a Lotus 1-2-3 worksheet in Microsoft Excel, you must click Yes when prompted to update the workbook with changes for Excel to convert links in the worksheet properly. When you convert a worksheet to Excel, you can maintain links to other Lotus 1-2-3 worksheets, or you can convert all the workbooks to Excel and change the links accordingly.

Correct the #REF! errors

1. In Excel, close the converted worksheet without saving.
2. Open the worksheet again in Excel, but this time click Yes when prompted to update the workbook with changes.

Keep links to other Lotus 1-2-3 worksheets when you convert to Excel

1. Make sure the linked-to Lotus 1-2-3 worksheets are closed.
2. In Excel, open the Lotus 1-2-3 worksheet that contains the links, and click Yes when prompted to update the workbook with changes.
3. On the File menu, click Save As, and then click Microsoft Excel Workbook in the Save as type box.

Change links to use converted Excel workbooks

1. In Excel, open all the linked-to Lotus 1-2-3 worksheets.
2. Open the worksheet that contains the links.
3. For each linked-to worksheet, click Save As on the File menu, and then click Microsoft Excel Workbook in the Save as type box.
4. Save the workbook that contains the links.