Cómo convertir archivos de Lotus 123 a ExcelEn un momento, Lotus 123 era la hoja de cálculo dominante en el mercado. En la década de los 90 comenzó a perder

su porción del mercado con Excel. Cargar los archivos de Lotus 123 en Excel es factible y hay dos maneras de

hacerlo: guardar el archivo en formato Excel desde Lotus 123, o bien abrir el archivo de Lotus 123 en Excel y dejar

que éste maneje la conversión.

Necesitarás Excel 1997 o posterior Lotus 123 (recomendado)

Instrucciones

1.- Abre tu archivo en Lotus 123.

2.- Dirígete al menú "Archivo", selecciona "Guardar como" y guárdalo como un archivo de Microsoft 97 (*.xls).

3.- Abre el archivo .xls resultante en Excel.

1.- Abre Excel.

2.- Ubica el archivo de Lotus 123 (con una extensión de *.wk4 en la mayoría de los casos).

3.- Haz clic en "Abrir". Excel te indicará que lo apruebes antes de convertir el archivo.

4.- Dirígete al menú "Archivo" y selecciona "Guardar como" para guardar una copia del archivo como un formato nativo de Excel.

Cómo convertir wk1 a xlsEs posible que necesites tener acceso a archivos de Lotus 1-2-3 cuando tienes solo el Excel para abrirlos. Puedes convertir los archivos de Lotus fácilmente abriéndolos en el Excel y guardándolos en el formato XLS. Sin embargo, pueden producirse problemas. Por ejemplo, los archivos WK1 mantienen su formato en un archivo separado del que contiene los datos.

Instrucciones

1.- Confirma que tanto el archivo de datos como el de formato del WK1 estén en la misma carpeta.

2.- Abre el menú desplegable "Archivo" del Excel. Haz clic en "Abrir". Busca y abre el archivo WK1 en la carpeta del directorio. No abras el archivo de formato tu mismo, solo el que tiene la extensión WK1.

3.- Abre el menú desplegable "Archivo" nuevamente. Haz clic en "Guardar". Selecciona .XLS como tipo de archivo en el panel Guardar. Selecciona "OK". Excel convertirá la planilla al formato de Excel.

4.- Abre el archivo .XLS convertido para revisar si tiene errores. Busca la frase "la fórmula no se convirtió correctamente". Si Excel no pudo convertir una fórmula, mostrará el valor de la misma y tendrá el mensaje "la fórmula no se convirtió correctamente" en un comentario de la celda. Para buscar esta frase en los comentarios, abre el menú

desplegable Editar y haz clic en la siguiente secuencia: "Buscar > Buscar en: Comentarios". Recrea las fórmulas que se hayan perdido cuando sea posible.

5.- Imprime las nuevas planillas y compara los números con las impresiones de las planillas originales de Lotus, si están disponibles. Esta es una revisión original para asegurarte de que la conversión se haya realizado correctamente.

Sugerencias para la importación de archivos de Lotus 1-2-3 a ExcelEste artículo contiene información acerca de los posibles problemas que pueden surgir al importar archivos de Lotus 1-2-3 en Microsoft Excel.

Abrir archivos de WK4

Para abrir archivos de Lotus 1-2-3 WK4 en Microsoft Excel versión 5.0, debe obtener el convertidor de archivos de Lotus 1-2-3 WK4. El convertidor de archivos de Lotus 1-2-3 WK4 está disponible en la nota de aplicación "Lotus 1-2-3 WK4 File Converter" (WE1130). Nota: Microsoft Excel 7.0 y Microsoft Excel 97 incluyen un convertidor de archivos Wk4. El convertidor incluido con Microsoft Excel 7.0 permite leer el formato de archivo de Lotus 1-2-3 Wk4. El convertidor de Microsoft Excel 97 le permite leer y escribir el formato Wk4.

Abrir archivos de WK4

Cuando se abre una hoja de cálculo de Lotus 1-2-3 o un libro, Microsoft Excel aplica el formato almacenado en cualquier asociado .fmt,. fm3 o en todos los archivos. Asegúrese de que el archivo de formato asociado se almacena en la misma carpeta que el archivo .wk. archivo. Si se vuelve a guardar un archivo de Lotus 1-2-3 en formato de Microsoft Excel (.xls), Microsoft Excel guarda los datos y el formato en un único archivo de libro.

Formato

En Microsoft Excel 5.0, cuando se abre un archivo de Lotus 1-2-3 WK4, objetos de dibujo, como macros botones, cuadros de texto y líneas, no se convierten. Además, se muestran los nombres de hoja de cálculo como el valor predeterminado de Lotus 1-2-3: A, B, C y así sucesivamente. El convertidor de archivos de Lotus 1-2-3 WK4 no convierte los objetos de dibujo en una hoja de cálculo. Esto incluye cualquiera de los siguientes elementos:

arc arrow button ellipse embedded object freehand

line polygon polyline rectangle rounded rectangle text

Nota: este problema no se produce en las versiones posteriores.

Objetos

Microsoft Excel 4.0 y versiones posteriores: 

En el Lotus 1-2-3, versiones 3.x y posterior, puede crear un gráfico en una hoja de gráfico o crear el gráfico como un objeto en la hoja de cálculo. 

De forma predeterminada, Microsoft Excel convierte automáticamente los gráficos asociados a una hoja de cálculo de Lotus 1-2-3.

Load_Chart_Wnd=0

Debido a que Microsoft Excel puede leer archivos Impress. (FM3) y siempre (todos) formato de archivos, puede importar una hoja de cálculo de Lotus 1-2-3 que contiene un gráfico en la hoja de cálculo. 

Microsoft Excel 2.x y 3.x: 

Cuando importa un archivo que contenga un gráfico, Microsoft Excel 2.x y 3.x muestran un mensaje para cada gráfico que se almacena con la hoja de cálculo o se encuentra en la hoja de cálculo. Se le preguntará si desea convertir el archivo a un gráfico de Microsoft Excel. Si hace clic en Sí , Microsoft Excel crea una nueva ventana de gráfico. En Microsoft Excel 3.x, a continuación, puede copiar el gráfico a la hoja de cálculo.

Gráficos

La base de datos, criterios y extraer rangos definidos se importan correctamente y funcionan correctamente. Sin embargo, rangos de criterios de base de datos se evalúan de forma diferente al extraer los datos, buscar datos y utilizar funciones de base de datos. Por ejemplo, un criterio de "Juan" busca sólo las filas con celdas que contienen "Juan". Si desactiva la casilla de verificación Evaluación de fórmulas de transición , un criterio de "Juan" encontrará filas que contienen celdas con valores que empiezan con "Juan"; por ejemplo, las celdas que contienen "Juan", "Couto", y se encuentra "Johnsen".

Bases de datos

Cada vez que abra un archivo de Lotus 1-2-3, se selecciona la casilla de verificación de la Entrada de fórmula de transición . 

Microsoft Excel calcula las fórmulas de forma diferente de Lotus 1-2-3. Cuando se utiliza una celda que contiene texto en una fórmula, Lotus 1-2-3 asigna un valor de 0 (cero) a la celda. En Microsoft Excel, no se pueden combinar texto y entradas numéricas en la misma fórmula. Sin embargo, cuando se utiliza una función de hoja de cálculo en Microsoft Excel, se asigna un valor de 0 a celdas que contienen texto. Por ejemplo, si desactiva la casilla de verificación Evaluación de fórmulas de transición y escriba el texto en la celda A1 y el valor 100 en la celda B1; la fórmula = A1 + B1 devuelve #VALUE! valor de error.Sin embargo, la hoja de cálculo =SUM(A1,B1) fórmula devuelve el valor 100. 

Lotus 1-2-3 evalúa las expresiones booleanas en 0 o 1 y muestra 0 o 1 en la celda. Por ejemplo, en Lotus 1-2-3, la expresión 2 < 3 muestra 1 en la celda para representar verdadero; Microsoft Excel muestra True o False en la celda. 

Si selecciona la casilla de verificación Evaluación de fórmulas de transición , Microsoft Excel mostrará 0 para Falso y 1 para Verdadero. 

Algunas funciones, como @MOD, @VLOOKUP y @HLOOKUP, se evalúan de manera diferente. 

Para hacer que Microsoft Excel calcular fórmulas como Lotus 1-2-3, siga estos pasos:

1. En el menú Herramientas, haga clic en Opciones. Haga clic en la ficha transición .2. Haga clic en Evaluación de fórmulas de transicióny haga clic en Aceptar.

Cálculos

Matemática orden de precedencia de diferencias 

Esta tabla compara los operadores matemáticos empleados por Microsoft Excel y 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

Nota: Lotus 1-2-3 evalúa el operador de exponenciación (^) antes que el operador de negación (-). Microsoft Excel se evalúa primero el operador de negación. Por ejemplo, en Lotus 1-2-3, la fórmula =-2 ^ 4 devuelve el valor -16, pero devuelve 16 en Microsoft Excel. Para corregir esta diferencia, utilice paréntesis para cambiar el orden de evaluación; Por ejemplo, =-(2^4) resultado -16.

Orden de cálculo

En Microsoft Excel, al abrir un archivo. WK4 de Lotus 1-2-3 que contiene un vínculo a otro archivo, las celdas pueden actualizarse con un #REF! valor de error. Para actualizar un vínculo externo en un archivo. WK4 de Lotus 1-2-3, siga estos pasos:

1. En Microsoft Excel, haga clic en vínculos en el menú Edición .2. En el cuadro de diálogo vínculos , seleccione el vínculo que desee actualizar. Haga clic

en Actualizar ahora. 

Nota: si desea abrir el documento de origen, haga clic en Abrir.Para evitar este comportamiento, guarde el archivo en formato de libro de Microsoft Excel.

Vínculos

Microsoft Excel y Lotus 1-2-3 utilizan los mismos sistemas de fecha de serie. Los valores de la serie en Microsoft Excel 7.0 y anterior entre 0 (1/1/1900) a 65380 (31/12/2078). Los valores de fecha de serie en Lotus 1-2-3 intervalo de 0 a 73050 (31/12/2099). Si importa una fecha de Lotus 1-2-3 que contiene una función de fecha con un año más tarde, a continuación, 2078, la función devuelve un #NUM! valor de error. Si importa una hoja de cálculo que contenga una fecha con formato con un año más tarde 2078, Microsoft Excel rellena la celda con 255 signos de número (#). 

Nota: este problema no existe en Microsoft Excel 97 porque permite fechas hasta el año 9999.

Convertir fechas

Las macros de Lotus 1-2-3 se almacenan directamente en la hoja de cálculo. Esto es diferente de cómo Microsoft Excel almacena las macros. Microsoft Excel almacena las macros en una hoja de macros (en Microsoft Excel 4.0) o en una hoja de módulo de las macros escritas en Visual Basic para Aplicaciones para

Microsoft Excel 5.0 y versiones posteriores. 

Microsoft Excel 4.0a y posterior: 

Algunas versiones anteriores de Microsoft Excel pueden ejecutar macros de Lotus 1-2-3 directamente. No es necesario traducir (reescritura) la macro. Al abrir un archivo de Lotus 1-2-3 que contiene macros, puede ver una lista de todos los disponibles 1-2-3 las macros en la lista de nombres definen para ese libro. Para ejecutar la macro de Lotus 1-2-3, presione CTRL y la letra asociada para la macro. Por ejemplo, presione CTRL + P. 

Nota: Excel 97 con la actualización de seguridad (8.0 h y posteriores) y Excel 2000 no ejecutan macros de Lotus 1-2-3. 

Microsoft Excel 4.0 y versiones anteriores: 

Para convertir las macros de Lotus 1-2-3 (reescritura) de macros de Microsoft Excel, haga lo siguiente:

1. En Microsoft Excel, abra la hoja de cálculo de Lotus 1-2-3 que contiene la macro.2. En el menú control, haga clic en Ejecutar. Para abrir el menú de control, presione ALT+BARRA

ESPACIADORA.3. Haga clic en Convertidor de Macroy, a continuación, haga clic en Aceptar.4. En el menú de traducir , haga clic en Lotus 1-2-3. Seleccione el nombre de la hoja de cálculo que

desea convertir.5. Seleccione el nombre de la macro que se va a convertir. Si desea que el traductor de macro de la

lista 1-2-3 al lado de la macro convertida de Microsoft Excel, haga clic en la opción Verbose .Microsoft Excel coloca la macro convertida en una nueva hoja de macro.

Volver arriba | Enviar comentarios

Referencias

Para obtener más información acerca de cómo convertir archivos de Lotus 1-2-3, haga clic en la ficha índice de la Ayuda de Microsoft Excel 97, escriba el texto siguiente

Conversión de archivos de Lotus 1-2-3.

y, a continuación, haga doble clic en el texto seleccionado para ir al tema "Información clave para usuarios de todos los niveles" . 

Para obtener más información acerca de la importación de hojas de cálculo, fórmulas, referencias y formatos de las versiones de Lotus 1-2-3 consulte "cambiar a Microsoft Excel de Lotus 1-2-3," versión 4.0, páginas 24-31, 38, o en "Manual del usuario",versión 3.0, páginas 41-64.

Volver arriba | Enviar comentarios

123 to Excel

Conversion issues

Excel opens and saves the following Lotus 123 file formats.

This Lotus 123 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+, 123/W, R1.1 WK3, FM3

4.0, 5.0 WK4

 I can’t open my Lotus file in Excel

Excel will open files created in Lotus 123 versions 2.x5.x. Most data and formatting created in Lotus 123 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 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:

o Auditing submenu commands (Tools menu)o Find command (Edit menu)o 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.".

Opening and Saving Lotus 123 Worksheets in Microsoft Excel

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

To open a Lotus 123 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 123 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 123 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 123 format, you can also use the File Conversion Wizard addin.

I’ve lost all the formatting from my converted file Lotus 123 files

When formatting is applied to a Lotus 123 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 convert4. 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 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:

o Supply values to the active cell (Precedents option)o Use the value in the active cell (Dependents option)o Contain only values (Constants option)o Contain only formulas (Formulas option)o Contain error values (Errors check box under Formulas option)o Contain comments (Comments option)o 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 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 12Feb 2Mar 4Apr 34May 32

Jun 8Jul 43Aug 3Sep 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 123 users, which provides macro conversion support.

 Terminology and Equivalents

Microsoft Excel Terms for Lotus 123 Users

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

 This Lotus 123 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 Oneinput table

Data table 2 Twoinput 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

Labelprefix 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 123 Commands

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

 This Lotus 123 commandCorresponds to this Excel command or option

/c Copy and Paste (Edit menu)

/fd Open (File menu)

/few Delete on the document shortcut menu in theOpen 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 Areasubmenu)

/qy Exit (File menu)

/re Clear (Edit menu)

/rf Number tab in the Cells dialog box (Formatmenu)

/rfc Number tab in the Cells dialog box (Formatmenu)

/rfp Number tab in the Cells dialog box (Formatmenu)

/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 123 and their equivalents in Excel.

 This Lotus 123 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 123 System Command (Windows only)

There is no direct equivalent for the Lotus 123 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’s 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 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)

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

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

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

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

Running Macros Created in Lotus 123 Release 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

1. In Lotus 123, load the file by attaching the addin.2. In the Lotus Macro Library Manager, copy the library commands to a worksheet using the Edit command.3. Save the worksheet in Lotus 123 WK1 format.4. Open the Lotus 123 worksheet in Excel.

Will my autoexec macros run?

If you have a Lotus 123 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’ve 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 123 \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 123 addin, such as the Allways addin 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 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.

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 YYYY,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) orFormat Cells (Macintosh) dialog box (Formatmenu)

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 Dircommand. (The File Dircommand is a separate command from the File Retrieve.)

Change folders and open files in the Opendialog 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 123 Charts in Microsoft Excel

Introduction

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.

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 123 Graph Options Grid Command Equivalents

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

 

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

1. Rightclick 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 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.

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

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

1. Click the xaxis (category) or yaxis (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 xaxis and the yaxis.

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 xaxis (category) or yaxis (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 xaxis and the yaxis.

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 yaxis (value).

You can change the xaxis 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 123 Worksheet Global Default 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

 What is the Excel Equivalent for the Lotus 123 Line Print Command?

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

What are Excel’s equivalent features for typical Lotus 123 print printer commands?

 This Lotus 123 printer commandCorresponds to this Excel feature or action

Range Set Print Area (File menu, Print Areasubmenu).

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, clickCells, and then click the Font tab.

Pg-Length

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

Other Worksheet is printed as displayed. To display values or formulas, click the View tab in theOptions (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 123 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 threedimensional 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 addin, 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 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.

Do Lotus 123 Releases 4 and 5 have 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 colour from the fill.

Does Excel have functions that were not available in Lotus 123?

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

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

 Excel's help for Lotus users

Tell me about the Lotus 123 Help feature in Excel.

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

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

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

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

2. 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 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’s 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’t 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’t 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 Directiondrop-down box.

Why aren’t my VLOOKUP formulas producing the same results?

Excel’s VLOOKUP works in a different manner to 123’s. 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: 2000Last modified 01-Mar-2011