Opening 1-2-3 Files
Excel can directly open Lotus 1-2-3 files up to version 5.0 (the .wk4 file
format).
Formatting
When you open a Lotus 1-2-3 worksheet or workbook, Microsoft Excel applies
the formatting stored in any associated .fmt, .fm3, or .all files. Be sure
that the associated formatting file is stored in the same folder as the
.wk? file. If you resave a Lotus 1-2-3 file in the Microsoft Excel (.xls)
format, Microsoft Excel saves the spreadsheet data and formatting in a
single workbook file.
NOTE: Lotus 1-2-3 .wk4 files do not have an associated formatting file.
Charts
In Lotus 1-2-3, versions 3.x and later, you can create a graph on a chart
sheet or create the chart as an object on the worksheet. In Lotus 1-2-3,
version 2.x, if you use the WYSIWYG add-in, you can place a graph on a
worksheet.
Because Microsoft Excel can read Impress (.fm3) files and Allways
(.all) formatting files, you can import a Lotus 1-2-3 worksheet that
contains a chart on the worksheet. The chart appears on the worksheet as it
does in Lotus 1-2-3.
Databases
The Database, Criteria, and Extract defined ranges are successfully
imported and function properly. However, database criteria ranges are
evaluated differently when you extract data, find data, and use database
functions. For example, a criteria of "John" finds only rows with cells
that contain "John." If you clear the
Transition Formula Evaluation check box (click
Options on the
Tools menu and click the
Transition tab), a criteria of "John" finds rows that contain cells with values beginning with "John." For example, cells that contain "John," "Johnson," and "Johnsen" are found.
Calculations
Whenever you open a Lotus 1-2-3 file, the
Transition Formula Entry check box is selected. When this feature is selected, Microsoft Excel converts formulas that are entered with Lotus 1-2-3 syntax to Microsoft Excel syntax and makes names defined in Microsoft Excel behave as defined names do in Lotus 1-2-3.
Microsoft Excel calculates formulas differently from Lotus 1-2-3. When a
cell that contains text is used in a formula, Lotus 1-2-3 assigns a value
of 0 (zero) to the cell. In Microsoft Excel, you cannot combine text and
numeric entries in the same formula. However, when you use a worksheet
function in Microsoft Excel, a value of 0 is assigned to cells that contain
text. For example, if you clear the
Transition Formula Evaluation check box, and you type text in cell A1 and the value 100 in cell B1, the formula =A1+B1 returns the #VALUE! error value. However, the worksheet formula =SUM(A1,B1) returns the value 100.
Lotus 1-2-3 evaluates Boolean expressions to 0 or 1 and displays 0 or 1 in
the cell. For example, in Lotus 1-2-3, the expression 2<3 displays 1 in the cell to represent True; Microsoft Excel displays True or False in the cell.
If you select the
Transition Formula Evaluation check box, Microsoft Excel displays 0 for False and 1 for True.
Some functions, including @MOD, @VLOOKUP, and @HLOOKUP, are evaluated
differently. For example, the @VLOOKUP function in Lotus 1-2-3 searches for
an exact match in the first column; the VLOOKUP worksheet function in
Microsoft Excel assumes the first column is sorted and finds the closest
value in the first column that does not exceed the lookup value. The
VLOOKUP and HLOOKUP worksheet functions in Microsoft Excel include a fourth
argument, range_lookup. If you set this argument to False, Microsoft Excel
searches for an exact match.
To cause Microsoft Excel to calculate formulas as Lotus 1-2-3 does, follow
these steps:
- On the Tools menu, click Options. Click the Transition tab.
- Click to select the Transition Formula Evaluation check box, and click OK.
Calculation Order
Mathematical Order of Precedence Differences:
The following table compares the mathematical operators used by Microsoft
Excel and Lotus 1-2-3.
Lotus Microsoft
Operator 1-2-3 Precedence Excel Precedence
---------------------------------------------------------------
Exponentiation ^ 1st ^ 2nd
Positive and + and - 2nd + and - 1st
negative
Multiplication * and / 3rd * and / 3rd
and division
Addition and + and - 4th + and - 4th
Subtraction
Comparison = < > 5th = < > 5th
<= >= <= >=
Logical NOT #not# 6th NOT() 6th
Logical AND #and# and 7th AND() and 7th
and OR #or# OR()
String & 7th & 7th
concatenation
: Lotus 1-2-3 evaluates the exponentiation operator (^) before the negation operator (-). Microsoft Excel evaluates the negation operator
first. For example, in Lotus 1-2-3, the formula =-2^4 returns the value
-16, but returns 16 in Microsoft Excel. To correct this difference, use
parentheses to change the order of evaluation; for example, use =-(2^4)
to produce -16.
Links
In Microsoft Excel, when you open a Lotus 1-2-3 .wk4 file that contains a
link to another file, the cells may be updated with a #REF! error value. To
update an external link in a Lotus 1-2-3 .wk4 file, follow these steps:
- In Microsoft Excel, click Links on the Edit menu.
- In the Links dialog box, select the link that you want to update. Click Update Now.
NOTE: If you want to open the source document, click Open. This also updates the external link.
To avoid this behavior, save the file in the Microsoft Excel workbook
format.
Macros
Microsoft Excel 2000 does not run Lotus 1-2-3 macros. You can rewrite any
macros that you need in Microsoft Visual Basic for Applications. For
information about writing Excel macro code, see Visual Basic Help.