C H A P T E R    18 Part 4  Switching from Other Applications Microsoft Office Resource Kit

Switching to Microsoft Excel Previous

Contents
Next

Index


In This Chapter
Converting File Formats in Microsoft Excel
Switching from Lotus 1­2­3
Switching from Quattro Pro for MS­DOS
Switching from Quattro Pro for Windows
Switching from Multiplan
Switching from Microsoft Works

See Also

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.

Top

Converting File Formats in Microsoft Excel

File formats determine the way information in a spreadsheet document is stored in a file. Different spreadsheet applications use different file formats. Excel allows you to open and save files in many different formats, using the Open and Save As commands on the File menu.

The list of formats that appears in the Save As dialog box (File menu) varies depending on what kind of sheet is active. Some file formats save the entire workbook; others save only the active worksheet. (When saving the latter file type, you must save each sheet in the workbook individually.) See the following sections for a comprehensive summary of these formats.

Opening Other File Formats in Microsoft Excel

Excel 97 and Excel 98 can open documents saved in any format listed in the Files of type (Windows) or List files of type (Macintosh) box in the Open dialog box (File menu). However, Excel cannot save documents in every one of these file formats.

The following table lists file formats that Excel can open but not save.

This source application Saves data in this file format
Microsoft Works version 2.0 (MS­DOS and Windows only) WKS
Lotus 1­2­3 Releases 3.0, 3.1+, and 1­2­3/W PIC format PIC (when included in an ALL file)
Lotus 1­2­3 Release 5 for Windows Templates WT4
Quattro Pro for Windows Version 5.0 WB1

Converting Documents from Other File Formats

The majority of your spreadsheet documents can be converted to Excel 97 (Windows) or Excel 98 (Macintosh) format by opening and then saving them in Excel.

To open a document in Excel

  1. On the File menu, click Open.

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

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

  4. Click Open.

    Excel converts the document and opens it.

Note   When you convert a document, Excel preserves the original content and formatting as much as possible. However, other applications might have similar features that work differently, so you might not always get the results you expect. Major conversion issues for Lotus 1­2­3 and other applications are described later in this chapter. For more information, see the section that corresponds to the application from which you are switching or converting documents.


Microsoft Excel did not convert my document properly

The file formats that Excel supports are listed in the Files of type (Windows) or List files of type (Macintosh) box in the Open dialog box (File menu). If your document type is not listed, you must save the document in a format this is supported by Excel. Open the document in the program it was created in, and then save the document in a compatible file format that Excel can open. For example, save the document as a text file.

After you convert a document to Excel, the converted document exists only in your computer's memory; the original document remains unchanged. To complete the conversion, you need to save the converted document in Excel format.

To save a converted document in Excel format

  1. On the File menu, click Save As.

  2. In the File name (Windows) or Save as (Macintosh) box, enter a new name for the converted document.

    This preserves the original document as a backup.

Converting Multiple Files at One Time

When you or your workgroup migrates to Excel 97 (Windows) or Excel 98 (Macintosh), you may need to quickly convert several documents at one time. To accomplish this task, open several documents at the same time in the Open dialog box (File menu). Excel converts each document you select. When you finish editing the documents, save each document as described earlier.

  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 documents, click the name of the first document you want to open, and then hold down SHIFT and click the name of the last document you want to open. Or hold down CTRL and click the names of the individual documents you want to open.

To convert several documents at once, you can also use the File Conversion Wizard add­in, which is supplied with Excel. The File Conversion Wizard makes it easy to convert a large library of spreadsheet files to Excel format. However, it does not convert from Excel format to other formats.

Note   Before using the File Conversion Wizard, place all of the files to be converted in one folder. If you have a number of files in different file formats, select the All Available Formats to Excel 8 option when using the Wizard.

To start the File Conversion Wizard

  1. On the Tools menu, point to Wizard, and then click File Conversion.

  2. Follow the instructions on your screen.

The File Conversion Wizard converts all files of the format and in the location you specify. The file types available to the File Conversion Wizard are the same file types found in the Open dialog box (File menu). After the File Conversion Wizard converts the files you select, it creates a summary report in a new workbook.

  Note   The File Conversion Wizard is not installed when you choose a Typical installation during Office 97 (Windows) Setup. If the File Conversion Wizard does not appear in the Add­Ins dialog box (Tools menu), rerun Setup and click Add/Remove. Under the Microsoft Excel option, select the Add­Ins option, and then select the File Conversion Wizard option.

  Note   The File Conversion Wizard is not installed when you choose an Easy installation during Office 98 (Macintosh) Setup. If the File Conversion Wizard does not appear in the Add­Ins dialog box (Tools menu), insert the compact disc that came with Microsoft Office 98 into the CD-ROM drive. If you installed Microsoft Office 98 or Excel from a network volume, connect to that network volume. Rerun Setup, and then click Custom Install. Follow the directions on the screen to finish installing the File Conversion Wizard.

Importing Text Files

When you open a text file in Excel, the Text Import Wizard guides you through the steps to import the text file and parse the text properly across columns.

To convert a text document to Excel format

  1. On the File menu, click Open.

  2. In the Files of type (Windows) or List files of type (Macintosh) box, click the document's original format.

  3. Below the Look in (Windows) or Select a document (Macintosh) box, select the text file you want to convert.

  4. Click Open, and then follow the instructions that appear in the Text Import Wizard.


Microsoft Excel does not list the document in the Open dialog box Make sure that you selected the correct file type or extension in the Files of type (Windows) or List files of type (Macintosh) box. If the document name you are looking for still does not appear, click All Files. Also check to make sure that you have selected the correct drive and folder.

Saving Microsoft Excel Workbooks in Other File Formats

You can save Excel workbooks or worksheets in file formats that can be read by other applications or by previous versions of Excel.

Tip   When you save an Excel workbook in another file format, use a different file name so that you can keep a copy of the original workbook as a backup.

To save an Excel document in another file format

  1. On the File menu, click Save As.

  2. In the File name (Windows) or Save as (Macintosh) box, enter a name for the converted workbook.

  3. In the Save as type box (Windows) or Save file as type box (Macintosh), click the format you want.

Tip   You can also specify the default format in which Excel saves new workbooks.

To specify the default format in which to save workbooks

  1. On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.

  2. In the Save Excel file as (Windows) or Default save as type (Macintosh) box, click the file format you want.

For more information about selecting a default format in which to save workbooks, 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:

User\Excel\Tools_Options\Transition\Default Save

For more information, see "Using Windows System Policies to Customize Office" in Chapter 7, "Customizing and Optimizing Microsoft Office."

You can save Excel workbooks or worksheets in the file formats described in the following sections.

File Formats That Save the Entire Workbook

When you save an Excel workbook in any of the following file formats, all sheets in the workbook are saved.

Windows format Macintosh format
Microsoft Excel Workbook Microsoft Excel Workbook
TemplateTemplate
Microsoft Excel 5.0/95 Workbook Microsoft Excel 5.0/95 Workbook
Microsoft Excel 97 & 5.0/95 Workbook Microsoft Excel 5.0 & 95-98 Workbook
Microsoft Excel 4.0 Workbook Microsoft Excel 4.0 Workbook
WK4 (1­2­3) WK4 (1­2­3)
WK3, FM3 (1­2­3) WK3, FM3 (1­2­3)

Note   Not all Excel data and formatting is saved in these formats. For more information about lost data or formatting when saving in previous Excel formats, see Chapter 12, "Upgrading from Previous Versions of Microsoft Excel."

File Formats That Save Only the Active Sheet

The following table lists file formats that save only the active sheet.

Windows format Macintosh format
Formatted Text (Space delimited) Formatted Text (Space delimited)
Text (Tab delimited) Text (Tab delimited)
CSV (Comma delimited) CSV (Comma delimited)
Microsoft Excel 4.0 Worksheet Microsoft Excel 4.0 Worksheet
Microsoft Excel 3.0 Worksheet Microsoft Excel 3.0 Worksheet
Microsoft Excel 2.1 Worksheet Microsoft Excel 2.2 Worksheet
WK1, FMT (1­2­3) WK1, FMT (1­2­3)
WK1, ALL (1­2­3) WK1, ALL (1­2­3)
WQ1 (Quattro Pro/DOS) Not supported
DBF 4 (dBASE IV)DBF 4 (dBASE IV)
DBF 3 (dBASE III) DBF 3 (dBASE III)
DBF 2 (dBASE II) DBF 2 (dBASE II)
CSV (Macintosh) CSV (Windows)
CSV (OS/2 or MS­DOS) CSV (OS/2 or MS­DOS)
DIF (Data Interchange Format) DIF (Data Interchange Format)
SYLK (Symbolic Link) SYLK (Symbolic Link)

Top

Switching from Lotus 1­2­3

This section describes the differences between Lotus 1­2­3 2.x­5.x and Excel. Excel includes several features that protect the Lotus 1­2­3 user's investment in Lotus 1­2­3 knowledge and experience, as well as strong file and macro conversion support for macros compatible with Lotus 1­2­3 Release 2.01.

What happens to my old Lotus 1­2­3 documents when I open them in Excel?   You can open files created in Lotus 1­2­3 2.5.x directly in Excel. Most data and formatting created in Lotus 1­2­3 are fully supported by Excel. For more information, see "Converting Lotus 1-2-3 Worksheets to Microsoft Excel" later in this section.

Can I share Excel documents with users of Lotus 1­2­3?   If your workgroup is using a combination of Lotus 1­2­3 2.x­5.x and Excel, users can exchange documents between versions. However, not all Excel features are supported in Lotus 1­2­3. For more information, see "Sharing Documents with Lotus 1-2-3" later in this section.

Do my old macros work in Excel?   Excel includes the Macro Interpreter for Lotus 1­2­3 users, which provides strong macro conversion support. For more information, see "Converting Lotus 1-2-3 Worksheets to Microsoft Excel" later in this section.

Using Microsoft Excel Tools for Lotus 1­2­3 Users

Although the commands and procedures for entering data in Lotus 1­2­3 differ from those of Excel, several features built into Excel allow you to use what you know about working with Lotus 1­2­3 to learn Excel. By using some or all of these options, you can be immediately productive and learn Excel at the same time.

Note   Unless otherwise noted, the options described in the following table are available only in Excel 97 for Windows. Excel 98 for the Macintosh includes only two options on the Transition tab in the Preferences dialog box (Tools menu): Microsoft Excel menu or help key and Transition formula evaluation.

If you want to Do this
Use Lotus 1­2­3 commands to learn equivalent Excel commands. On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab. Under Settings in the Microsoft Excel menu or Help key box, type a menu­activation character (the slash character is entered by default), and then click Lotus 1­2­3 Help (Windows and Macintosh).
Move through Excel worksheets using an alternate command set. On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab. Under Settings, select the Transition navigation keys check box.
Learn Excel formula syntax by using Lotus 1­2­3 function syntax. On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab. Under Sheet Options, select the Transition formula entry check box.
Assist file conversion to Excel by using Lotus 1­2­3 rules for evaluating text fields 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.
Run your Lotus 1­2­3 macros. Open the Lotus 1­2­3 file containing the macros you want to use, hold down CTRL, and press the macro activation letter.

Microsoft Excel Transition Options

Some Excel spreadsheet operations — such as calculating formulas, using the keyboard, and entering dates —work differently from those in other spreadsheet applications. However, Excel lets you decide how you want these features to work. You can select either the standard Excel operation or the operation that matches Lotus 1­2­3 and other Lotus 1­2­3­compatible spreadsheet applications.

To specify how you want Excel to operate, use the options on the Transition tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu).

Transition Navigation Keys (Windows Only)

  Excel 97 for Windows provides an alternate set of keyboard commands for moving around spreadsheets. For example, in Lotus 1­2­3 pressing HOME moves the active cell highlight to cell A1. In Excel, pressing HOME moves the active cell highlight to the first cell in the current row. When you use transition navigation keys in Excel, however, pressing HOME moves the active cell to cell A1.

To activate alternate keyboard commands

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

  2. Under Settings, select the Transition navigation keys check box.

The following tables list the keyboard shortcuts that are activated when you select the Transition navigation keys check box in Excel.

This key combination Performs this action in Excel
CTRL+LEFT ARROW or SHIFT+TAB Moves left one page
CTRL+RIGHT ARROW or TAB Moves right one page
CTRL+PAGE UPGoes to the next sheet in a workbook
CTRL+PAGE DOWNGoes to the previous sheet in a workbook
HOMESelects the cell in the upper­left corner of the sheet
F5Activates the Go To command (Edit menu)

When the Transition navigation keys check box is selected, you can use the following text­alignment prefix characters to assign alignment formats as you enter data into cells.

This prefix character Performs this action in Excel
Apostrophe (')Aligns data in the cell to the left
Quotation mark (") Aligns data in the cell to the right
Caret (^)Centers data in the cell
Backslash ( \ ) Repeats characters across the cell

Transition Formula Entry (Windows Only)

  Excel 97 for Windows uses a different syntax than Lotus 1­2­3 in formulas and functions. Using transition formula entry to learn the Excel syntax, you can enter any formula or function exactly as you would in Lotus 1­2­3, and you are shown how it is entered in Excel.

To enter a formula, function, or range name in Lotus 1­2­3 syntax

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

  2. Under Sheet Options, select the Transition formula entry check box.

Note    Excel does not automatically turn on transition formula entry for Excel workbooks or Lotus 1­2­3 worksheets. You must select the option on the Transition tab.

When the Transition formula entry check box is selected, you can:

  • Enter any formula or function as you would in Lotus 1­2­3.

    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 1­2­3 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 1­2­3 range names that originate in Lotus 1­2­3 are affected by transition formula entry.

Transition Formula Evaluation

Excel and Lotus 1­2­3 evaluate certain formulas and expressions differently. Transition formula evaluation allows Excel to calculate formulas and database criteria according to Lotus 1­2­3 rules.

To use Lotus 1­2­3 rules to calculate formulas and database criteria

  1. On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.

  2. Under Sheet Options, select the Transition formula evaluation check box.

The following table compares expressions that are evaluated differently in Excel and Lotus 1­2­3.

This expression Is evaluated this way in Lotus 1­2­3 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 1­2­3 functions, including @MOD, @VLOOKUP, and @HLOOKUP, are evaluated differently than the equivalent Excel functions.

    For example, the Lotus 1­2­3 @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 1­2­3 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 1­2­3 rules.

If you save the Lotus 1­2­3 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 1­2­3 rules.

New A Functions in Microsoft Excel

Excel includes several new functions that are evaluated in the same way as their corresponding Lotus 1­2­3 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 non­numeric data, just as its corresponding Lotus 1­2­3 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 1­2­3 functions are listed in the following table.

This Excel A function Corresponds to this Lotus 1­2­3 function
AVERAGEA@AVG
MINA@MIN
MAXA@MAX
VARA@VARS
VARPA@VARP
STDEVA@STDS
STDEVPA@STD

Note   When a Lotus 1­2­3 sheet is opened in Excel, Lotus 1­2­3 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 non­A functions in Excel, as described in the following table.

Value typeNon­A functions A functions
NumbersTreated normally Treated normally
TextText is ignored Text has a value of 0
TRUE Logical Value TRUE is ignoredTRUE has a value of 1
FALSE Logical Value FALSE is ignoredFALSE 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."

Microsoft Excel Terms for Lotus 1­2­3 Users

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

This Lotus 1­2­3 term Corresponds to this Excel term or concept
@FunctionFunction
AddressReference
Anchor cellSelecting a range of cells
BorderRow and column headings
CALC indicatorStatus bar
Cell pointerActive cell
Column labelsColumn headings
Command promptDialog box
Control panelMenu bar, formula bar, status bar
CopyCopy and paste
CrosshatchingChart patterns
Current cellActive cell
Current worksheetActive worksheet or chart or macro sheet
Data labelsData marker labels
Data rangeData series
Data table 1One­input table
Data table 2Two­input table
Date formatNumber format
EraseClear
Formula criteriaComputed criteria
GlobalWorkspace
GraphChart
Graph labelsChart text
Graph titlesChart titles
HighlightSelect or selection
IndicatorStatus bar
Input rangeDatabase range
LabelText
Label­prefixAlignment
Label/matching criteria Comparison criteria
Logical 0FALSE
Logical 1TRUE
Menu pointerMenu selection
Mode indicatorStatus bar
MoveCut and paste
Number/matching criteria Comparison criteria
Numeric formatNumber format
Output rangeExtract range
Picture fileChart document
Pointer movement keys Arrow keys
Print rangePrint area
PrintGraphPrinting a chart
PromptDialog box
Protected cellLocked/protected cell
Range highlightSelected range
Repeating labelFill alignment
Retrieve a fileOpen a file
Row numbersRow headings
Stacked bar graphColumn chart, bar chart
Status indicator, status line Status bar
StringText
Target cellDependent cell
Target fileDependent document
Time formatNumber format
TitlesSplit worksheet window with frozen panes
Translate utilityOpen and Save As (File menu)
ValueNumber
WindowMultiple windows, pane

Microsoft Excel Equivalents for Lotus 1­2­3 Commands

The following table lists frequently used Lotus 1­2­3 commands and the equivalent commands in Excel.

This Lotus 1­2­3 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)

Microsoft Excel Equivalents for Lotus 1­2­3 Keyboard Commands

Excel default keyboard equivalents to Lotus 1­2­3 keys are listed in the following tables.

Note   When Windows users select the Transition navigation keys option on the Transition tab in the Options dialog box (Tools menu), Lotus 1­2­3 keyboard equivalents are automatically activated, and these Excel 97 keyboard equivalents do not apply. For more information, see "Using Microsoft Excel Tools for Lotus 1-2-3 Users" earlier in this chapter.

Function Keys

The following table compares function key assignments in Lotus 1­2­3 and their equivalents in Excel.

This Lotus 1­2­3 function key Corresponds to this Excel function key And has this meaning
F1F1 Help
F2F2 Edit
F3F3 Name
F4F4 Switches between absolute and relative formula references
F5F5 Go To
F6F6 Next pane (if panes are not frozen)
F8Performed automatically Table
F9F9 Calculate
F10F11 or ALT+F1 Graph

Navigation Keys

The following table compares navigation key assignments in Lotus 1­2­3 and their equivalents in Excel.

This Lotus 1­2­3 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 ARROWCTRL+UP ARROW or END+UP ARROW
END, DOWN ARROWCTRL+DOWN ARROW or END+DOWN ARROW
END, LEFT ARROWCTRL+LEFT ARROW or END+LEFT ARROW
END, RIGHT ARROWCTRL+RIGHT ARROW or END+RIGHT ARROW
HOMECTRL+HOME
TABALT+PAGE DOWN
SHIFT+TABALT+PAGE UP
PAGE UPPAGE UP
PAGE DOWNPAGE DOWN

Converting Lotus 1­2­3 Worksheets to Microsoft Excel

You can use Excel with your existing Lotus 1­2­3 worksheets by opening your Lotus 1­2­3 worksheet in Excel the same way you open any Excel workbook. When you finish editing the worksheet, you can save it as an Excel workbook or as a Lotus 1­2­3 worksheet.

Note   If you open a Lotus 1­2­3 .WK* file in Excel, the transition formula evaluation feature is automatically activated, and Excel uses Lotus 1­2­3 rules for calculating formulas. For more information about transition formula evaluation, see "Using Microsoft Excel Tools for Lotus 1-2-3 Users" earlier in this chapter.

Excel opens and saves the following Lotus 1­2­3 file formats.

This Lotus 1­2­3 release Saves data in this file format
1, 1AWKS
2.0, 2.01, 2.2WK1, ALL
2.3, 2.4WK1, FMT
3.0WK3
3.1, 3.1+, 1­2­3/W, R1.1 WK3, FM3
4.0, 5.0WK4

Note   When you open either WK3 or WK4 formats, Excel reads and writes both two­dimensional and three­dimensional worksheets.

When you open a Lotus 1­2­3 worksheet, Excel automatically opens the corresponding ALL, FMT, or FM3 file. Drop shadows and objects drawn on top of charts are not imported to Excel. Also, double underlines and wide underlines appear as single underlines in Excel.

When you save a Lotus 1­2­3 worksheet in Excel, an FMT or FM3 file is automatically saved. However, because of the limitations of the Impress add­in, you can save only the first eight styles you create and the first eight fonts you use.

Opening and Saving Lotus 1­2­3 Worksheets in Microsoft Excel

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

To open a Lotus 1­2­3 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­2­3 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­2­3 worksheet and opens it.

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

If the Lotus 1­2­3 worksheet you want to convert has graphs associated with it, Excel converts the Lotus 1­2­3 graphs to Excel charts, and places them on a separate chart sheet in the workbook. However, if the charts are created using either the Impress or Allways add­in, they are embedded on the corresponding worksheet.

After you convert a Lotus 1­2­3 worksheet to Excel, the converted worksheet exists only in your computer's memory; the original worksheet remains unchanged. To complete the conversion, you need to save the converted worksheet in Excel format.

To save a converted Lotus 1­2­3 worksheet in Excel format

  1. On the File menu, click Save As.

  2. In the File name (Windows) or Save as (Macintosh) box, enter a new name for the converted worksheet.

    This step preserves the original worksheet in Lotus 1­2­3 format as a backup.

  3. In the Save as type (Windows) or Save file as type (Macintosh) box, click Excel workbook.

Keep a copy of the original Lotus 1­2­3 worksheet; when a formula does not convert, you can refer to the original Lotus 1­2­3 formula and rebuild it using the equivalent Excel method. The most prevalent conversion problems occur in formulas that have more than seven levels of nesting in one formula, which is common with formulas that use multiple @IF functions. For more information about converting such formulas, see "Using Lotus 1-2-3 Formulas in Excel" later in this chapter.

Converting Multiple Lotus 1­2­3 Worksheets

To convert multiple Lotus 1­2­3 worksheets, open several worksheets at the same time in the Open dialog box (File menu). Excel converts each worksheet you select. When you finish editing the worksheets, save each worksheet as described in the previous section.

  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.

If you want to convert multiple worksheets to or from Lotus 1­2­3 format, you can also use the File Conversion Wizard add­in. For more information, see "Converting Documents from Other File Formats" earlier in this chapter.

Translating Lotus 1­2­3 Formatting

When formatting is applied to a Lotus 1­2­3 WK1 or WK3 worksheet, a separate file is created and saved along with the worksheet. In Excel, however, formatting information is saved in the workbook file.

This section describes the general procedure used to apply formatting in Excel, and describes how formatting in Excel corresponds to formatting in Lotus 1­2­3. For example, the following procedure demonstrates how to format numbers in an Excel worksheet.

To format numbers in Excel

  1. Select the cells you want to format.

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

  3. Under Category, click Number, and then select the formats you want.

You use the same general procedure to apply other types of formatting, using the other categories and tabs in the Cells (Windows) or Format Cells (Macintosh) dialog box (Format menu).

The following table lists the Excel equivalents for Lotus 1­2­3 number formats. The second and third columns indicate the category and selected options of the format, as they appear on the Number tab in the Cells (Windows) or Format Cells (Macintosh) dialog box (Format menu) in Excel.

This Lotus 1­2­3 number format Corresponds to this Excel number category With these Excel number formats
FixedNumber Default settings.
ScientificScientific Default settings.
CurrencyCurrency In the Negative numbers box, select ($1,234.10).
CommaNumber Select the Use 1000 Separator (,) check box.
GeneralGeneral None.
+/­No equivalent None.
PercentPercentage Default settings.
DateDate Under Type, select 04­Mar­97.
TimeTime Under Type, select 1:30:55 PM.
TextText You can display formulas or values for the entire worksheet. On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the View tab. Under Window options, select the Formulas check box. You can also see individual formulas in the formula bar.
HiddenCustom In the Type box, type ;;;
Or click Row or Column on the Format menu, and then click Hide to hide entire rows or columns.
ResetGeneral None.

When you convert a Lotus 1­2­3 worksheet to Excel, you must also convert the file that contains the formatting. Many Lotus 1­2­3 users have add­ins to help format their worksheets. Lotus 1­2­3 Releases 2.01 and 2.2 use the Allways add­in (which produces an ALL file); Releases 2.3 and later use the WYSIWYG or Impress add­in (which produces an FMT or FM3 file).

Allways Formatting

Excel fully supports Allways (ALL) files. When you import a Lotus 1­2­3 worksheet that has a corresponding ALL file (with the same name and in the same folder), Excel opens the ALL file automatically. When you save the Lotus 1­2­3 file, Excel saves both the WK1 file and a separate ALL file, if one existed originally. You can also create an ALL file when you save a WK1 file, even if none existed originally.

If a Lotus 1­2­3 worksheet has both an FMT or FM3 and ALL file associated with it, Excel ignores the ALL file and applies the formatting specified in the FMT or FM3 file.

WYSIWYG and Impress Formatting

When you open a Lotus 1­2­3 WK1 or WK3 worksheet that has a corresponding FMT or FM3 file created with the WYSIWYG or Impress add­in in the same folder, Excel opens the WYSIWYG or Impress file automatically and applies the equivalent formatting in Excel. Conversely, when you use Excel to save a Lotus 1­2­3 worksheet in Lotus 1­2­3 format, an FMT or FM3 file is automatically saved along with it (if one existed originally).

The Lotus 1­2­3 WYSIWYG or Impress add­in creates embedded graphs, and also draws arrows, ovals, and other objects on top of these graphs. In Excel, when you import a Lotus 1­2­3 worksheet and its associated WYSIWYG or Impress file, the embedded graph appears, but any overlaid drawings do not. However, after the worksheet and graph are imported, you can use any of the Excel drawing tools to enhance them as long as you save the worksheet in Excel format.

Underlining

When you open a Lotus 1­2­3 worksheet that contains double underlines, the double underlines are converted to single underlines in Excel. Many underlining tasks, such as double underlining or single underlining at the bottom of summed columns, are achieved in Excel with the text underlining options on the Font tab or the cell border options on the Border tab in the Cells (Windows) or Format Cells (Macintosh) dialog box (Format menu).

  Replacing the Lotus 1­2­3 System Command (Windows only)

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

To activate the command prompt in Windows 95 or Windows NT Workstation 4.0

  • Click the Start menu, point to Programs, and then click MS­DOS Prompt.

To activate the command prompt in Windows NT Workstation 3.51

  • In the Main program group, double­click the Command Prompt icon.

Auditing Converted Worksheets

Audits conducted by the industry on corporate MS­DOS­based 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­2­3 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­2­3 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­2­3 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

  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.

To select 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.

To 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­2­3­style 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 1­2­3 Formulas in Microsoft Excel

Most Lotus 1­2­3 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 1­2­3 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 1­2­3 Nested Formulas

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

@VLOOKUP(a1,table,1)

where table is a range name that refers to the following two­column 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 1­2­3 Functions

Most Lotus 1­2­3 functions have an equivalent in Excel. When transition formula entry is activated, you can type most Lotus 1­2­3 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 1­2­3 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 1­2­3 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 1­2­3. These functions are listed alphabetically in the following table, along with their Excel equivalents.

This Lotus 1­2­3 function Corresponds to this Excel function Comments
@@INDIRECT
@ABSABS
@ACOSACOS
@ASINASIN
@ATANATAN
@ATAN2ATAN2
@AVGAVERAGE
@CELLCELL
@CELLPOINTERCELL In Excel, when you use the CELL function without a second argument, it returns information about the current selection.
@CHARCHAR
@CHOOSECHOOSE
@CLEANCLEAN
@CODECODE
@COLSCOLUMNS
@COSCOS
@COUNTCOUNTA
@CTERMNPER In Excel the NPER function requires periodic payment instead of future value.
@DATEDATE
@DATEVALUEDATEVALUE
@DAVGDAVERAGE
@DAYDAY
@D360DAYS360 The @D360 function is available in Lotus 1­2­3 Release 3 and later.
@DCOUNTDCOUNTA
@DDBDDB
@DGETDGET
@DMAXDMAX
@DMINDMIN
@DSTDDSTDEVP
@DSTDSDSTDEV The @DSTDS function is available in Lotus 1­2­3 Release 3 and later.
@DSUMDSUM
@DVARDVARP
@DVARSDVAR The @DVARS is available in Lotus 1­2­3 Release 3 and later.
@ERRNone In Excel no equivalent is necessary, because Excel lets you type error values directly into cells and formulas.
@EXACTEXACT
@EXPEXP
@FALSEFALSE
@FINDFIND
@FVFV
@HLOOKUPHLOOKUP In Excel turn on transition formula evaluation to use the Lotus 1­2­3 evaluation rules.
@HOURHOUR
@IFIF In Excel the last two arguments of the IF function can be any value, not just numbers or strings, as in @IF.
@INDEXINDEX In Excel the INDEX function also has a form for selecting values from an array.
@INTTRUNC or INT
@IRRIRR In Excel the arguments are given in reverse order.
@ISERRISERR The ISERR function detects any of six Excel error values.
@ISNAISNA
@ISNUMBERISNONTEXT or ISNUMBER
@ISRANGEISREF The @ISRANGE function is available in Lotus 1­2­3 Release 3 and later.
@ISSTRINGISTEXT
@LEFTLEFT
@LENGTHLEN
@LNLN
@LOWERLOWER
@LOGLOG
@MAXMAX or MAXA
@MIDMID
@MINMIN or MINA
@MINUTEMINUTE
@MODMOD In Excel turn on transition formula evaluation to use the Lotus 1­2­3 evaluation rules.
@MONTHMONTH
@NN
@NANA
@NOWNOW
@NPVNPV
@PIPI
@PMTPMT The arguments for the PMT function are in different order than they are in @PMT.
@PROPERPROPER
@PVPV The arguments for the PV function are in different order than they are in @PV.
@RANDRAND In Excel the RAND function calculates values randomly each time it is recalculated; @RAND calculates the same values in each work session.
@RATERATE The arguments for the RATE function are in different order than they are in @RATE.
@REPEATREPT
@REPLACEREPLACE
@RIGHTRIGHT
@ROUNDROUND
@ROWSROWS
@ST
@SECONDSECOND
@SINSIN
@SLNSLN
@SQRTSQRT
@STDSTDEVP or STDEVPA
@STDEVSTDEV or STDEVA
@STDEVPSTDEVP
@STDSSTDEV or STDEVPA The @STDS function is available Lotus 1­2­3 Release 3 and later.
@STRINGFIXED
@SUMSUM
@SYDSYD
@TANTAN
@TERMNPER The arguments for the NPER function are in different order than they are in @TERM.
@TIMETIME
@TIMEVALUETIMEVALUE
@TODAYTODAY
@TRIMTRIM
@TRUETRUE
@UPPERUPPER
@VALUEVALUE
@VARVARA
@VARPVARPA
@VARSVAR or VARA The @VARS function is available in Lotus 1­2­3 Release 3 and later.
@VDBVDB
@VLOOKUPVLOOKUP In Excel turn on transition formula evaluation to use the Lotus 1­2­3 evaluation rules.
@YEARYEAR

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 1­2­3 Pure Functions

Lotus 1­2­3 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 1­2­3 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 1­2­3 and Microsoft Excel

The following table shows the mathematical operators used by Excel and Lotus 1­2­3, in descending order of evaluation.

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

=–(2^4)

Using Three­dimensional Formulas in Workbooks

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.

Using Equivalents for the Lotus 1­2­3 Data Distribution Command

There is no equivalent command in Excel for the Lotus 1­2­3 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 add­in. To do this task, click Add­Ins on the Tools menu, and then select the Analysis ToolPak check box. If the Analysis ToolPak option does not appear in the Add­Ins dialog box, rerun Setup and click Add/Remove. Under the Microsoft Excel option, select the Add­Ins option, and then select the Analysis ToolPak option.

Using Equivalents for Lotus 1­2­3 Data Matrix Commands

The Excel equivalents for the Lotus 1­2­3 data matrix commands are array functions, rather than commands.

This Lotus 1­2­3 data matrix command Corresponds to this Excel array function
Invert MINVERSE
Multiply MMULT

Using Equivalents for Lotus 1­2­3 Data Regression Commands

The Excel equivalents for the Lotus 1­2­3 data regression commands are array functions and do not translate directly. Instead, Excel includes the LINEST, TREND, LOGEST, and GROWTH functions for performing regression analysis.

You can also use the Data Analysis command (Tools menu) to perform regression analysis using the Regression analysis tool.

Running Lotus 1­2­3 Macros in Microsoft Excel

Most Lotus 1­2­3 users have invested time over the years building macros. These users are concerned about how Excel works with their macros. Excel includes the Macro Interpreter for Lotus 1­2­3 Users, which provides strong macro conversion support.

Using the Macro Interpreter for Lotus 1­2­3 Users

Users can run large Lotus 1­2­3 macro applications, including custom menus, without modification in Excel using the Macro Interpreter. This utility runs all Lotus 1­2­3 macros that are compatible with Lotus 1­2­3 Release 2.01.

The name assigned to a macro in Lotus 1­2­3, such as \a, is defined in Excel as a Lotus 1­2­3 macro name when you open a Lotus 1­2­3 worksheet in Excel. You can run any macro that is assigned to a macro name consisting of a backslash ( \ ) followed by a single letter. Excel assigns a lowercase letter to each macro name.

To run a Lotus 1­2­3 macro in Excel, hold down the CTRL key and press the macro letter name that is normally used with the backslash ( \ ) key in Lotus 1­2­3.

The Macro Interpreter runs all your Lotus 1­2­3 macros with the following exceptions:

  • Macros that are not compatible with Lotus 1­2­3 Release 2.01

  • Macros that contain keystrokes for menu commands available only in Release 2.2 or later

  • Macros that call a Lotus 1­2­3 add­in

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 1­2­3 add­ins, you need to remove these macro statements from your Lotus 1­2­3 macros. You can, however, call Excel procedures from Lotus 1­2­3 macros, as described later in this section.

Substituting Standard Microsoft Excel Features for Lotus 1­2­3 Macros

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

This Lotus 1­2­3 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 re­enter 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­click for best fit
Adjust multiple­column widths simultaneously Select multiple columns, and then drag the right border of a column heading or double­click 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)
UnderlineBorder 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­2­3 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 1­2­3 Release 2.2

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

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

To convert Lotus 1­2­3 MLB files

  1. In Lotus 1­2­3, load the file by attaching the add­in.

  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­2­3 WK1 format.

  4. Open the Lotus 1­2­3 worksheet in Excel.

Running Autoexec Macros

If you have a Lotus 1­2­3 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

  1. On the File menu, click Open.

  2. 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 1­2­3 \0 macro on the same worksheet, the Auto_Open macro runs first, and then the \0 macro runs.

Removing Lotus 1­2­3 Add­ins

Be sure to remove any occurrence of keystrokes or command names that attach, start, or use a Lotus 1­2­3 add­in, such as the Allways add­in and its menu structure. For example, remove statements such as /a and {app1}.

Altering Lotus1­2­3 Macros That End in a Menu

When you run a Lotus 1­2­3 macro in Excel, the Lotus 1­2­3 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 1­2­3 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 1­2­3 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 1­2­3 Macros

When you run a Lotus 1­2­3 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 1­2­3 macro. For more information, see "Microsoft Excel Transition Options" earlier in this chapter.

Adjusting Screen Size Before Running Lotus 1­2­3 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 20­row page size that is standard in Lotus 1­2­3.

Calling Microsoft Excel Procedures from Lotus 1­2­3 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 1­2­3 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 1­2­3 add­ins with calls to Excel add­ins or Visual Basic procedures. Using XLCALL, you can run an Excel procedure, after which control returns to the Lotus 1­2­3 macro. Using XLBRANCH, Excel takes over control and does not return to the original Lotus 1­2­3 macro.

With these two commands, you can rebuild part or all of complex Lotus 1­2­3 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 1­2­3 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

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

  2. To close the active workbook without saving changes, click No.

  3. On the File menu, click New to open a new workbook.

Using Lotus 1­2­3 Charts in Microsoft Excel

Excel and Lotus 1­2­3 format charts differently. This section discusses Excel equivalents for Lotus 1­2­3 chart format commands.

Lotus 1­2­3 Graph Options Format Command Equivalents

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

To 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­2­3 Graph Options Grid Command Equivalents

The Lotus 1­2­3 Graph Options Grid commands apply to all graph types with axes.

To add and delete gridlines in a chart in Excel

  1. Right­click 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­2­3 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­dimensional charts.

Lotus 1­2­3 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­2­3 Graph View command.

Lotus 1­2­3 Graph Options Color Command Equivalents

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

To 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­2­3 Graph Options Scale Command Equivalents

The following sections describe Excel equivalents for Lotus 1­2­3 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

  1. Click the x­axis (category) or y­axis (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­axis and the y­axis.

  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.

To control a chart scale manually in Excel

  1. Click the x­axis (category) or y­axis (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­axis and the y­axis.

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

  1. In the chart, click the y­axis (value).

    You can change the x­axis 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.

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

Working with Printers

This section describes differences in printer setup and printing procedures between Lotus 1­2­3 and Excel.

Lotus 1­2­3 Worksheet Global Default Printer Command Equivalents

Excel has equivalent features for most Lotus 1­2­3 global printer commands.

This Lotus 1­2­3 global printer command Corresponds to this Excel feature or action
Interface Setting up a printer and port
Auto­LF 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­Length 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 1­2­3 Line Print Command Equivalents

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

Lotus 1­2­3 Print Printer Command Equivalents

Excel has equivalent features for most Lotus 1­2­3 print printer commands.

This Lotus 1­2­3 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­Length
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.

WK3 Functions Without Excel Equivalents

A number of Lotus 1­2­3 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­dimensional 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 add­in, 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 1­2­3 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­2­3 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­2­3 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­2­3 worksheets before converting them to Excel.

Lotus 1­2­3 Releases 4 and 5 Features Without Microsoft Excel Equivalents

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

  • Range versions created with the Lotus 1­2­3 Version Manager

    No direct equivalent exists in Excel for range versions created with the Lotus 1­2­3 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­if assumptions for multiple sets of up to 32 changing cells.

  • Database records in Lotus 1­2­3 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 1­2­3

If your workgroup is upgrading gradually to Excel, some users may have to share documents with users of Lotus 1­2­3.

To save an Excel workbook in Lotus 1­2­3 format

  1. On the File menu, click Save As.

  2. In the File name (Windows ) or Save as (Macintosh) box, enter the name for the converted workbook.

  3. In the Save as type (Windows) or Save file as type (Macintosh) box, click the Lotus 1­2­3 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 1­2­3 file formats.

In Excel, save data in this file format To share data with this Lotus 1­2­3 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
WK33.0
WK3, FM33.1, 3.1+, 1­2­3/W, R1.1
WK4, WT44.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

  1. On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.

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

User\Excel\Tools_Options\Transition\Default Save

For more information, see "Using Windows System Policies to Customize Office" in Chapter 7, "Customizing and Optimizing Microsoft Office."

Linking Lotus 1­2­3 Worksheets and Microsoft Excel Worksheets

By linking cells, you can use values from a Lotus 1­2­3 worksheet without exporting the worksheet to Excel. Then, when you change the data in the Lotus 1­2­3 worksheet, the Excel worksheet is automatically updated. For example, you can link sales figures from various Lotus 1­2­3 worksheets to a single Excel worksheet and then use the formatting and printing features of Excel to create a summary report of sales.

To link a Lotus 1­2­3 worksheet to an Excel worksheet

  1. In Excel, open both the Lotus 1­2­3 worksheet and the Excel worksheet you want to link.

  2. Switch to the Lotus 1­2­3 worksheet.

  3. Select the cell or range containing the data you want to link to the Excel worksheet.

  4. On the Edit menu, click Copy.

    A moving border appears around the selected cell or range.

  5. Switch to the Excel worksheet.

  6. Select the cell or the upper­left corner of the range that you want linked to the Lotus 1­2­3 worksheet.

  7. On the Edit menu, click Paste Special.

  8. Click Paste Link.

Excel enters a formula in each cell that links the worksheets.

Note   If the linked data from Lotus 1­2­3 is pasted into more than one cell, Excel enters the formula that links the worksheets as an array formula. You cannot clear, delete, or move individual cells that contain an array formula. If you want to be able to edit individual cells, copy and link each cell individually.

You can link Microsoft Excel worksheets to files saved in any of the following Lotus 1­2­3 formats:

  • WKS

  • WK1

  • WK3

  • WK4

If you use Lotus 1­2­3 to edit a Lotus 1­2­3 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 1­2­3 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 1­2­3 Worksheets

When you switch to Excel, you might need to include data from Lotus 1­2­3 worksheets in summary reports created in Excel. In Excel you can include details from Lotus 1­2­3 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 1­2­3 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 1­2­3

When converting Excel files to Lotus 1­2­3 format, the following difficulties arise because Lotus 1­2­3 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 1­2­3.

In these cases, Excel substitutes the value of the formula for the formula itself.

When you save an Excel worksheet in a Lotus 1­2­3 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 1­2­3 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 1­2­3 Equivalents

Many Excel functions have equivalent Lotus 1­2­3 functions. However, the following Excel functions have no equivalents in Lotus 1­2­3 Release 3.1 or earlier, or Lotus 1­2­3/W Release 1.0, and generate an error message in Lotus 1­2­3.

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

Excel functions without equivalents in Lotus 1­2­3 3.1 or earlier

AREASMATCH
DOLLARMDETERM
DPRODUCTMINVERSE
FACTMIRR
FREQUENCYMMULT
GROWTHPPMT
INTPRODUCT
IPMTSEARCH
ISBLANKSUBSTITUTE
ISERRORTEXT
ISLOGICALTRANSPOSE
LINESTTREND
LOGESTTYPE
LOOKUPWEEKDAY

Using Lotus 1­2­3 Help

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

Note   The Lotus 1­2­3 Help feature also provides Help topics for users switching from Lotus 1­2­3 for MS­DOS.

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

To use Lotus 1­2­3 Help

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

  2. Under Settings, click Lotus 1­2­3 Help.

Next you need to indicate whether you want to see step­by­step instructions or demonstrations when you press Lotus 1­2­3 keys.

To see step­by­step instructions or demonstrations

  1. On the Help menu, click Lotus 1­2­3 Help.

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

The following sections describe options in the Help for Lotus 1­2­3 Users dialog box.

Menu

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

Top

Switching from Quattro Pro for MS­DOS

Excel 97 for Windows lets you use your existing Quattro Pro 4.0 for MS­DOS files while taking advantage of the ease and power of Excel. You can open Quattro Pro for MS­DOS files in Excel and run Quattro Pro macros that are compatible with Lotus 1­2­3 release 2.01.

Note   Excel cannot open Quattro Pro 5.0 for MS­DOS files. If you need to convert such a file to Excel, first save each sheet in the Quattro Pro notebook to Quattro Pro 4.0 for MS­DOS format.

Converting Quattro Pro for MS­DOS Worksheets to Microsoft Excel

The majority of your Quattro Pro for MS­DOS worksheets can be converted to Excel format by opening them and then saving them in Excel.

To open a Quattro Pro for MS­DOS worksheet in Excel

  1. On the File menu, click Open.

  2. In the Files of type box, click Quattro Pro/DOS.

  3. Below the Look in box, select the name of the worksheet.

  4. Click Open.

    Excel converts the Quattro Pro for MS­DOS worksheet and opens it.

After you convert a Quattro Pro for MS­DOS worksheet to Excel, the converted worksheet exists only in your computer's memory; the original worksheet remains unchanged. To complete the conversion, you need to save the converted worksheet in Excel format.

To save a converted Quattro Pro for MS­DOS worksheet in Excel format

  1. On the File menu, click Save As.

  2. In the File name box, enter a new name for the converted worksheet.

    This preserves the original Quattro Pro for MS­DOS worksheet as a backup.

  3. In the Save as type box, click Microsoft Excel workbook.

Sharing Documents with Quattro Pro for MS­DOS

If your workgroup is upgrading gradually to Excel 97 for Windows, some users may have to share documents with users of Quattro Pro for MS­DOS.

To save an Excel document in Quattro Pro for MS­DOS format

  1. On the File menu, click Save As.

  2. In the File name box, enter a name for the converted document.

  3. In the Save as type box, click WQ1 (Quattro Pro/DOS).

Running Quattro Pro for MS­DOS Macros in Microsoft Excel

Excel for Windows runs Quattro Pro macros that are compatible with Lotus 1­2­3 Release 2.01. For more information, see "Running Lotus 1-2-3 Macros in Microsoft Excel" earlier in this chapter.

To run a Quattro Pro macro in Excel, hold down the CTRL key and press the macro letter name that is normally used with the backslash ( \ ) key in Quattro Pro.

Top

Switching from Quattro Pro for Windows

The majority of your Quattro Pro for Windows 5.x and earlier notebooks can be converted to Excel 97 format by opening them and then saving them in Excel.

Note   The Quattro Pro for Windows file converter (Xlqpw.dll) is not installed when you choose a Typical installation during Setup. If Quattro Pro for Windows does not appear in the Open dialog box (File menu), rerun Setup and click Add/Remove. Under the Microsoft Excel option, select the Spreadsheet Converters option, and then select the Quattro Pro 1.0/5.0 (Win) option.

To open a Quattro Pro for Windows notebook in Excel

  1. On the File menu, click Open.

  2. In the Files of type box, click Quattro Pro for Windows.

  3. Below the Look in box, select the name of the worksheet.

  4. Click Open.

    Excel converts the Quattro Pro for Windows worksheet and opens it.

Note   Excel cannot open Quattro Pro version 6.0 for Windows files. If you need to convert such a file to Excel, first save it to Quattro Pro 5.0 for Windows format.

After you convert a Quattro Pro for Windows notebook to Excel, the converted notebook exists only in your computer's memory; the original notebook remains unchanged. To complete the conversion, you need to save the converted notebook in Excel workbook format.

To save a converted Quattro Pro for Windows notebook in Excel format

  1. On the File menu, click Save As.

  2. In the File name box, enter a new name for the converted notebook.

    This preserves the original Quattro Pro for Windows notebook as a backup.

  3. In the Save as type box, click Microsoft Excel workbook.

The following Quattro Pro for Windows features do not have direct equivalents in Excel and are not imported:

  • 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 1­2­3.

  • 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 what­if 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.

Top

Switching from Multiplan

To transfer Multiplan files to Excel 97 (Windows), you must first save the Multiplan worksheet in SYLK (symbolic link) format.

Converting Multiplan Files to Excel

Both Multiplan and Excel can work with SYLK files. To use your Multiplan files in Excel, first save them in SYLK format, and then you can open them in Microsoft Excel.

To open a SYLK file in Excel

  1. On the File menu, click Open.

  2. In the Files of type box, click SYLK (Symbolic Link) Files.

  3. Below the Look in box, select the name of the file.

  4. Click Open.

    Excel converts the SYLK file and opens it.

After you convert a Multiplan worksheet to Excel, the converted worksheet exists only in your computer's memory; the original worksheet remains unchanged. To complete the conversion, you need to save the converted worksheet in Excel format.

To save a converted Multiplan worksheet in Excel format

  1. On the File menu, click Save As.

  2. In the File name box, enter a new name for the converted worksheet.

    This preserves the original SYLK file as a backup.

  3. In the Save as type box, click Microsoft Excel workbook.

Sharing Documents with Multiplan

If your workgroup is upgrading gradually to Excel, some users may have to share documents with users of Multiplan. Use the SYLK format, with which both Multiplan and Excel can work.

To save the active sheet of an Excel workbook in SYLK format

  1. On the File menu, click Save As.

  2. In the File name (Windows) or Save as (Macintosh) box, enter a name for the converted workbook.

  3. In the Save as type (Windows) or Save file as type (Macintosh) box, click SYLK (Symbolic Link).

    Excel saves only the active sheet. To save other sheets in the workbook, repeat this procedure for each sheet.

To save the active sheet of an Excel workbook as an Excel 2.1 worksheet

  1. On the File menu, click Save As.

  2. In the File name box, enter a name for the converted workbook.

  3. In the Save as type box, click Microsoft Excel 2.1 Worksheet.

    Excel saves only the active sheet. To save other sheets in the workbook, repeat this procedure separately for each sheet.

Using Multiplan Command Equivalents

The following tables list frequently used Multiplan commands and their equivalent commands in Excel.

Multiplan Format Options 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
Commas (Multiplan version 3.04 and earlier) Number tab in the Cells dialog box (Format menu)
Decimal separator (Multiplan version 4.0 and later) Changing the decimal separator
Error messages (Multiplan version 4.0 and later) No equivalent
FormulasView tab in the Options dialog box (Tools menu)

Multiplan Selection Command Equivalents

The following table describes the equivalent Excel features or actions for Multiplan options.

This Multiplan key Corresponds to this Excel feature or action
F1, SEMICOLON (;) or CTRL+W On the Window menu, select the number of the window you want.
F2 or CTRL+FWith worksheet protection turned on, press TAB.
SHIFT+F2 or CTRL+R, CTRL+F With worksheet protection turned on, press SHIFT+TAB.
F4 or EXCLAMATION point (!) F9, or the Calc Now button on the Calculation tab in the Options dialog box (Tools menu).
F6 or COLON (:)Extend the selection by dragging with the mouse.
ALT+H or QUESTION MARK (?) Help menu.

Multiplan Cell Editing Command Equivalents

The following table describes the equivalent Excel features or actions for Multiplan options.

This Multiplan key Corresponds to this Excel feature or action
SHIFT+F1, SHIFT+F6 On the Window menu, select the number of the window you want.
SHIFT+F3, arrow key or @), @, arrow key Function (Insert menu).
F3, arrow key or @, arrow key Paste (Insert menu, Name submenu).
SHIFT+F5 or CTRL+T Step Macro button (Macro toolbar).
SHIFT+F9 or SHIFT+F7 or CTRL+R, CTRL+R Record New Macro and Stop Recording (Tools menu, Record Macro submenu).

Multiplan Option 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
RecalcCalculation tab in the Options dialog box (Tools menu).
IterationCalculation tab in the Options dialog box (Tools menu).
Test atCalculation tab in the Options dialog box (Tools menu).
Alpha/valueYou don't need to specify alpha or value; Excel accepts any type of valid data in the selected cell.
LearnSelect all the cells in which you want to enter data. The ENTER, TAB, and arrow keys move the active cell within the selection.
MuteIn the Windows Control Panel, double­click the Sounds icon to manage the system sounds.
Old menus (Multiplan version 4.0 and later) No equivalent.
Hold Alpha (Multiplan version 4.0 and later) No direct equivalent; Excel is always ready to accept data in the selected cell.

Multiplan Print File Command Equivalents

There is no procedure in Excel for the Multiplan Print File command. For information about printing to a text file, see your Windows documentation.

Multiplan Print Options 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
AreaSet Print Area (File menu, Print Area submenu)
Setup (Multiplan version 3.04 and earlier) Page Setup (File menu)
FormulasView tab in the Options dialog box (Tools menu)
Row­col numbers Page Setup (File menu)
Printer (Multiplan version 4.0 and earlier) Page Setup (File menu)
Model (Multiplan version 4.0 and earlier) Page Setup (File menu)
Draft (Multiplan version 4.0 and earlier) Page Setup (File menu)
Number of copies (Multiplan version 4.2) Print (File menu)

Multiplan Run Command Equivalents

There is no direct equivalent to the Multiplan Run command in Excel, but you can activate the command prompt from Start menu.

To activate the command prompt in Windows 95 or Windows NT Workstation 4.0

  • Click Start menu, point to Programs, and then click MS­DOS Prompt.

To activate MS­DOS on the Windows NT 3.51 operating system

  • In the Main program group, double­click 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
Cross­refNo direct equivalent. You can trace formula precedents and dependents using the Auditing submenu commands (Tools menu).
NamesTo 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.
SummaryNo 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.)

Top

Switching from Microsoft Works

Excel can open worksheets created in earlier versions of Works (version 2.x and earlier) for Windows or for the Macintosh. Later versions of Works (version 3.x and later) can save worksheets in Excel format.

Converting Microsoft Works Files to Microsoft Excel

The majority of your Works 3.x and earlier worksheets can be converted to Excel format by opening them and then saving them in Excel.

To open a Works worksheet file in Excel

  1. On the File menu, click Open.

  2. In the Files of type (Windows) or List files of type (Macintosh) box, select a file format.

  3. Below the Look in box, select the name of the worksheet.

  4. Click Open.

    Excel converts the Works worksheet and opens it.

  Tip   To import worksheets from Works 4.0 for Windows, first save the worksheets in Excel SS format in Works. The Excel SS format is the same as Excel 4.0 format, which Excel 97 (Windows) can open.

  Tip   To import worksheets from Works 4.0 for the Macintosh, first save the worksheets in Excel 5.0 format in Works.

After you convert a Works worksheet to Excel, the converted worksheet exists only in your computer's memory; the original worksheet remains unchanged. To complete the conversion, you need to save the converted worksheet in Excel format.

To save a converted Works worksheet in Excel format

  1. On the File menu, click Save As.

  2. In the File name (Windows) or Save as (Macintosh) box, enter a new name for the converted worksheet.

    This step preserves the original Works worksheet as a backup.

  3. In the Save as type (Windows) or Save file as type (Macintosh) box, click Excel workbook.

Sharing Documents with Microsoft Works

If your workgroup is upgrading gradually to Excel, some users may have to share documents with users of Works.

To save the active sheet of an Excel workbook in Works format

  1. On the File menu, click Save As.

  2. In the Save as type (Windows) or Save file as type (Macintosh) box, click WKS (1­2­3).

  3. In the File name (Windows) or Save as (Macintosh) box, enter a name for the converted worksheet.

    Excel saves only the active sheet. To save other sheets in the workbook, repeat this procedure separately for each sheet.



Top
Contents | << Previous | Next >> | Index