Troubleshoot converting file formats

Some of the content in this topic may not be applicable to some languages.

ShowI can't open my Lotus 1-2-3 file in Microsoft Excel.

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.

ShowI can't open a file I created in Microsoft Works.

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.

ShowI can't open my Quattro Pro files 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.

ShowMy workbook is very large and takes a long time to open.

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.

ShowUse Lotus 1-2-3

  1. Open the original WK4 file in Lotus 1-2-3, and select all blank cells below the last cell in the worksheet that contains data.
  2. On the Edit menu, click Delete, and then click OK.
  3. Save and close the worksheet, and open it again in Excel.

ShowUse Excel

  1. In Excel, select all blank rows below the last cell in your worksheet that contains data.
  2. On the Edit menu, click Delete.
  3. Save the workbook.

ShowThe formatting I applied in Excel is gone.

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.

ShowValues on the converted sheet don't match the values on the original sheet.

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.

ShowSome formulas were changed to text or values.

When Excel opens a file created in another spreadsheet program and encounters an operator (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) or function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) 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.

ShowDates and phone numbers from my Lotus 1-2-3 file are displayed as text.

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.

ShowUse Lotus 1-2-3

  1. Open the file in Lotus 1-2-3.
  2. Change dates that use a Text number format to a Date number format.
  3. Replace hyphens in dates with forward slashes (/).

ShowUse Excel

  1. Double-click the cell.
  2. Delete the equal sign (=) in front of the date or phone number, and press ENTER.

Dates appear with forward-slash separators.

 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.

ShowMy Lotus 1-2-3 or Quattro Pro macros don't run in Microsoft Excel.

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 (Microsoft Visual Basic Help: To get help for Visual Basic in Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.).

ShowAll of the text in the file appears in the first column.

  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.

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

ShowWhen I save data in dBASE format, some of my data is missing.

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:

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

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 (current region: The block of filled-in cells that includes the currently selected cell or cells. The region extends in all directions to the first empty row or column.) 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").

ShowLinks to other Lotus 1-2-3 worksheets aren't converted correctly or have #REF! errors.

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.
 
 
Applies to:
Excel 2003