|
C H A P T E R 18 |
Part 4 Switching from Other Applications |
Microsoft Office Resource Kit |
| Switching to Microsoft Excel |
|
|
This chapter tells you what to expect when you or your workgroup
switches to Microsoft Excel 97 for Windows or Excel 98 for the Macintosh from another spreadsheet program.
The primary questions most new Excel users have are:
- What happens to my old documents when I open them in Excel 97 (Windows) or Excel 98 (Macintosh)?
- Can I share Excel 97 or 98 documents with users of my old
spreadsheet program?
- Do my old macros still work in Excel 97 or 98?
If you are switching to Excel 97 or 98, this chapter
answers these questions for you.
- Enter any formula or function
as you would in Lotus 123.
Excel automatically translates the formula
into an Excel formula when you click the enter box (the
checked box) in the formula bar or press ENTER. For example, if
you enter the formula @AVG(A1..A5), Excel changes it
to =AVERAGE(A1:A5).
- Enter a reference that corresponds
to a defined range name.
The range name appears in the formula after you click
the enter box or press ENTER.
- Edit the reference
of a range name in the formula bar by clicking the formula bar,
which automatically displays the reference.
The range name reappears when you click the enter
box or press ENTER.
- Delete a range name.
All formulas that contain that range name change to
display the reference instead of the range name.
- Use
a dollar sign ($) before a range name to make the range name absolute.
When you turn transition formula entry on or off, formulas do
not automatically reapply names or revert names to references.
So you should decide whether you want to use transition formula
entry feature before you begin working on a worksheet. After you
turn on transition formula entry, it remains in effect for that
worksheet until you turn it off, even if you save a Lotus 123
worksheet as an Excel workbook.
Note Transition
formula entry affects only range names that are simple and absolute,
and does not affect range names that refer to nonadjacent selections.
All Lotus 123 range names that originate in Lotus 123
are affected by transition formula entry.
Transition Formula Evaluation
Excel and Lotus 123 evaluate certain
formulas and expressions differently. Transition formula
evaluation allows Excel to calculate formulas
and database criteria according to Lotus 123
rules.
To
use Lotus 123 rules to calculate formulas and
database criteria
- On the Tools menu,
click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.
- Under Sheet Options, select the Transition formula evaluation check box.
The following table compares expressions that are evaluated differently
in Excel and Lotus 123.
| This expression |
Is evaluated this way in Lotus 123
| And this way in Excel
|
| Cells that contain text
| When the cell is used in a formula, it is given a value of zero (0).
| The cell containing text is ignored in the calculation. In Excel, you cannot combine text and numeric entries in the same formula.
|
| Boolean expressions |
Boolean expressions are evaluated to 0 or 1 and display 0 or 1 in the cell. For example, 2<3 shows a 1 in the cell to represent True.
| Excel also calculates Boolean expressions as 0 and 1, but displays FALSE or TRUE, respectively, in the cell.
|
Note also the following differences:
- In Excel, database
criteria ranges are evaluated differently when you are extracting
data, finding data, and using database functions.
For example, computed criteria can use existing field
names.
- Certain Lotus 123 functions,
including @MOD, @VLOOKUP, and @HLOOKUP, are evaluated differently
than the equivalent Excel functions.
For example, the Lotus 123 @VLOOKUP
function performs literal matches on text, whereas the Excel VLOOKUP function returns a lookup value for nonliteral text,
using the nearest entry in alphabetic order.
When you open a Lotus 123 worksheet in Excel, transition formula evaluation is automatically turned on
for that sheet. This condition ensures that the formulas are calculated
according to the preceding Lotus 123 rules.
If you save the Lotus 123 worksheet as an Excel workbook, the transition formula evaluation option remains
turned on until you turn it off. For workbooks created in Excel, however, transition formula evaluation is not automatically
turned on.
Note Avoid
turning transition formula evaluation on and off while working
with a document in Excel; otherwise, the values calculated
on your worksheet might change. If you leave transition formula
evaluation turned off, your worksheet adheres to Excel
rules. If you leave the option turned on, your worksheet adheres
to Lotus 123 rules.
New A Functions in Microsoft Excel
Excel includes several new functions that are
evaluated in the same way as their corresponding Lotus 123
functions, regardless of whether the transition formula evaluation
option described in the previous section is turned on. For example,
the Excel AVERAGE function evaluates only numeric data.
However, the new Excel function AVERAGEA evaluates numeric
as well as nonnumeric data, just as its corresponding Lotus 123
function @AVG does.
Like the COUNTA function introduced previously in Excel,
these new functions follow the naming convention of NameA
and are therefore referred to as A functions.
The new A functions and their corresponding Lotus 123
functions are listed in the following table.
| This Excel A function
| Corresponds to this Lotus 123 function
|
| AVERAGEA | @AVG
|
| MINA | @MIN
|
| MAXA | @MAX
|
| VARA | @VARS
|
| VARPA | @VARP
|
| STDEVA | @STDS
|
| STDEVPA | @STD
|
Note When
a Lotus 123 sheet is opened in Excel,
Lotus 123 functions that correspond to A functions
are automatically converted to A functions.
In general, the A functions treat text and logical values differently
than the equivalent nonA functions in Excel, as
described in the following table.
| Value type | NonA functions
| A functions |
| Numbers | Treated normally
| Treated normally |
| Text | Text is ignored
| Text has a value of 0
|
| TRUE Logical Value |
TRUE is ignored | TRUE has a value of 1
|
| FALSE Logical Value |
FALSE is ignored | FALSE has a value of 0
|
 |
Tools
and Utilities The
Office Resource Kit Tools and Utilities include the Office 97
Resource Kit Help file, a collection of Knowledge Base articles
about Office 97 written by Microsoft Technical Support.
The article XL97: New A Functions in Microsoft Excel 97 (ID:
Q156445) contains detailed information about the new A functions.
For more information, see "Microsoft Technical Support Help File" in Appendix A,
"Microsoft Office Resource Kit Tools and Utilities."
|
 | Tip In
Windows, you can open more than one file at a time if all the
files are within a single folder. To select a contiguous group
of worksheets, click the name of the first worksheet you want
to open, and then hold down SHIFT and click the name of the last
worksheet you want to open. Or hold down CTRL and click the names
of the individual worksheets you want to open.
|
| | Replacing the Lotus 123 System Command (Windows only)
|
- Click the Start menu, point to Programs, and then click MSDOS Prompt.
To
activate the command prompt in Windows NT Workstation 3.51
Auditing Converted Worksheets
Audits conducted by the industry on corporate MSDOSbased
worksheets have found that approximately 30 percent of all worksheets
contain serious errors. In some cases, major decisions have been
made using worksheets that have been incorrect for years.
The only way to catch these errors is with a worksheet audit.
You can do the audit while the worksheet is in Lotus 123
or after it is converted to Excel. The best course is
a partial audit on both sides, since each audit catches different
problems.
Auditing Your Worksheets Before Conversion
Auditing your Lotus 123 worksheets before conversion
catches problems inherent in the original worksheet, such as values
that have replaced formulas, circular errors, incorrect results,
and bad range names.
Auditing Your Worksheets After Conversion
Auditing after conversion catches problems introduced by the conversion
process or by reorganization and linking. Auditing worksheets
in Excel helps you find formulas that did not convert,
links that are incorrect, or unexpected problems for which you
might need additional help. The following Excel features
are particularly useful for auditing:
- Auditing submenu commands (Tools menu)
- Find command (Edit menu)
- Special button in the Go To dialog box (Edit menu)
Note If
Excel encounters formulas that it cannot convert when
you open a Lotus 123 worksheet, only the resulting
values are displayed. The original formula is discarded. Excel indicates this by displaying a cell comment (and cell comment
indicator) in the cell, containing the message "Formula failed
to convert." For more information, see "Using Lotus
1-2-3 Formulas in Microsoft Excel" later in this section.
To
search for cells containing formulas that did not convert correctly
- On the Edit menu,
click Find.
- In the Look in box, click Comments.
- In the Find what box, type formula failed to convert
- Click Find Next.
The first cell containing the text you entered is selected. A message appears if the text cannot be found.
- Click the Find Next button again to go to the next cell with
a comment containing the text.
After you have converted the worksheet, you can select all cells
with cell comments.
To
select all cells with cell comments
- On the Edit menu,
click Go To.
- Click Special, and
then click Comments.
This selects all cells with comments, allowing you to see where
your formulas did not convert.
You can also print the comments along with the sheet and then
use this printed document as a reference for troubleshooting.
To
print cell comments
- On the File menu,
click Page Setup, and then click the Sheet tab.
- Under Print, select the option you want in the Comments box.
Cell comments consist of all comments inserted by Excel
during the conversion process, as well as all cells converted
from WK3 files that contain Lotus 123style
text notes in their formulas.
The Special button in the Go To dialog box (Edit
menu) is a powerful tool for auditing converted worksheets. Using
the options in this dialog box, you can find cells that:
- Supply values to the active
cell (Precedents option)
- Use the value
in the active cell (Dependents option)
- Contain
only values (Constants option)
- Contain
only formulas (Formulas option)
- Contain
error values (Errors check box under Formulas option)
- Contain
comments (Comments option)
- Contain different
reference patterns in a row or column (Row differences
or Column differences option)
Auditing with Cell Tracers
Another auditing feature included with Excel is cell
tracers. Cell tracers are arrows drawn on a worksheet
that point to the precedents or dependents of a selected cell,
or trace the error path of a cell containing an error value. Use
the Auditing submenu commands (Tools menu) to display
tracer arrows. Alternatively, you can point to Auditing
and then click Show Auditing Toolbar to display the Auditing
toolbar, which you can use to trace the flow of data between cells
on your worksheet.
In the following illustration, cell J8 was selected and the Trace
Precedents button (Auditing toolbar) was clicked twice.
The first click added arrows pointing to the first level of precedent
cells, J5:J7; the second click indicated the second level of precedent
cells, C2:C4 and C8:C10.
Using Lotus 123 Formulas in Microsoft Excel
Most Lotus 123 formulas and functions convert
to their Excel equivalents when the worksheet is opened.
If Excel finds formulas that it cannot convert when
you open a Lotus 123 worksheet, then only the
resulting values are preserved; the original formula is lost.
Excel indicates this in two ways:
- First, when Excel
encounters a formula that it cannot convert, a message notifies
the user that Excel cannot read the record.
- Second,
a cell comment (along with a cell comment indicator) appears in
the cell, containing the message "Formula failed to convert."
Translating Lotus 123 Nested Formulas
Perhaps the most common reason for the "Cannot read record"
message when converting Lotus 123 formulas to
Excel occurs when a formula in your Lotus 123
worksheet uses more than seven levels of nesting. To get around
this, you can break the formula into sections of less than seven
nested segments before conversion. However, many such nested formulas
exist in order to construct elaborate alternative calculations
based on a range of current conditions, such as @IF statements.
In this case, a better solution is to create a formula using @VLOOKUP
and refer to a table elsewhere on the worksheet. Then no nesting
is needed, and you end up with a more readable and structured
formula.
For example, suppose that in one cell you have the following Lotus 123
formula that arrives at a value, based on the name of a month
from January to September:
@IF(a1="Jan",12,@IF(a1="Feb",2,@IF(a1="Mar",4,@IF(a1="Apr",34,
@IF(a1="May",32,@IF(a1="Jun",8,@IF(a1="Jul",43,@IF(a1="Aug",3,
@IF(a1="Sep",67,0)))))))))
This formula has nine levels of nesting. To make conversion to
Excel easier, rewrite the formula like this in Lotus 123:
@VLOOKUP(a1,table,1)
where table is a range name that refers to
the following twocolumn table, located anywhere on the worksheet.
Jan 12
Feb 2
Mar 4
Apr 34
May 32
Jun 8
Jul 43
Aug 3
Sep 67
When you then open the file in Excel (and transition
formula evaluation is automatically turned on), the formula converts
without problems, and it works properly. (The offset argument
1 is automatically converted to 2 because Excel starts
counting at 1, not 0.) Using a table in this way is not only easier
to read than the original formula, but it is also easy to modify
by changing or adding new values.
Translating Lotus 123
Functions
Most Lotus 123 functions have an equivalent in
Excel. When transition formula entry is activated, you
can type most Lotus 123 functions directly into
the formula bar, where they are automatically converted to their
Excel equivalents. The functions you cannot enter in
this way are specific to Lotus 123 Release 3
and later. For more information about transition formula entry,
see "Microsoft Excel Transition Options" earlier in
this chapter.
Excel also uses different rules than Lotus 123
when evaluating text in formulas, certain database criteria, and
the value of certain logical operators. When transition formula
evaluation is activated, some functions are interpreted as they
would be in Lotus 123. These functions are listed
alphabetically in the following table, along with their
Excel equivalents.
| This Lotus 123 function
| Corresponds to this Excel function
| Comments |
| @@ | INDIRECT
|
|
| @ABS | ABS
|
|
| @ACOS | ACOS
|
|
| @ASIN | ASIN
|
|
| @ATAN | ATAN
|
|
| @ATAN2 | ATAN2
|
|
| @AVG | AVERAGE
|
|
| @CELL | CELL
|
|
| @CELLPOINTER | CELL
| In Excel, when you use the CELL function without a second argument, it returns information about the current selection.
|
| @CHAR | CHAR
|
|
| @CHOOSE | CHOOSE
|
|
| @CLEAN | CLEAN
|
|
| @CODE | CODE
|
|
| @COLS | COLUMNS
|
|
| @COS | COS
|
|
| @COUNT | COUNTA
|
|
| @CTERM | NPER
| In Excel the NPER function requires periodic payment instead of future value.
|
| @DATE | DATE
|
|
| @DATEVALUE | DATEVALUE
|
|
| @DAVG | DAVERAGE
|
|
| @DAY | DAY
|
|
| @D360 | DAYS360
| The @D360 function is available in Lotus 123 Release 3 and later.
|
| @DCOUNT | DCOUNTA
|
|
| @DDB | DDB
|
|
| @DGET | DGET
|
|
| @DMAX | DMAX
|
|
| @DMIN | DMIN
|
|
| @DSTD | DSTDEVP
|
|
| @DSTDS | DSTDEV
| The @DSTDS function is available in Lotus 123 Release 3 and later.
|
| @DSUM | DSUM
|
|
| @DVAR | DVARP
|
|
| @DVARS | DVAR
| The @DVARS is available in Lotus 123 Release 3 and later.
|
| @ERR | None
| In Excel no equivalent is necessary, because Excel lets you type error values directly into cells and formulas.
|
| @EXACT | EXACT
|
|
| @EXP | EXP
|
|
| @FALSE | FALSE
|
|
| @FIND | FIND
|
|
| @FV | FV
|
|
| @HLOOKUP | HLOOKUP
| In Excel turn on transition formula evaluation to use the Lotus 123 evaluation rules.
|
| @HOUR | HOUR
|
|
| @IF | IF
| In Excel the last two arguments of the IF function can be any value, not just numbers or strings, as in @IF.
|
| @INDEX | INDEX
| In Excel the INDEX function also has a form for selecting values from an array.
|
| @INT | TRUNC or INT
|
|
| @IRR | IRR
| In Excel the arguments are given in reverse order.
|
| @ISERR | ISERR
| The ISERR function detects any of six Excel error values.
|
| @ISNA | ISNA
|
|
| @ISNUMBER | ISNONTEXT or ISNUMBER
|
|
| @ISRANGE | ISREF
| The @ISRANGE function is available in Lotus 123 Release 3 and later.
|
| @ISSTRING | ISTEXT
|
|
| @LEFT | LEFT
|
|
| @LENGTH | LEN
|
|
| @LN | LN
|
|
| @LOWER | LOWER
|
|
| @LOG | LOG
|
|
| @MAX | MAX or MAXA
|
|
| @MID | MID
|
|
| @MIN | MIN or MINA
|
|
| @MINUTE | MINUTE
|
|
| @MOD | MOD
| In Excel turn on transition formula evaluation to use the Lotus 123 evaluation rules.
|
| @MONTH | MONTH
|
|
| @N | N
|
|
| @NA | NA
|
|
| @NOW | NOW
|
|
| @NPV | NPV
|
|
| @PI | PI
|
|
| @PMT | PMT
| The arguments for the PMT function are in different order than they are in @PMT.
|
| @PROPER | PROPER
|
|
| @PV | PV
| The arguments for the PV function are in different order than they are in @PV.
|
| @RAND | RAND
| In Excel the RAND function calculates values randomly each time it is recalculated; @RAND calculates the same values in each work session.
|
| @RATE | RATE
| The arguments for the RATE function are in different order than they are in @RATE.
|
| @REPEAT | REPT
|
|
| @REPLACE | REPLACE
|
|
| @RIGHT | RIGHT
|
|
| @ROUND | ROUND
|
|
| @ROWS | ROWS
|
|
| @S | T
|
|
| @SECOND | SECOND
|
|
| @SIN | SIN
|
|
| @SLN | SLN
|
|
| @SQRT | SQRT
|
|
| @STD | STDEVP or STDEVPA
|
|
| @STDEV | STDEV or STDEVA
|
|
| @STDEVP | STDEVP
|
|
| @STDS | STDEV or STDEVPA
| The @STDS function is available Lotus 123 Release 3 and later.
|
| @STRING | FIXED
|
|
| @SUM | SUM
|
|
| @SYD | SYD
|
|
| @TAN | TAN
|
|
| @TERM | NPER
| The arguments for the NPER function are in different order than they are in @TERM.
|
| @TIME | TIME
|
|
| @TIMEVALUE | TIMEVALUE
|
|
| @TODAY | TODAY
|
|
| @TRIM | TRIM
|
|
| @TRUE | TRUE
|
|
| @UPPER | UPPER
|
|
| @VALUE | VALUE
|
|
| @VAR | VARA
|
|
| @VARP | VARPA
|
|
| @VARS | VAR or VARA
| The @VARS function is available in Lotus 123 Release 3 and later.
|
| @VDB | VDB
|
|
| @VLOOKUP | VLOOKUP
| In Excel turn on transition formula evaluation to use the Lotus 123 evaluation rules.
|
| @YEAR | YEAR
|
|
|
Getting Help with Microsoft Excel Functions
For details about Excel functions, click the Paste
Function button (Standard toolbar), select the function
you want, and then click the Question Mark button. In the
Office Assistant, click Help with this feature, and then
click Help on selected function.
Functions are divided into categories in the Function Wizard
dialog box. For example, the FREQUENCY, LINEST, LOGEST, GROWTH,
and TREND functions are located in the Statistical category,
while the MINVERSE and MMULT functions are located in the Math
& Trig category. Help for the Analysis ToolPak is
available by clicking the Help button in any Analysis
ToolPak dialog box.
|
Translating Lotus 123
Pure Functions
Lotus 123 includes several pure functions
that are evaluated in the same way as some Excel and
Multiplan functions. For example, the @PUREAVG function is evaluated
in the same way as the Excel function AVERAGE: Both
functions evaluate only numeric data. When a Lotus 123
worksheet is opened in Excel, all pure functions are
automatically converted to their corresponding Excel
functions, regardless of the setting of the Transition formula
entry option on the Transition tab in the Options
dialog box
(Tools menu).
Using Mathematical Operators in Lotus 123
and Microsoft Excel
The following table shows the mathematical operators used by Excel and Lotus 123, in descending order of evaluation.
| Lotus 123 order of operators
| Excel order of operators
|
| ^ | AND, OR, NOT functions
|
| + or – (unary)
| + or – (unary)
|
| * or / | ^
|
| + or – | * or /
|
| = < > <= >= <>
| + or – |
| #not# (unary) | &
|
| #and# #or# | = < > <= >= <>
|
| & (Release 2.0 and later)
|
|
In Lotus 123, the exponentiation operator (^)
is evaluated before the negation operator ( – );
in Excel, negation is evaluated first. Thus, the formula
=–2^4
produces the value –16 in Lotus 123,
and 16 in Excel. To change this, use parentheses to
force the preferred order of evaluation in Excel. For
example:
=–(2^4)
|
Using Threedimensional Formulas in Workbooks
If you store a group of worksheets with identical layouts, such
as monthly reports, in the same workbook, you can use threedimensional
formulas to consolidate data into summary worksheets. These threedimensional
formulas allow you to specify sheet ranges in a workbook, which
are similar to cell ranges on a worksheet. You can apply a number
of different functions, such as SUM and AVERAGE, to the resulting
threedimensional range.
For example, the formula SUM(Sheet1:Sheet4!$A$1) sums the contents
of cell A1 on the contiguous sheets named Sheet1, Sheet2, Sheet3,
and Sheet4.
You can also use the Consolidate command (Data menu)
to create summary reports for sheets that have an identical or
similar layout.
|
Using Equivalents for the Lotus 123 Data Distribution Command
There is no equivalent command in Excel for the Lotus 123
Data Distribution command. Instead, Excel provides
the FREQUENCY function, which calculates a data distribution.
You can also choose the Data Analysis command (Tools
menu) and select the Histogram analysis tool to calculate
frequencies for a range of data.
 | Note If you are using Excel 98 (Macintosh), the Analysis ToolPak is automatically installed when you choose an Easy installation during Setup.
|
| | Note If you are using Excel 97 (Windows) and the Data Analysis command does not appear on the Tools menu, you need to install the Analysis ToolPak addin. To do this task,
click AddIns on the Tools menu, and then select the Analysis ToolPak check box. If the Analysis ToolPak option does not appear in the AddIns dialog box, rerun Setup and click Add/Remove. Under the Microsoft Excel option, select the AddIns option, and then select the Analysis ToolPak option.
|
- Macros that are not compatible
with Lotus 123 Release 2.01
- Macros
that contain keystrokes for menu commands available only in Release
2.2 or later
- Macros that call a Lotus 123
addin
When a macro does not run, a dialog box appears that identifies
the cell address where the error occurred. It is a good idea to
make a note of this cell reference. Also, the dialog box contains
a Help button that you can click to see more information
about possible causes of the error. If you have macros that call
Lotus 123 addins, you need to remove these
macro statements from your Lotus 123 macros.
You can, however, call Excel procedures from Lotus 123
macros, as described later in this section.
Substituting Standard Microsoft Excel
Features for Lotus 123 Macros
Many Lotus 123 macros do not need to be converted.
These macros, which aid the user with formatting or printing from
Lotus 123, are replaced by standard features
in Excel. Some of the most common Lotus 123
macros and the Excel features that replace them are
described in the following table.
| This Lotus 123 macro action
| Corresponds to this Excel feature
|
| Input printer setup strings
| Font tab in the Cells (Windows) or Format Cells (Macintosh) dialog box (Format menu) and Page Setup dialog box (File menu)
|
| Accept dates, parse into YY,MM,DD, and then reenter with @Date and formats
| Automatic date acceptance and formatting
|
| Prompt to select ranges for chart data
| Chart Wizard button (Standard toolbar)
|
| Format anything quickly
| Style command (Format menu)
|
| Adjust column width |
Drag the right border of the column heading, or doubleclick for best fit
|
| Adjust multiplecolumn widths simultaneously
| Select multiple columns, and then drag the right border of a column heading or doubleclick a border column heading for best fit
|
| Sum a column or row |
AutoSum button (Standard toolbar)
|
| Align text with Range Label Align
| Align Left, Align Right, or Center buttons (Formatting toolbar)
|
| Underline | Border and Font tabs in the Cells (Windows) or Format Cells (Macintosh) dialog box (Format menu)
|
| Shift a block of cells |
Insert and Delete commands on the shortcut menu
|
| Enter commonly used formulas
| Workbook containing an Excel Visual Basic for Applications module with function procedures to share among users
|
| Redefine and update multiple data tables (Lotus 123 can have only one data table active at a time)
| Multiple data tables available in a worksheet without redefinition
|
| Request data by line item or build a data entry form on the worksheet
| Form command (Data menu) for data entry and editing that automatically creates a custom data form without macros
|
| Change to commonly used directories using the File Dir command. (The File Dir command is a separate command from the File Retrieve.)
| Change folders and open files in the Open dialog box (File menu)
|
| Split horizontal or vertical windows
| Drag window split bar |
| Insert monthly, quarterly, or weekly headers
| Use the AutoFill feature by dragging the fill handle
|
Running Macros Created in Lotus 123 Release 2.2
Excel can run macros that contain any Lotus 123
Release 2.2 advanced macro commands, such as {BORDERSON}, {BORDERSOFF},
{FRAMEON}, {FRAMEOFF}, {GRAPHON}, and {GRAPHOFF}. Excel
also reads linking formulas created by Release 2.2. However,
Excel cannot run macros that use slash menu commands that are
specific to Release 2.2.
Converting Lotus 123
Release 2.2 Macro Library Files
If you have Lotus 123 macros in macro libraries
(macros in Lotus 123 Release 2.2 MLB file format),
you can convert them to Excel.
To
convert Lotus 123 MLB files
- In Lotus 123,
load the file by attaching the addin.
- In
the Lotus Macro Library Manager, copy the library commands
to a worksheet using the Edit command.
- Save
the worksheet in Lotus 123 WK1 format.
- Open
the Lotus 123 worksheet in Excel.
Running Autoexec Macros
If you have a Lotus 123 autoexec macro (named
\0) on your worksheet, the macro runs automatically when you open
the worksheet in Excel.
To
open a worksheet without running the autoexec macro
- On the File menu,
click Open.
- Under the Look in
box, select the workbook, and then hold down SHIFT
and click OK.
If you have both an Excel macro named Auto_Open that
refers to a macro sheet and a Lotus 123 \0 macro
on the same worksheet, the Auto_Open macro runs first, and then
the \0 macro runs.
Removing Lotus 123
Addins
Be sure to remove any occurrence of keystrokes or command names
that attach, start, or use a Lotus 123 addin,
such as the Allways addin and its menu structure. For example,
remove statements such as /a and {app1}.
Altering Lotus123 Macros
That End in a Menu
When you run a Lotus 123 macro in Excel,
the Lotus 123 macro cannot end in a menu, such
as the keystrokes /PP (Print Printer). If a macro does end in
a menu, a message appears stating that macros cannot end in a
menu. Then the macro terminates. The macro can, however, end in
a prompt for more information, such as the keystrokes /PPR (Print
Printer Range), so that you can specify the print range.
Getting Help Within Macro Prompts
When you run a Lotus 123 macro that contains
a command for user input, such as /XN, /XL, {GETLABEL}, or {GETNUMBER},
Excel displays a dialog box requesting user input. Enter
the information, and then click OK.
If a Lotus 123 macro contains custom menu commands,
such as /XM, {MENUBRANCH}, or {MENUCALL}, the Menu dialog
box appears and displays your menu choices.
Tip For
online Help with macro prompt dialog boxes, press F1.
Verifying Options Before Running Lotus 123
Macros
When you run a Lotus 123 macro, transition formula
entry and transition navigation keys are temporarily turned on,
and the Move Selection After Enter option is temporarily turned
off. However, transition formula evaluation is not automatically
turned on when you run a Lotus 123 macro. For
more information, see "Microsoft Excel Transition Options"
earlier in this chapter.
Adjusting Screen Size Before Running
Lotus 123 Macros
For best visual results, you should maximize the Excel
window, as well as the active document window. Note that the {PGUP},
{PGDN}, {BIGRIGHT}, and {BIGLEFT} commands work with the current
page size, not with the 20row page size that is standard
in Lotus 123.
Calling Microsoft Excel Procedures
from Lotus 123 Macros
Two Excel macro commands allow you to call or branch
to Visual Basic procedures or Excel 4.0 XLM macros
written in Excel from within a Lotus 123
macro. These functions are {XLCALL} and {XLBRANCH}, and take the
following form:
{XLCALL xl_name}
{XLBRANCH xl_name}
where xl_name is the procedure name.
One use of these commands might be to replace calls to Lotus 123
addins with calls to Excel addins or Visual
Basic procedures. Using XLCALL, you can run an Excel
procedure, after which control returns to the Lotus 123
macro. Using XLBRANCH, Excel takes over control and
does not return to the original Lotus 123 macro.
With these two commands, you can rebuild part or all of complex
Lotus 123 macro statements with more concise
Visual Basic code; it is not necessary to rewrite the entire macro.
Erasing the Active Worksheet and Starting
a New One
Excel files are called workbooks, and each workbook
can contain multiple worksheets. You can have more than one workbook
open at once, so you can keep the active workbook open while starting
a new one. The following procedure corresponds to the Lotus 123
Worksheet Erase command, which removes the active worksheet
from memory but not from your disk, so you can start a new one.
To
remove the active workbook from memory
- On the File menu, click Close.
If you made unsaved changes to the active worksheet,
a dialog box appears asking if you want to save your changes.
- To close the active workbook without saving changes, click No.
- On the File menu, click New to open a new workbook.
Using Lotus 123 Charts in Microsoft Excel
Excel and Lotus 123 format charts differently.
This section discusses Excel equivalents for Lotus 123
chart format commands.
Lotus 123 Graph Options
Format Command Equivalents
The Lotus 123 Graph Options Format commands apply
to line and xy (scatter) charts only.
To
format a line or xy (scatter) chart in Excel
- Select a data series.
- On
the Format menu, click Data Series, and then click
the Patterns tab.
- Under Line,
select a line style, color, and weight.
– or –
If you do not want any lines, click None.
- Under Marker, select a marker style, foreground color, and background color.
– or –
If you do not want any markers, click None.
Lotus 123 Graph Options Grid Command Equivalents
The Lotus 123 Graph Options Grid commands apply
to all graph types with axes.
To
add and delete gridlines in a chart in Excel
- Rightclick a blank
area of the chart, and then click Chart Options on the
shortcut menu.
- Click the Gridlines tab,
and select the options you want.
Lotus 123 Graph Type Command Equivalents
In Excel, you change the chart type after you create
the chart. Click the Chart Type command (Chart menu)
when a chart is active. This command also allows you to select
additional chart types, such as threedimensional charts.
Lotus 123 Graph View
Command Equivalents
After you create a chart in Excel, it remains visible
on a worksheet as an embedded chart, or as a separate chart sheet
in the workbook. Therefore, the procedure for creating a chart
in Excel is the closest equivalent to the Lotus 123
Graph View command.
Lotus 123 Graph Options
Color Command Equivalents
There is no direct equivalent in Excel for the Lotus 123
Graph Options Color command. However, you can change the
color of individual chart items.
To
change the color of a chart item in Excel
- Click the chart item you
want to format.
- On the Format menu, point
to Selected Chart Item.
- In
the dialog box that appears, select the options you want.
Note The
name of the Selected Chart Item command on the Format menu changes based on the chart item you select. For example, if you select
a chart axis, the command Axis appears on the Format menu.
Lotus 123 Graph Options
Scale Command Equivalents
The following sections describe Excel equivalents for
Lotus 123 Scale commands.
Auto
Excel creates the scale automatically. If you designate
any aspect of the scale as manual, you can return it to automatic.
To
set an axis scale to automatic in Excel
- Click the xaxis (category)
or yaxis (value).
- On the Format
menu, click Selected Axis, and then click the Scale
tab.
Except for xy scatter charts, the options on the Scale
tab are different for the xaxis and the yaxis.
- Select
the Auto check box for any option you want to return to
automatic.
Manual, Lower, and Upper
You can control the chart scale manually.
To
control a chart scale manually in Excel
- Click the xaxis (category)
or yaxis (value).
- On the Format menu, click Selected Axis, and then click the Scale tab.
Except for xy scatter charts, the options on the Scale
tab are different for the xaxis and the yaxis.
- Change
the options you want.
Format
You can change the number format on the chart scale.
To
change the number format on a chart scale in Excel
- In the chart, click the
yaxis (value).
You can change the xaxis number format only
in xy scatter charts.
- On the Format menu,
click Selected Axis, and then click the Number tab.
- Select
the number format you want to use on the chart.
Indicator
You can display chart scale indicators.
To
display or hide chart scale indicators in Excel
- Select the chart.
- On
the Chart menu, click Chart Options, and then click
the Axes tab.
- Select or clear the Value
(X) Axis or Value (Y) Axis check box.
Working with Printers
This section describes differences in printer setup and printing
procedures between Lotus 123 and Excel.
Lotus 123 Worksheet
Global Default Printer Command Equivalents
Excel has equivalent features for most Lotus 123
global printer commands.
| This Lotus 123 global printer command
| Corresponds to this Excel feature or action
|
| Interface
| Setting up a printer and port
|
| AutoLF
| None; handled by printer driver
|
| Left
| Page Setup (File menu)
|
| Right
| Page Setup (File menu)
|
| Top |
Page Setup (File menu)
|
| Bottom
| Page Setup (File menu)
|
| PgLength
| Page Setup (File menu)
|
| Wait
| None; handled by printer driver
|
| Setup
| None; handled by printer driver
|
| Name
| Page Setup (File menu)
|
| Quit
| ESC key |
Lotus 123 Line Print
Command Equivalents
There is no command in Excel that is equivalent to the
Lotus 123 Line Print command. Instead,
use the LINE.PRINT macro function.
Lotus 123 Print Printer
Command Equivalents
Excel has equivalent features for most Lotus 123
print printer commands.
| This Lotus 123 printer command
| Corresponds to this Excel feature or action
|
| Range
| Set Print Area (File menu, Print Area submenu).
|
| Page
| None. |
| Options
|
|
| Page Setup (File menu).
|
| Page Setup (File menu).
|
| Page Setup (File menu).
|
| On the File menu click Page Setup, and then click the Sheet tab. Under Print titles, enter the row and column references you want to appear.
|
| Select the cells; on the Format menu, click Cells, and then click the Font tab.
|
|
| On the File menu click Page Setup, and then click the Page tab. In the Paper size box, select the page size you want.
|
| Worksheet is printed as displayed. To display values or formulas, click the View tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu). To add or remove headers and footers, click Page Setup (File menu). To add or remove page breaks, click Page Break or Remove Page Break (Insert menu).
|
| ESC key.
|
| Clear
|
|
| Reset individual settings.
|
| On the Insert menu, point to Name, and then click Define and delete Print_Area.
|
| On the Insert menu, click Name, and then click Define and delete Print_Titles.
|
| On the File menu, click Page Setup to reset margins. Page length and setup string are handled by the printer driver.
|
| Align
| None. |
| Go |
Print (File menu).
|
| Quit
| ESC key. |
WK3 Functions Without Excel
Equivalents
A number of Lotus 123 WK3 functions —
nonaggregate functions that use threedimensional references —
do not properly convert to Excel. A nonaggregate
function is one that is not commonly used with a range of values.
An aggregate function is one that is always
used with a range of values, such as SUM, AVERAGE, MIN, and MAX.
For example, the nonaggregate function @INDEX does not convert
if it uses references that encompass more than one ply of a threedimensional
worksheet. (It converts properly if no threedimensional
reference is used.) WK3 functions are unsupported when they include
a threedimensional argument; if a normal argument is used,
they may work. These unsupported functions are shown in the following
list.
| Nonaggregate functions with threedimensional references
|
| @CELL | @IRR
| @ROWS |
| @COLS | @ISRANGE
| @S |
| @COORD | @N
| @SHEETS |
| @INDEX | @NPV
| @SUMPRODUCT |
In addition, Excel cannot convert formulas with more
than one table argument using @DSUM, @DAVG, @DMIN, @DMAX, @DSTD,
@DVAR, @DSTDS, or @DVARS. Other functions that present conversion
problems include @DQUERY when using the DataLens addin,
and @CELLPOINTER when using the sheet argument.
|
Errors Caused by Special Characters
Because Excel does not allow all of the special characters
in names that Lotus 123 does, names that contain
different special characters but are otherwise identical are converted
to an identical name, resulting in an error.
Besides letters and numbers, Excel allows only underscore
and backslash ( \ ) characters to be used in names.
Excel converts any invalid characters in names to the
underscore character ( _ ) when reading Lotus 123
worksheets. Excel notifies the user that it cannot read
the record if two or more defined names on the worksheet contain
special characters that cause them to resolve to the same name.
For example, if you have defined the names TOTAL$ and TOTAL# on
a Lotus 123 worksheet and you open the worksheet
in Excel, the first defined name TOTAL$ is converted
to TOTAL_, and the second defined name TOTAL# is
lost. You can work around this by checking for invalid characters
in your Lotus 123 worksheets before converting
them to Excel.
|
Lotus 123 Releases
4 and 5 Features Without Microsoft Excel Equivalents
Lotus 123 features without direct equivalents
in Excel are not imported. These include the following:
- Range versions created with
the Lotus 123 Version Manager
No direct equivalent exists in Excel for
range versions created with the Lotus 123 Version
Manager. Excel imports only the data from the version
that is currently displayed (the last time the file was saved).
However, you can use the Excel Scenario Manager to create,
store, and retrieve whatif assumptions for multiple sets
of up to 32 changing cells.
- Database records
in Lotus 123 Release 4
Because these are not compatible with either ODBC
or Microsoft Query, they are not imported.
- Embedded
OLE objects
These include the Lotus Maps objects from Release
5.
- Rotated text or drawing
These objects are imported, but they are displayed
with normal horizontal alignment.
- Gradient fills
The cell or object is formatted using only the primary
color from the fill.
Sharing Documents with Lotus 123
If your workgroup is upgrading gradually to Excel, some
users may have to share documents with users of Lotus 123.
To
save an Excel workbook in Lotus 123
format
- On the File menu,
click Save As.
- In the File name (Windows ) or Save as (Macintosh)
box, enter the name for the converted workbook.
- In
the Save as type (Windows) or Save file as type (Macintosh) box, click the Lotus 123
version in which you want to save the file.
If the format you select does not support multisheet
workbooks, Excel prompts you to save each sheet individually.
Excel can save data in the following Lotus 123
file formats.
| In Excel, save data in this file format
| To share data with this Lotus 123 release
|
| WKS (saves the active sheet only)
| 1, 1A |
| WK1, ALL (saves the active sheet only)
| 2.0, 2.01, 2.2 |
| WK1, FMT (saves the active sheet only)
| 2.3, 2.4 |
| WK3 | 3.0
|
| WK3, FM3 | 3.1, 3.1+, 123/W, R1.1
|
| WK4, WT4 | 4.0, 5.0
|
Tip End
users can specify the default format in which Excel
saves new documents.
To
specify the default format in which to save documents
- On the Tools menu,
click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.
- In
the Save Excel files as (Windows) or Default save as type (Macintosh) box, click the file format you
want.
The next time you save a document that is not already
saved in Excel format, you are prompted to save it in
this format.
For more information about selecting a default format in which
to save documents, see "Specifying the Default Format in Which to Save Office Documents" in Chapter 22, "Supporting
Multiple Versions of Microsoft Office."
| | Tip In
Windows 95 and Windows NT Workstation 4.0, you
can use a system policy to define a default value for the Save
as type option in the Save As dialog box (File
menu) for all Excel users in your workgroup. In the
System Policy Editor, set the following policy:
|
If you use Lotus 123 to edit a Lotus 123
worksheet linked to an Excel worksheet, the linked cells
are updated when you open the Excel workbook.
You can use the Links command (Edit menu) to open
the Lotus 123 worksheet that contains the source
data for the Excel worksheet you are editing. You can
also redirect links to refer to another worksheet.
|
Consolidating Microsoft Excel and Lotus 123
Worksheets
When you switch to Excel, you might need to include
data from Lotus 123 worksheets in summary reports
created in Excel. In Excel you can include
details from Lotus 123 worksheets. The addresses
of these data sources are stored in the worksheet, and you can
call them whenever an update is needed using the Consolidate
command (Data menu). You can also construct dynamic links
to the source data that update the figures in the consolidation
worksheet automatically.
For example, suppose you have a departmental budget that consists
of monthly data from several different sources throughout the
department. The source sheets can be a variety of Excel
and Lotus 123 files. Each source might have a
set of daily, weekly, or monthly worksheets itemizing actual and
projected budgets. Each source can use consolidation to collect
specific data from the set of worksheets in order to create a
summary worksheet. The person responsible for the overall budget
can use consolidation formulas to collect this summary information
from each source across the network and, in turn, create a master
departmental summary worksheet.
|
Using Microsoft Excel References in
Lotus 123
When converting Excel files to Lotus 123
format, the following difficulties arise because Lotus 123
has no equivalent functionality:
- Excel cannot export
formulas containing references to nonadjacent selections, unless
they are arguments to translatable functions.
- Functions
that produce references as a result are not exported to WKS format.
- The
Excel intersection (blank space) and union (comma) operators
are not exported to Lotus 123.
In these cases, Excel substitutes the value of the formula
for the formula itself.
When you save an Excel worksheet in a Lotus 123
file format, any references to rows beyond 2048 (for WKS file
format) or 8192 (for WK1 and WK3 file formats) wrap around the
end of the row. For example, when you save a reference to cell
A8193 in WK1 format, the reference changes to A1 in
Excel.
Lotus 123 Releases 1a and 2.01 do not support
external references (file linking) to other worksheets, so any
Excel formulas containing external references are not
exported to WKS or WK1 file formats. In these cases,
Excel substitutes the value of the formula for the formula itself.
Microsoft Excel Functions Without
Lotus 123 Equivalents
Many Excel functions have equivalent Lotus 123
functions. However, the following Excel functions have
no equivalents in Lotus 123 Release 3.1 or earlier,
or Lotus 123/W Release 1.0, and generate an error
message in Lotus 123.
Note Excel also provides many addin functions and statistical
functions in the Analysis ToolPak that don't have Lotus 123
equivalents; these are not included in this list.
| Excel functions without equivalents in Lotus 123 3.1 or earlier
|
| AREAS | MATCH
|
| DOLLAR | MDETERM
|
| DPRODUCT | MINVERSE
|
| FACT | MIRR
|
| FREQUENCY | MMULT
|
| GROWTH | PPMT
|
| INT | PRODUCT
|
| IPMT | SEARCH
|
| ISBLANK | SUBSTITUTE
|
| ISERROR | TEXT
|
| ISLOGICAL | TRANSPOSE
|
| LINEST | TREND
|
| LOGEST | TYPE
|
| LOOKUP | WEEKDAY
|
Using Lotus 123 Help
In Excel, the Lotus 123
Help feature allows you to use familiar Lotus 123
keys and commands while you learn how to use Excel.
For example, you can choose a Lotus 123 key or
command and have Excel display stepbystep
instructions for the corresponding action in Excel.
Or Excel can demonstrate and actually carry out the
corresponding action.
Note The
Lotus 123 Help feature also provides Help topics
for users switching from Lotus 123 for MSDOS.
You can press a Lotus 123 key and have
Excel either automatically demonstrate the corresponding feature
or list the steps you need to perform it in Excel. If
you are not sure which Lotus 123 key to press,
you can choose from a list of Lotus 123 commands.
To
use Lotus 123 Help
- On the Tools menu,
click Options, and then click the Transition tab.
- Under Settings, click Lotus 123 Help.
Next you need to indicate whether you want to see stepbystep
instructions or demonstrations when you press Lotus 123
keys.
To
see stepbystep instructions or demonstrations
- On the Help menu,
click Lotus 123 Help.
- Under
Help options, click Instructions to see stepbystep
instructions.
– or –
Click Demo to see demonstrations.
When you're working in an Excel document and you press
the SLASH key ( / ), or the key
you specified on the Transition tab in the Options
dialog box (Tools menu), the Help for Lotus 123
Users dialog box appears. Select the Lotus 123
command you want, and then click either Instructions or
Demo.
The following sections describe options in the Help for Lotus 123
Users dialog box.
Menu
The Menu box in the Help for Lotus 123
Users dialog box displays a list of Lotus 123
menu items. Type the Lotus 123 keystrokes you
would use to choose a command. Depending on the type of help you
select under Help options, Excel either begins
a demonstration or displays instructions for carrying out the
equivalent actions in Excel. For multilevel Lotus 123
menus, Excel displays the submenu at the bottom of the
dialog box. To move to the next menu level, select the menu item
in the Menu box, and then press ENTER
or press the first letter of the menu item.
Help Options
In the Help options box in the Help for Lotus 123
Users dialog box you can choose either to display a text box
containing the Excel equivalent procedure for carrying
out a Lotus 123 command (the Instructions
option), or to watch Excel demonstrate the equivalent
steps for you (the Demo option).
For commands requiring additional information, such as cell references,
you are prompted for the necessary information at the top of the
Excel window before the demonstration starts.
The Faster and Slower buttons allow you to choose
from among five demonstration speeds, with 5 being the fastest
and 1 the slowest. The current speed is displayed in the box to
the right of the two buttons.
- Excel cannot open
Quattro Pro files that are password protected. Before you open
the file in Excel, make sure you have removed any password
on the file.
- Excel cannot run macros
created in Quattro Pro for Windows.
- Excel cannot directly open a Quattro Pro for Windows 6.0 file.
Save Quattro Pro for Windows 6.0 files in an earlier Quattro Pro
format or in another format, such as Lotus 123.
- External
links in Quattro Pro for Windows version 6.0 files do not
convert when you open the file in Excel.
- Quattro
Pro for Windows graphs are stored in a different manner than
Excel stores its charts, and are not imported.
- The
Excel Scenario Manager is not directly equivalent to
the Quattro Pro for Windows Scenario Manager. Therefore,
Excel imports only the data from the scenario that is currently
displayed (the last time the file was saved). You can use the
Excel Scenario Manager to create, store, and retrieve
whatif assumptions for multiple sets of up to 32 changing
cells.
- Quattro Pro for Windows Hot Links to
external database tables or to the Data Modeling Desktop are not
compatible with ODBC, Query, or Excel PivotTables,
so they are not imported.
- Embedded OLE objects
are not imported.
- For gradient fills, the object
is formatted using the primary color from the fill.
- Click Start menu, point to Programs, and then click MSDOS Prompt.
To activate MSDOS on the Windows NT 3.51 operating system
- In the Main program group, doubleclick the Command Prompt icon.
Multiplan Run Report Command Equivalents
The following table describes the equivalent Excel features
or actions for Multiplan options.
| This Multiplan option
| Corresponds to this Excel feature or action
|
| Crossref | No direct equivalent. You can trace formula precedents and dependents using the Auditing submenu commands (Tools menu).
|
| Names | To get a list of defined names in a worksheet, select an empty area of the worksheet and click Name (Insert menu); then click Paste and click the Paste List button.
|
| Summary | No direct equivalent. To identify conditions in a worksheet that are likely to cause errors, use the Auditing submenu commands (Tools menu).
|
Multiplan Transfer Clear Window Command Equivalents
There is no direct equivalent to the Multiplan Transfer Clear
Window command in Excel. Instead, do one of the
following:
- To create a new workbook,
choose New (File menu).
- To close
the active workbook, choose Close (File menu).
- To
clear cell contents from the active worksheet, select the cells
you want to clear, and then press DELETE.
(To select an entire worksheet, click the button located at the
intersection of the row headers and column headers.)
