123 to Excel

Conversion issues

Excel opens and saves the following Lotus 1񩻫 file formats.

This Lotus 1񩻫 release

Saves data in this file format

1, 1A

WKS

2.0, 2.01, 2.2

WK1, ALL

2.3, 2.4

WK1, FMT

3.0

WK3

3.1, 3.1+, 1񩻫/W, R1.1

WK3, FM3

4.0, 5.0

WK4

 

I can抰 open my Lotus file in Excel

Excel will open files created in Lotus 1񩻫 versions 2.x5.x. Most data and formatting created in Lotus 1񩻫 are fully supported by Excel. The latest versions of Lotus 1-2-3 use a file type (.123) not currently supported by Excel. You must save these as .WK4 files before you try and open them in Excel.

Auditing Converted Worksheets

Audits conducted by the industry on corporate MS璂OS璪ased 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 1񩻫 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 1񩻫 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 1񩻫 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.".

Opening and Saving Lotus 1񩻫 Worksheets in Microsoft Excel

The majority of your Lotus 1񩻫 worksheets can be converted to Excel format by opening them and then saving them in Excel.

To open a Lotus 1񩻫 worksheet in Excel

  1. On the File menu, click Open.

  2. In the Files of type (Windows) or List files of type (Macintosh) box, click Lotus 1񩻫 Files.

  3. Below the Look in (Windows) or Select a document (Macintosh) box, select the name of the worksheet.

  4. Click Open.

Excel converts the Lotus 1񩻫 worksheet and opens it.

How do I know if my work has been successfully converted?

We recommend that you print the key pages or reports from your Lotus files before you convert them, remembering to ensure they are calculated (function key F9) first. After you have converted the files, print the same pages and reports from Excel and make a tick-check comparison between them. If the results are the same, your conversion has been successful.

My results are not the same after conversion.

If you have formulas in cells that cannot be converted, Excel notifies you that it cannot read the record, and then displays another dialog box asking you whether to continue alerting you each time a cell does not convert. Formulas that do not convert are discarded, but the result of the formula is preserved and Excel attaches a comment to the cell, containing the message "Formula failed to convert."

What is the file conversion wizard?

If you want to convert multiple worksheets to or from Lotus 1񩻫 format, you can also use the File Conversion Wizard add璱n.

I抳e lost all the formatting from my converted file Lotus 1񩻫 files

When formatting is applied to a Lotus 1񩻫 WK1 or WK3 worksheet, a separate file is created and saved along with the worksheet. Check that the formatting file was in the same directory as the .WK* file.

How do I search for cells containing formulas that did not convert correctly

  1. On the Edit menu, click Find.

  2. In the Look in box, click Comments.

  3. In the Find what box, type formula failed to convert

  4. Click Find Next.

    The first cell containing the text you entered is selected. A message appears if the text cannot be found.

  5. 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.

How do I find all cells with cell comments

  1. On the Edit menu, click Go To.

  2. 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.

How do I print cell comments

  1. On the File menu, click Page Setup, and then click the Sheet tab.

  2. 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 1񩻫璼tyle 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.

Translating Lotus 1񩻫 Nested Formulas

Perhaps the most common reason for the "Cannot read record" message when converting Lotus 1񩻫 formulas to Excel occurs when a formula in your Lotus 1񩻫 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 1񩻫 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 1񩻫:

@VLOOKUP(a1,table,1)

where table is a range name that refers to the following two璫olumn 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.

Do my old macros work in Excel?

Excel includes the Macro Interpreter for Lotus 1񩻫 users, which provides macro conversion support.

 

Terminology and Equivalents

Microsoft Excel Terms for Lotus 1񩻫 Users

The following table lists Lotus 1񩻫 terms and their Excel counterparts. The Excel term is not necessarily an exact equivalent of the Lotus 1񩻫 term, but rather a term you can look up in online Help for more information.

 

This Lotus 1񩻫 term

Corresponds to this Excel term or concept

@Function

Function

Address

Reference

Anchor cell

Selecting a range of cells

Border

Row and column headings

CALC indicator

Status bar

Cell pointer

Active cell

Column labels

Column headings

Command prompt

Dialog box

Control panel

Menu bar, formula bar, status bar

Copy

Copy and paste

Crosshatching

Chart patterns

Current cell

Active cell

Current worksheet

Active worksheet or chart or macro sheet

Data labels

Data marker labels

Data range

Data series

Data table 1

One璱nput table

Data table 2

Two璱nput table

Date format

Number format

Erase

Clear

Formula criteria

Computed criteria

Global

Workspace

Graph

Chart

Graph labels

Chart text

Graph titles

Chart titles

Highlight

Select or selection

Indicator

Status bar

Input range

Database range

Label

Text

Label璸refix

Alignment

Label/matching criteria

Comparison criteria

Logical 0

FALSE

Logical 1

TRUE

Menu pointer

Menu selection

Mode indicator

Status bar

Move

Cut and paste

Number/matching criteria

Comparison criteria

Numeric format

Number format

Output range

Extract range

Picture file

Chart document

Pointer movement keys

Arrow keys

Print range

Print area

PrintGraph

Printing a chart

Prompt

Dialog box

Protected cell

Locked/protected cell

Range highlight

Selected range

Repeating label

Fill alignment

Retrieve a file

Open a file

Row numbers

Row headings

Stacked bar graph

Column chart, bar chart

Status indicator, status line

Status bar

String

Text

Target cell

Dependent cell

Target file

Dependent document

Time format

Number format

Titles

Split worksheet window with frozen panes

Translate utility

Open and Save As (File menu)

Value

Number

Window

Multiple windows, pane

 

Microsoft Excel Equivalents for Lotus 1񩻫 Commands

The following table lists frequently used Lotus 1񩻫 commands and the equivalent commands in Excel.

 

This Lotus 1񩻫 command

Corresponds to this Excel command or option

/c

Copy and Paste (Edit menu)

/fd

Open (File menu)

/few

Delete on the document shortcut menu in the Open dialog box (File menu)

/fr

Open (File menu)

/fs

Save As (File menu)

/gv

Open (File menu, when the chart is in a separate file)

/m

Cut and Paste (Edit menu)

/ppg

Print (File menu)

/ppr

Set Print Area (File menu, Print Area submenu)

/qy

Exit (File menu)

/re

Clear (Edit menu)

/rf

Number tab in the Cells dialog box (Format menu)

/rfc

Number tab in the Cells dialog box (Format menu)

/rfp

Number tab in the Cells dialog box (Format menu)

/rnl

Create (Insert menu, Name submenu)

/rnc

Define (Insert menu, Name submenu)

/wcs

Width (Format menu, Column submenu)

/wdc

Delete (Edit menu)

/wdr

Delete (Edit menu)

/wey

Close and New (File menu)

/wic

Columns (Insert menu)

/wir

Rows (Insert menu)

/wtc

Unfreeze Panes (Window menu)

/wth

Freeze Panes (Window menu)

/wtv

Freeze Panes (Window menu)

 

 

The Navigation Keys are different

The following table compares navigation key assignments in Lotus 1񩻫 and their equivalents in Excel.

 

This Lotus 1񩻫 navigation key

Corresponds to this Excel navigation key

UP ARROW, DOWN ARROW

UP ARROW, DOWN ARROW

LEFT ARROW, RIGHT ARROW

LEFT ARROW, RIGHT ARROW

END, UP ARROW

CTRL+UP ARROW or END+UP ARROW

END, DOWN ARROW

CTRL+DOWN ARROW or END+DOWN ARROW

END, LEFT ARROW

CTRL+LEFT ARROW or END+LEFT ARROW

END, RIGHT ARROW

CTRL+RIGHT ARROW or END+RIGHT ARROW

HOME

CTRL+HOME

TAB

ALT+PAGE DOWN

SHIFT+TAB

ALT+PAGE UP

PAGE UP

PAGE UP

PAGE DOWN

PAGE DOWN

 

Is there an equivalent for the Lotus 1񩻫 System Command (Windows only)

There is no direct equivalent for the Lotus 1񩻫 System command in Excel, but you can activate the command prompt from the Start menu.

How do I use three dimensional formulas?

If you store a group of worksheets with identical layouts, such as monthly reports, in the same workbook, you can use three-dimensional formulas to consolidate data into summary worksheets. These three-dimensional 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 three-dimensional 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.

Where can I get help on 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.

What is the order of mathematical operators?

 

Lotus 1񩻫 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 1񩻫, 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 1񩻫, and 16 in Excel. To change this, use parentheses to force the preferred order of evaluation in Excel. For example:

=(2^4)

Can I use my Lotus 1-2-3 macros in Excel?

Most Lotus 1񩻫 users have invested time over the years building macros. Excel includes the Macro Interpreter for Lotus 1񩻫 Users, which provides limited macro conversion support.

You can run large Lotus 1񩻫 macro applications, including custom menus, without modification in Excel using the Macro Interpreter. However, it will not run any macro command added after Lotus 1񩻫 Release 2.01.

We strongly recommend that you convert any Lotus 1-2-3 macros that you use into Excel抯 VBA at the earliest opportunity. We have a team of specialists available to help you convert your macros.

Running Macros Created in Lotus 1񩻫 Release 2

Excel can run macros that contain any Lotus 1񩻫 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 1񩻫 Release 2.2 Macro Library Files

If you have Lotus 1񩻫 macros in macro libraries (macros in Lotus 1񩻫 Release 2.2 MLB file format), you can convert them to Excel.

To convert Lotus 1񩻫 MLB files

  1. In Lotus 1񩻫, load the file by attaching the add璱n.

  2. In the Lotus Macro Library Manager, copy the library commands to a worksheet using the Edit command.

  3. Save the worksheet in Lotus 1񩻫 WK1 format.

  4. Open the Lotus 1񩻫 worksheet in Excel.

Will my autoexec macros run?

If you have a Lotus 1񩻫 autoexec macro (named \0) on your worksheet, the macro runs automatically when you open the worksheet in Excel.

Can I stop the autoexec macro from running?

  1. On the File menu, click Open.

  2. Under the Look in box, select the workbook, and then hold down SHIFT and click OK.

I have a \0 Lotus macro, and I抳e now created an Excel Auto_Open macro

If you have both an Excel macro named Auto_Open that refers to a macro sheet and a Lotus 1񩻫 \0 macro on the same worksheet, the Auto_Open macro runs first, and then the \0 macro runs.

Can I use my Lotus 1-2-3 add-ins in Excel

No. You must be sure to remove any occurrence of keystrokes or command names that attach, start, or use a Lotus 1񩻫 add璱n, such as the Allways add璱n and its menu structure. For example, remove statements such as /a and {app1}.

Lotus 1-2-3 macros that end in a menu

When you run a Lotus 1񩻫 macro in Excel, the Lotus 1񩻫 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.

Substituting Standard Microsoft Excel Features for Lotus 1񩻫 Macros

Many Lotus 1񩻫 macros do not need to be converted. These macros, which aid the user with formatting or printing from Lotus 1񩻫, are replaced by standard features in Excel. Some of the most common Lotus 1񩻫 macros and the Excel features that replace them are described in the following table.

 

This Lotus 1񩻫 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 YYYY,MM,DD, and then re璭nter 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 double璫lick for best fit

Adjust multiple璫olumn widths simultaneously

Select multiple columns, and then drag the right border of a column heading or double璫lick 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 1񩻫 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

 

 

 

Using Lotus 1񩻫 Charts in Microsoft Excel

Introduction

Excel and Lotus 1񩻫 format charts differently. This section discusses Excel equivalents for Lotus 1񩻫 chart format commands.

Lotus 1񩻫 Graph Options Format Command Equivalents

The Lotus 1񩻫 Graph Options Format commands apply to line and xy (scatter) charts only.

How do I format a line or xy (scatter) chart in Excel?

  1. Select a data series.

  2. On the Format menu, click Data Series, and then click the Patterns tab.

  3. Under Line, select a line style, color, and weight.

    or

    If you do not want any lines, click None.

  4. Under Marker, select a marker style, foreground color, and background color.

or

If you do not want any markers, click None.

Lotus 1񩻫 Graph Options Grid Command Equivalents

The Lotus 1񩻫 Graph Options Grid commands apply to all graph types with axes.

 

How do I add and delete gridlines in a chart in Excel?

  1. Right璫lick a blank area of the chart, and then click Chart Options on the shortcut menu.

  2. Click the Gridlines tab, and select the options you want.

Lotus 1񩻫 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 three璬imensional charts.

Lotus 1񩻫 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 1񩻫 Graph View command.

Lotus 1񩻫 Graph Options Color Command Equivalents

There is no direct equivalent in Excel for the Lotus 1񩻫 Graph Options Color command. However, you can change the color of individual chart items.

How do I change the color of a chart item in Excel?

  1. Click the chart item you want to format.

  2. On the Format menu, point to Selected Chart Item.

  3. 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 1񩻫 Graph Options Scale Command Equivalents

The following sections describe Excel equivalents for Lotus 1񩻫 Scale commands.

Auto

Excel creates the scale automatically. If you designate any aspect of the scale as manual, you can return it to automatic.

How do I set an axis scale to automatic in Excel?

  1. Click the x璦xis (category) or y璦xis (value).

  2. 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 x璦xis and the y璦xis.

  3. 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.

How do I control a chart scale manually in Excel?

  1. Click the x璦xis (category) or y璦xis (value).

  2. 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 x璦xis and the y璦xis.

  3. Change the options you want.

Format

You can change the number format on the chart scale.

How do I change the number format on a chart scale in Excel?

  1. In the chart, click the y璦xis (value).

    You can change the x璦xis number format only in xy scatter charts.

  2. On the Format menu, click Selected Axis, and then click the Number tab.

  3. Select the number format you want to use on the chart.

Indicator

You can display chart scale indicators.

 

How do I display or hide chart scale indicators in Excel?

  1. Select the chart.

  2. On the Chart menu, click Chart Options, and then click the Axes tab.

  3. Select or clear the Value (X) Axis or Value (Y) Axis check box

.

 

 

Printing

What are the Excel Equivalents for Lotus 1񩻫 Worksheet Global Default Printer Commands?

 

This Lotus 1񩻫 global printer command

Corresponds to this Excel feature or action

Interface

Setting up a printer and port

Auto璍F

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)

Pg璍ength

Page Setup (File menu)

Wait

None; handled by printer driver

Setup

None; handled by printer driver

Name

Page Setup (File menu)

Quit

ESC key

 

What is the Excel Equivalent for the Lotus 1񩻫 Line Print Command?

There is no command in Excel that is equivalent to the Lotus 1񩻫 Line Print command. Instead, use the LINE.PRINT macro function.

What are Excel抯 equivalent features for typical Lotus 1񩻫 print printer commands?

 

This Lotus 1񩻫 printer command

Corresponds to this Excel feature or action

Range

Set Print Area (File menu, Print Area submenu).

Page

None.

Options


 

Header

Page Setup (File menu).

Footer

Page Setup (File menu).

Margins

Page Setup (File menu).

Borders

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.

Setup

Select the cells; on the Format menu, click Cells, and then click the Font tab.

Pg璍ength

On the File menu click Page Setup, and then click the Page tab. In the Paper size box, select the page size you want.

Other

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).

Quit

ESC key.

Clear


 

All

Reset individual settings.

Range

On the Insert menu, point to Name, and then click Define and delete Print_Area.

Borders

On the Insert menu, click Name, and then click Define and delete Print_Titles.

Format

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.

 

 

Which WK3 functions do not have Excel equivalents?

A number of Lotus 1񩻫 WK3 functions nonaggregate functions that use three-dimensional 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 three-dimensional worksheet. (It converts properly if no three-dimensional reference is used.) WK3 functions are unsupported when they include a three-dimensional argument; if a normal argument is used, they may work. These unsupported functions are shown in the following list.

 

Nonaggregate functions with three璬imensional references

@CELL

@IRR

@ROWS

@COLS

@ISRANGE

@S

@COORD

@N

@SHEETS

@INDEX

@NPV

@SUMPRODUCT

 

pppp

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 add璱n, and @CELLPOINTER when using the sheet argument.

Why have some of my range-names changed?

Because Excel does not allow all of the special characters in names that Lotus 1񩻫 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 1񩻫 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 1񩻫 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 1񩻫 worksheets before converting them to Excel.

Do Lotus 1񩻫 Releases 4 and 5 have features without Microsoft Excel equivalents?

Lotus 1񩻫 features without direct equivalents in Excel are not imported. These include the following:

  • Range versions created with the Lotus 1񩻫 Version Manager

  • No direct equivalent exists in Excel for range versions created with the Lotus 1񩻫 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 what璱f assumptions for multiple sets of up to 32 changing cells.

  • Database records in Lotus 1񩻫 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 colour from the fill.

Does Excel have functions that were not available in Lotus 1񩻫?

The following Excel functions have no equivalents in Lotus 1񩻫 Release 3.1 or earlier, or Lotus 1񩻫/W Release 1.0.

Note Excel also provides many add璱n functions and statistical functions in the Analysis ToolPak that don't have Lotus 1񩻫 equivalents; these are not included in this list.

 

Excel functions without equivalents in Lotus 1񩻫 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

 

 

 

Excel's help for Lotus users

Tell me about the Lotus 1񩻫 Help feature in Excel.

In Excel, the Lotus 1񩻫 Help feature allows you to use familiar Lotus 1񩻫 keys and commands while you learn how to use Excel. For example, you can choose a Lotus 1񩻫 key or command and have Excel display step璪y璼tep instructions for the corresponding action in Excel. Or Excel can demonstrate and actually carry out the corresponding action.

Note The Lotus 1񩻫 Help feature also provides Help topics for users switching from Lotus 1񩻫 for MS璂OS.

You can press a Lotus 1񩻫 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 1񩻫 key to press, you can choose from a list of Lotus 1񩻫 commands.

To use Lotus 1񩻫 Help

1. On the Tools menu, click Options, and then click the Transition tab.

2. Under Settings, click Lotus 1񩻫 Help.

Next you need to indicate whether you want to see step璪y璼tep instructions or demonstrations when you press Lotus 1񩻫 keys.

To see step璪y璼tep instructions or demonstrations

1. On the Help menu, click Lotus 1񩻫 Help.

2. Under Help options, click Instructions to see step璪y璼tep 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 1񩻫 Users dialog box appears. Select the Lotus 1񩻫 command you want, and then click either Instructions or Demo.

The following sections describe options in the Help for Lotus 1񩻫 Users dialog box.

Menu

The Menu box in the Help for Lotus 1񩻫 Users dialog box displays a list of Lotus 1񩻫 menu items. Type the Lotus 1񩻫 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 1񩻫 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 1񩻫 Users dialog box you can choose either to display a text box containing the Excel equivalent procedure for carrying out a Lotus 1񩻫 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 and the Year 2000

Overview

 

Microsoft Excel 97 interprets "00" to "29" as "2000" to "2029",

 

and the shortcut "30" will resolve to "1930"

 

 

 

The full range of dates Excel 97 can handle

 

Earliest date not recognised

31 December 1899

First recognised date in 4-digit year format

1 January 1900

First recognised date in 2-digit year format

1 January 2000

Last recognised 2-digit year format

31 December 2029

Last recognised 4-digit year format

31 December 9999

 

How can I tell if my spreadsheets are millennium compliant?

If your work does not contain any date formulas, or formulas that act on date entries, or formulas that give dates as results, then you do not have a problem.

If your work does contain some of the above, you need to ensure that the year portion of any date contains four characters, for example, 1999, not just 99.

What formulas could present millennium compliance problems?

This is a list of Excel抯 date and time formulas. It is how you use them that can generate problems.

 

DATE

DATEVALUE

DAY

DAYS360

EDATE

EOMONTH

HOUR

MINUTE

MONTH

NETWORKDAYS

NOW

SECOND

TIME

TIMEVALUE

TODAY

WEEKDAY

WORKDAY

YEAR

YEARFRAC

 

Which formulas rely on the system clock?

NOW and TODAY

 

Other

Can Excel sort on more than three fields?

First, sort on the minor fields and progressively work towards the major fields.

How do I get the or the or the symbols in my work?

Use (c) and (r) and (tm). Excel will automatically change them to , and using its "AutoCorrect" feature which is on the "Tools" menu.

I don抰 want my work corrected as I type.

On the Tools menu, click "AutoCorrect". To prevent all automatic corrections, clear the Replace text as you type check box. To prevent specific types of corrections, clear the check box for the corresponding option.

Can I turn "AutoCorrect" off?

"AutoCorrect" has five check boxes that allow you to control what it does or doesn抰 correct. "AutoCorrect" is on the "Tools" menu.

Facts and figures

Largest

.. allowed negative number

-1E-307

.. allowed positive number

1E+308

Dates

Latest date allowed for calculation

December 31, 9999

Earliest date allowed for calculation

January 1, 1900 (January 1, 1904, if 1904 date system is used)

Maximum

.. column width

255 characters.

.. length of formula contents

1,024 characters.

.. length of textual cell contents

32,000 characters.

.. number of adjustable cells in solver

200

.. number of arguments in a function

30

.. number of calculated item formulas in a pivot table

Limited by available memory

.. number of cell styles in a workbook

4000

.. number of changing cells in a scenario

32

.. number of colours in a workbook

56

.. number of custom functions

Limited by available memory

.. number of custom number formats

Limited by available memory

.. number of custom toolbars can I create

Limited by available memory.

.. number of data fields in a pivot table

256

.. number of fields in a data form

32

.. number of items in a pivot table

8000

.. number of iterations

32767

.. number of linked sheets

Limited by available memory

.. number of named views in a workbook

Limited by available memory, although the Custom Views dialog box lists only the first 256 views.

.. number of names in a workbook

Limited by available memory

.. number of nested levels of functions

7

.. number of open workbooks

Limited by available memory and system resources.

.. number of page fields in a pivot table

256 (May be limited by available memory)

.. number of pages in a pivot table

8000

.. number of panes in a window

4

.. number of pivot tables on a sheet

Limited by available memory

.. number of reports

Limited by available memory

.. number of row or column fields in a pivot table

Limited by available memory

.. number of scenarios

Limited by available memory; a summary report shows only the first 251 scenarios.

.. number of selected ranges

2048

.. number of sheets in a workbook

Limited by available memory (default number of sheets is 3; the .. number of sheets in a default workbook is 255).

.. number of sort references

3 in a single sort; unlimited when using sequential sorts

.. number of toolbar buttons

Limited by available memory.

.. number of undo levels

16 (default)

.. number of windows in a workbook

Limited only by system resources

.. row height

409 points.

.. size of worksheet arrays

6,553 elements

.. worksheet size

65,536 rows by 256 columns.

Other

Worksheet functions

329

Number precision

15 digits.

Smallest allowed negative number

-1E+308

Smallest allowed positive number

1E-307

Zoom range

10 percent to 400 percent

Can I have more than 255 worksheets

Yes. You will have to add them either manually or by using a VBA program.

How do I change the default number of sheets in a new workbook

From the worksheet menu select "Tools > Options [General]", then change "Sheets in new workbook:" to any number between 1 and 255.

Row heights are measured in points. What is a "Point"?

A point is a unit of measurement that determines the height of a character. A point is approximately 1/72 of an inch.

How are column widths measured?

The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell. The "Standard Font" is the default text font for worksheets defined in the "Normal" style.

How do I make the cursor move down to the next cell when I press "Enter"?

From the Tools menu select Options. Click on the Edit tab, then check the box Move selection after Enter, and finally select the direction you want to move by selecting Down, Right, Up, or Left from the Direction drop-down box.

Why aren抰 my VLOOKUP formulas producing the same results?

Excel抯 VLOOKUP works in a different manner to 123抯. It has an optional fourth argument which you may need to use in order to get the same result.

The Lotus format is:
@VLOOKUP(LookupValue, SourceTable, ColumnOffset)

The Excel format is:
=VLOOKUP(LookupValue, SourceTable, ColumnOffset,[TRUE/FALSE])

Check the Excel Help file for more information.

Excel is changing what I am typing into the cell. Why?

The most likely cause is an entry in "AutoCorrect" table. Click on "Tools" then select "AutoCorrect" and see if there is an entry for your problem word.

Why are you recommending that we do not use "Transition formula evaluation"?

"Transition formula evaluation" lets Excel treat formulas as though they should be evaluated according to Lotus 123 rules. This can lead to unexpected or incorrect results when data is shared between users who do not follow the same evaluation rule.

When I enter a date, e.g., 3/4/99, I get "=3/4/99" in the formula bar and "0.0075758" in the cell. Why?

You have got "Transition formula entry" turned on. To tuen it off, click on "Tools, Options", then click on the "Transition" tab and uncheck the "Transition formula entry" box.

How do I find the last day of a month?

To find the last day of December 1998, use the formula "=DATE(1999,1,0)". The "zeroth" day of the current month represents the last day of the previous month.

OR the more obvious,

"=DATE(1999,1,1)-1"

Note: This document is culled from many sources.

Originally published: 2000
Last modified 01-Mar-2011