Chapter 18 - Switching to MS Excel

Article
02/20/2014

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

For a summary of new and improved features in Excel 97 (Windows) and Excel 98 (Macintosh), see Chapter 2, "What's New in Microsoft Office."
For information about installing Excel or other Microsoft Office applications, see Chapter 4, "Installing Microsoft Office."
For information about upgrading to Excel 97 and 98 from a previous version of Excel, see Chapter 12, "Upgrading from Previous Versions of Microsoft Excel."

Converting File Formats in MS 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 MS 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

On the File menu, click Open.
In the Files of type (Windows) or List files of type (Macintosh) box, click All Files.
Below the Look in (Windows) or Select a document (Macintosh) box, select the name of the document.
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 that 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

On the File menu, click Save As.
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

On the Tools menu, point to Wizard, and then click File Conversion.
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

On the File menu, click Open.
In the Files of type (Windows) or List files of type (Macintosh) box, click the document's original format.
Below the Look in (Windows) or Select a document (Macintosh) box, select the text file you want to convert.
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 MS 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

On the File menu, click Save As.
In the File name (Windows) or Save as (Macintosh) box, enter a name for the converted workbook.
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

On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.
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
Template	Template
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)

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

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

On the Tools menu, click Options, and then click the Transition tab.
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 UP	Goes to the next sheet in a workbook
CTRL+PAGE DOWN	Goes to the previous sheet in a workbook
HOME	Selects the cell in the upper-left corner of the sheet
F5	Activates 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

On the Tools menu, click Options, and then click the Transition tab.
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

On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.
Under Sheet Options, select the Transition formula evaluation check box.

The following table compares expressions that are evaluated differently in Excel and Lotus 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 type	Non-A functions	A functions
Numbers	Treated normally	Treated normally
Text	Text is ignored	Text has a value of 0
TRUE Logical Value	TRUE is ignored	TRUE has a value of 1
FALSE Logical Value	FALSE is ignored	FALSE has a value of 0

Tools and Utilities The Office Resource Kit Tools and Utilities include the Office 97 Resource Kit Help file, a collection of Knowledge Base articles about Office 97 written by Microsoft Technical Support. The article XL97: New A Functions in Microsoft Excel 97 (ID: 156445) 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
@Function	Function
Address	Reference
Anchor cell	Selecting a range of cells
Border	Row and column headings
CALC indicator	Status bar
Cell pointer	Active cell
Column labels	Column headings
Command prompt	Dialog box
Control panel	Menu bar, formula bar, status bar
Copy	Copy and paste
Crosshatching	Chart patterns
Current cell	Active cell
Current worksheet	Active worksheet or chart or macro sheet
Data labels	Data marker labels
Data range	Data series
Data table 1	One-input table
Data table 2	Two-input table
Date format	Number format
Erase	Clear
Formula criteria	Computed criteria
Global	Workspace
Graph	Chart
Graph labels	Chart text
Graph titles	Chart titles
Highlight	Select or selection
Indicator	Status bar
Input range	Database range
Label	Text
Label-prefix	Alignment
Label/matching criteria	Comparison criteria
Logical 0	FALSE
Logical 1	TRUE
Menu pointer	Menu selection
Mode indicator	Status bar
Move	Cut and paste
Number/matching criteria	Comparison criteria
Numeric format	Number format
Output range	Extract range
Picture file	Chart document
Pointer movement keys	Arrow keys
Print range	Print area
PrintGraph	Printing a chart
Prompt	Dialog box
Protected cell	Locked/protected cell
Range highlight	Selected range
Repeating label	Fill alignment
Retrieve a file	Open a file
Row numbers	Row headings
Stacked bar graph	Column chart, bar chart
Status indicator, status line	Status bar
String	Text
Target cell	Dependent cell
Target file	Dependent document
Time format	Number format
Titles	Split worksheet window with frozen panes
Translate utility	Open and Save As (File menu)
Value	Number
Window	Multiple windows, pane

Microsoft Excel Equivalents for Lotus 1-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
F1	F1	Help
F2	F2	Edit
F3	F3	Name
F4	F4	Switches between absolute and relative formula references
F5	F5	Go To
F6	F6	Next pane (if panes are not frozen)
F8	Performed automatically	Table
F9	F9	Calculate
F10	F11 or ALT+F1	Graph

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 ARROW	CTRL+UP ARROW or END+UP ARROW
END, DOWN ARROW	CTRL+DOWN ARROW or END+DOWN ARROW
END, LEFT ARROW	CTRL+LEFT ARROW or END+LEFT ARROW
END, RIGHT ARROW	CTRL+RIGHT ARROW or END+RIGHT ARROW
HOME	CTRL+HOME
TAB	ALT+PAGE DOWN
SHIFT+TAB	ALT+PAGE UP
PAGE UP	PAGE UP
PAGE DOWN	PAGE DOWN

Converting Lotus 1-2-3 Worksheets to MS 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, 1A	WKS
2.0, 2.01, 2.2	WK1, ALL
2.3, 2.4	WK1, FMT
3.0	WK3
3.1, 3.1+, 1-2-3/W, R1.1	WK3, FM3
4.0, 5.0	WK4

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

On the File menu, click Open.
In the Files of type (Windows) or List files of type (Macintosh) box, click Lotus 1-2-3 Files.
Below the Look in (Windows) or Select a document (Macintosh) box, select the name of the worksheet.
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

On the File menu, click Save As.
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.
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 Microsoft 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

Select the cells you want to format.
On the Format menu, click Cells, and then click the Number tab.
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
Fixed	Number	Default settings.
Scientific	Scientific	Default settings.
Currency	Currency	In the Negative numbers box, select ($1,234.10).
Comma	Number	Select the Use 1000 Separator (,) check box.
General	General	None.
+/-	No equivalent	None.
Percent	Percentage	Default settings.
Date	Date	Under Type, select 04-Mar-97.
Time	Time	Under Type, select 1:30:55 PM.
Text	Text	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.
Hidden	Custom	In the Type box, type ;;; Or click Row or Column on the Format menu, and then click Hide to hide entire rows or columns.
Reset	General	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

On the Edit menu, click Find.
In the Look in box, click Comments.
In the Find what box, type formula failed to convert
Click Find Next.

The first cell containing the text you entered is selected. A message appears if the text cannot be found.
Click the Find Next button again to go to the next cell with a comment containing the text.

After you have converted the worksheet, you can select all cells with cell comments.

To select all cells with cell comments

On the Edit menu, click Go To.
Click Special, and then click Comments.

This selects all cells with comments, allowing you to see where your formulas did not convert.

You can also print the comments along with the sheet and then use this printed document as a reference for troubleshooting.

To print cell comments

On the File menu, click Page Setup, and then click the Sheet tab.
Under Print, select the option you want in the Comments box.

Cell comments consist of all comments inserted by Excel during the conversion process, as well as all cells converted from WK3 files that contain Lotus 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.

Cc749833.01802(en-us,TechNet.10).gif

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
@ABS	ABS
@ACOS	ACOS
@ASIN	ASIN
@ATAN	ATAN
@ATAN2	ATAN2
@AVG	AVERAGE
@CELL	CELL
@CELLPOINTER	CELL	In Excel, when you use the CELL function without a second argument, it returns information about the current selection.
@CHAR	CHAR
@CHOOSE	CHOOSE
@CLEAN	CLEAN
@CODE	CODE
@COLS	COLUMNS
@COS	COS
@COUNT	COUNTA
@CTERM	NPER	In Excel the NPER function requires periodic payment instead of future value.
@DATE	DATE
@DATEVALUE	DATEVALUE
@DAVG	DAVERAGE
@DAY	DAY
@D360	DAYS360	The @D360 function is available in Lotus 1-2-3 Release 3 and later.
@DCOUNT	DCOUNTA
@DDB	DDB
@DGET	DGET
@DMAX	DMAX
@DMIN	DMIN
@DSTD	DSTDEVP
@DSTDS	DSTDEV	The @DSTDS function is available in Lotus 1-2-3 Release 3 and later.
@DSUM	DSUM
@DVAR	DVARP
@DVARS	DVAR	The @DVARS is available in Lotus 1-2-3 Release 3 and later.
@ERR	None	In Excel no equivalent is necessary, because Excel lets you type error values directly into cells and formulas.
@EXACT	EXACT
@EXP	EXP
@FALSE	FALSE
@FIND	FIND
@FV	FV
@HLOOKUP	HLOOKUP	In Excel, turn on transition formula evaluation to use the Lotus 1-2-3 evaluation rules.
@HOUR	HOUR
@IF	IF	In Excel the last two arguments of the IF function can be any value, not just numbers or strings, as in @IF.
@INDEX	INDEX	In Excel the INDEX function also has a form for selecting values from an array.
@INT	TRUNC or INT
@IRR	IRR	In Excel the arguments are given in reverse order.
@ISERR	ISERR	The ISERR function detects any of six Excel error values.
@ISNA	ISNA
@ISNUMBER	ISNONTEXT or ISNUMBER
@ISRANGE	ISREF	The @ISRANGE function is available in Lotus 1-2-3 Release 3 and later.
@ISSTRING	ISTEXT
@LEFT	LEFT
@LENGTH	LEN
@LN	LN
@LOWER	LOWER
@LOG	LOG
@MAX	MAX or MAXA
@MID	MID
@MIN	MIN or MINA
@MINUTE	MINUTE
@MOD	MOD	In Excel, turn on transition formula evaluation to use the Lotus 1-2-3 evaluation rules.
@MONTH	MONTH
@N	N
@NA	NA
@NOW	NOW
@NPV	NPV
@PI	PI
@PMT	PMT	The arguments for the PMT function are in different order than they are in @PMT.
@PROPER	PROPER
@PV	PV	The arguments for the PV function are in different order than they are in @PV.
@RAND	RAND	In Excel the RAND function calculates values randomly each time it is recalculated; @RAND calculates the same values in each work session.
@RATE	RATE	The arguments for the RATE function are in different order than they are in @RATE.
@REPEAT	REPT
@REPLACE	REPLACE
@RIGHT	RIGHT
@ROUND	ROUND
@ROWS	ROWS
@S	T
@SECOND	SECOND
@SIN	SIN
@SLN	SLN
@SQRT	SQRT
@STD	STDEVP or STDEVPA
@STDEV	STDEV or STDEVA
@STDEVP	STDEVP
@STDS	STDEV or STDEVPA	The @STDS function is available Lotus 1-2-3 Release 3 and later.
@STRING	FIXED
@SUM	SUM
@SYD	SYD
@TAN	TAN
@TERM	NPER	The arguments for the NPER function are in different order than they are in @TERM.
@TIME	TIME
@TIMEVALUE	TIMEVALUE
@TODAY	TODAY
@TRIM	TRIM
@TRUE	TRUE
@UPPER	UPPER
@VALUE	VALUE
@VAR	VARA
@VARP	VARPA
@VARS	VAR or VARA	The @VARS function is available in Lotus 1-2-3 Release 3 and later.
@VDB	VDB
@VLOOKUP	VLOOKUP	In Excel, turn on transition formula evaluation to use the Lotus 1-2-3 evaluation rules.
@YEAR	YEAR

Getting Help with Microsoft Excel Functions

For details about Excel functions, click the Paste Function button (Standard toolbar), select the function you want, and then click the Question Mark button. In the Office Assistant, click Help with this feature, and then click Help on selected function.

Functions are divided into categories in the Function Wizard dialog box. For example, the FREQUENCY, LINEST, LOGEST, GROWTH, and TREND functions are located in the Statistical category, while the MINVERSE and MMULT functions are located in the Math & Trig category. Help for the Analysis ToolPak is available by clicking the Help button in any Analysis ToolPak dialog box.

Translating Lotus 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 TookPak 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)
Underline	Border and Font tabs in the Cells (Windows) or Format Cells (Macintosh) dialog box (Format menu)
Shift a block of cells	Insert and Delete commands on the shortcut menu
Enter commonly used formulas	Workbook containing an Excel Visual Basic for Applications module with function procedures to share among users
Redefine and update multiple data tables (Lotus 1-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

In Lotus 1-2-3, load the file by attaching the add-in.
In the Lotus Macro Library Manager, copy the library commands to a worksheet using the Edit command.
Save the worksheet in Lotus 1-2-3 WK1 format.
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

On the File menu, click Open.
Under the Look in box, select the workbook, and then hold down SHIFT and click OK.

If you have both an Excel macro named Auto_Open that refers to a macro sheet and a Lotus 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}.

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

On the File menu, click Close.

If you made unsaved changes to the active worksheet, a dialog box appears asking if you want to save your changes.
To close the active workbook without saving changes, click No.
On the File menu, click New to open a new workbook.

Using Lotus 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

Select a data series.
On the Format menu, click Data Series, and then click the Patterns tab.
Under Line, select a line style, color, and weight.

– or –

If you do not want any lines, click None.
Under Marker, select a marker style, foreground color, and background color.

– or –

If you do not want any markers, click None.

Lotus 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

Right-click a blank area of the chart, and then click Chart Options on the shortcut menu.
Click the Gridlines tab, and select the options you want.

Lotus 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

Click the chart item you want to format.
On the Format menu, point to Selected Chart Item.
In the dialog box that appears, select the options you want.

Note The name of the Selected Chart Item command on the Format menu changes based on the chart item you select. For example, if you select a chart axis, the command Axis appears on the Format menu.

Lotus 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

Click the x-axis (category) or y-axis (value).
On the Format menu, click Selected Axis, and then click the Scale tab.

Except for xy scatter charts, the options on the Scale tab are different for the x-axis and the y-axis.
Select the Auto check box for any option you want to return to automatic.

Manual, Lower, and Upper

You can control the chart scale manually.

To control a chart scale manually in Excel

Click the x-axis (category) or y-axis (value).
On the Format menu, click 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.
Change the options you want.

Format

You can change the number format on the chart scale.

To change the number format on a chart scale in Excel

In the chart, click the y-axis (value).

You can change the x-axis number format only in xy scatter charts.
On the Format menu, click Selected Axis, and then click the Number tab.
Select the number format you want to use on the chart.

Indicator

You can display chart scale indicators.

To display or hide chart scale indicators in Excel

Select the chart.
On the Chart menu, click Chart Options, and then click the Axes tab.
Select or clear the Value (X) Axis or Value (Y) Axis check box.

Working with Printers

This section describes differences in printer setup and printing procedures between Lotus 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 Microsoft 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.

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

On the File menu, click Save As.
In the File name (Windows) or Save as (Macintosh) box, enter the name for the converted workbook.
In the Save as type (Windows) or Save file as type (Macintosh) box, click the Lotus 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
WK3	3.0
WK3, FM3	3.1, 3.1+, 1-2-3/W, R1.1
WK4, WT4	4.0, 5.0

Tip End users can specify the default format in which Excel saves new documents.

To specify the default format in which to save documents

On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.
In the Save Excel files as (Windows) or Default save as type (Macintosh) box, click the file format you want.

The next time you save a document that is not already saved in Excel format, you are prompted to save it in this format.

For more information about selecting a default format in which to save documents, see "Specifying the Default Format in Which to Save Office Documents" in Chapter 22, "Supporting Multiple Versions of Microsoft Office."

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

In Excel, open both the Lotus 1-2-3 worksheet and the Excel worksheet you want to link.
Switch to the Lotus 1-2-3 worksheet.
Select the cell or range containing the data you want to link to the Excel worksheet.
On the Edit menu, click Copy.

A moving border appears around the selected cell or range.
Switch to the Excel worksheet.
Select the cell or the upper-left corner of the range that you want linked to the Lotus 1-2-3 worksheet.
On the Edit menu, click Paste Special.
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 Excel worksheets to files saved in any of the following Lotus 1-2-3 formats:

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

AREAS	MATCH
DOLLAR	MDETERM
DPRODUCT	MINVERSE
FACT	MIRR
FREQUENCY	MMULT
GROWTH	PPMT
INT	PRODUCT
IPMT	SEARCH
ISBLANK	SUBSTITUTE
ISERROR	TEXT
ISLOGICAL	TRANSPOSE
LINEST	TREND
LOGEST	TYPE
LOOKUP	WEEKDAY

Using Lotus 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

On the Tools menu, click Options, and then click the Transition tab.
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

On the Help menu, click Lotus 1-2-3 Help.
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.

Cc749833.01803(en-us,TechNet.10).gif

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

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.

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 MS 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

On the File menu, click Open.
In the Files of type box, click Quattro Pro/DOS.
Below the Look in box, select the name of the worksheet.
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

On the File menu, click Save As.
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.
In the Save as type box, click Microsoft Excel workbook.

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

On the File menu, click Save As.
In the File name box, enter a name for the converted document.
In the Save as type box, click WQ1 (Quattro Pro/DOS).

Running Quattro Pro for MS-DOS Macros in MS 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.

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

On the File menu, click Open.
In the Files of type box, click Quattro Pro for Windows.
Below the Look in box, select the name of the worksheet.
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

On the File menu, click Save As.
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.
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.

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 MS 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 Excel.

To open a SYLK file in Excel

On the File menu, click Open.
In the Files of type box, click SYLK (Symbolic Link) Files.
Below the Look in box, select the name of the file.
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

On the File menu, click Save As.
In the File name box, enter a new name for the converted worksheet.

This preserves the original SYLK file as a backup.
In the Save as type box, click Microsoft Excel workbook.

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

On the File menu, click Save As.
In the File name (Windows) or Save as (Macintosh) box, enter a name for the converted workbook.
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

On the File menu, click Save As.
In the File name box, enter a name for the converted workbook.
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
Formulas	View 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+F	With 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
Recalc	Calculation tab in the Options dialog box (Tools menu).
Iteration	Calculation tab in the Options dialog box (Tools menu).
Test at	Calculation tab in the Options dialog box (Tools menu).
Alpha/value	You don't need to specify alpha or value; Excel accepts any type of valid data in the selected cell.
Learn	Select all the cells in which you want to enter data. The ENTER, TAB, and arrow keys move the active cell within the selection.
Mute	In 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
Area	Set Print Area (File menu, Print Area submenu)
Setup (Multiplan version 3.04 and earlier)	Page Setup (File menu)
Formulas	View 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-ref	No direct equivalent. You can trace formula precedents and dependents using the Auditing submenu commands (Tools menu).
Names	To get a list of defined names in a worksheet, select an empty area of the worksheet and click Name (Insert menu); then click Paste and click the Paste List button.
Summary	No direct equivalent. To identify conditions in a worksheet that are likely to cause errors, use the Auditing submenu commands (Tools menu).

Multiplan Transfer Clear Window Command Equivalents

There is no direct equivalent to the Multiplan Transfer Clear Window command in Excel. Instead, do one of the following:

To create a new workbook, choose New (File menu).
To close the active workbook, choose Close (File menu).
To clear cell contents from the active worksheet, select the cells you want to clear, and then press DELETE. (To select an entire worksheet, click the button located at the intersection of the row headers and column headers.)

Switching from MS 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 MS Works Files to MS 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

On the File menu, click Open.
In the Files of type (Windows) or List files of type (Macintosh) box, select a file format.
Below the Look in box, select the name of the worksheet.
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

On the File menu, click Save As.
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.
In the Save as type (Windows) or Save file as type (Macintosh) box, click Microsoft Excel workbook.

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

On the File menu, click Save As.
In the Save as type (Windows) or Save file as type (Macintosh) box, click WKS (1-2-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.

Chapter 18 - Switching to MS Excel

On This Page

Converting File Formats in MS Excel

Opening Other File Formats in MS Excel

Converting Documents from Other File Formats

To open a document in Excel

Microsoft Excel did not convert my document properly

To save a converted document in Excel format

Converting Multiple Files at One Time

To start the File Conversion Wizard

Importing Text Files

To convert a text document to Excel format

Microsoft Excel does not list the document in the Open dialog box

Saving MS Excel Workbooks in Other File Formats

To save an Excel document in another file format

To specify the default format in which to save workbooks

File Formats That Save the Entire Workbook

File Formats That Save Only the Active Sheet

Switching from Lotus 1-2-3

Using MS Excel Tools for Lotus 1-2-3 Users

Microsoft Excel Transition Options

Transition Navigation Keys (Windows Only)

Transition Formula Entry (Windows Only)

Transition Formula Evaluation

New A Functions in Microsoft Excel

Microsoft Excel Terms for Lotus 1-2-3 Users

Microsoft Excel Equivalents for Lotus 1-2-3 Commands

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

Function Keys

Navigation Keys

Converting Lotus 1-2-3 Worksheets to MS Excel

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

To open a Lotus 1-2-3 worksheet in Excel

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

Converting Multiple Lotus 1-2-3 Worksheets

Translating Lotus 1-2-3 Formatting

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

Auditing Converted Worksheets

Auditing Your Worksheets Before Conversion

Auditing Your Worksheets After Conversion

Auditing with Cell Tracers

Using Lotus 1-2-3 Formulas in Microsoft Excel

Translating Lotus 1-2-3 Nested Formulas

Translating Lotus 1-2-3 Functions

Getting Help with Microsoft Excel Functions

Translating Lotus 1-2-3 Pure Functions

Using Mathematical Operators in Lotus 1-2-3 and Microsoft Excel

Using Three-dimensional Formulas in Workbooks

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

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

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

Running Lotus 1-2-3 Macros in Microsoft Excel

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

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

Running Macros Created in Lotus 1-2-3 Release 2.2

Converting Lotus 1-2-3 Release 2.2 Macro Library Files

Running Autoexec Macros

Removing Lotus 1-2-3 Add-ins

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

Getting Help Within Macro Prompts

Verifying Options Before Running Lotus 1-2-3 Macros

Adjusting Screen Size Before Running Lotus 1-2-3 Macros

Calling Microsoft Excel Procedures from Lotus 1-2-3 Macros

Erasing the Active Worksheet and Starting a New One

Using Lotus 1-2-3 Charts in Microsoft Excel

Lotus 1-2-3 Graph Options Format Command Equivalents

Lotus 1-2-3 Graph Options Grid Command Equivalents

Lotus 1-2-3 Graph Type Command Equivalents

Lotus 1-2-3 Graph View Command Equivalents

Lotus 1-2-3 Graph Options Color Command Equivalents

Lotus 1-2-3 Graph Options Scale Command Equivalents

Working with Printers

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

Lotus 1-2-3 Line Print Command Equivalents

Lotus 1-2-3 Print Printer Command Equivalents

WK3 Functions Without Microsoft Excel Equivalents

Errors Caused by Special Characters

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

Sharing Documents with Lotus 1-2-3

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