Go To TechNet Home Page

Chapter 18: Switching to MS Excel

~Previous Chapter_____^Office Resource Kit Contents_____Next Chapter~

This chapter places particular emphasis on issues that arise when a workgroup is moving to Microsoft Excel from another spreadsheet application. This chapter is intended as a starting point to be used in the transition process, and offers guidance and information specific to that process.

Contents

Converting File Formats

Switching from Lotus 1-2-3

Borland Quattro Pro for MS-DOS Compatibility

Borland Quattro Pro for Windows Compatibility

Multiplan Compatibility

Microsoft Works Compatibility

Converting File Formats

File formats determine the way information in a workbook is stored in a file. Different spreadsheet applications use different file formats. Microsoft Excel enables you to open and save files in many different formats, using the Open and Save As commands on the File menu. This section examines these data exchange formats in detail.

Data Exchange Specifications

Microsoft Excel can both open and save many different file formats. The list of formats that appears in the Save As dialog box varies depending on what kind of sheet is active. When saving, some file formats save the entire workbook and others save only the active worksheet. (When saving the latter file type, you need to activate and save each sheet in the workbook individually.) See the following lists for a comprehensive summary of formats based on how they can be used.

Formats for Opening and Saving Files

File Formats That Save the Entire Workbook:

Note A supplemental converter exists to allow users of Microsoft Excel 5.0c for Windows (16-bit) to open, but not save, WK4 files. You can obtain this converter from the Office Resource Kit CD, or by calling Microsoft Product Support Services.

File Formats That Save the Active Sheet Only

Formats for Opening Files

File Formats That Microsoft Excel Can Open But Not Save:

Clipboard Formats

You can paste the following formats in Microsoft Excel with the Paste or Paste Special command:

* From Microsoft Excel only

Importing Text Files

If you want to open a text file in Microsoft Excel, click the Open button or choose Open from the File menu, select the filename, and click OK. The file is loaded and the Text Import Wizard appears. The Text Import Wizard guides you through the steps you need to import the text file and distribute the text across columns.

Switching from Lotus 1-2-3

All of your Lotus 1-2-3 worksheets are compatible with Microsoft Excel, allowing you to open them, work on them, and then save them either as Lotus 1&hyph;2-3 worksheets or Microsoft Excel worksheets. With Microsoft Excel for Windows 95, you can run Lotus 1-2-3 Release 2.01-compatible macros, and you can import formatting files created using Allways or the WYSIWYG add-in. You can also use the skills you developed while using Lotus 1-2-3 to help you learn Microsoft Excel for Windows. With Help for Lotus 1-2-3 Users, you can enter a Lotus 1-2-3 command and then choose either to watch as Microsoft Excel performs the equivalent Microsoft Excel commands or to read a set of detailed written instructions.

The simple act of opening a Lotus 1-2-3 worksheet into Microsoft Excel converts both formulas and formatting automatically. After the worksheet is opened (or loaded, in Lotus 1-2-3 terms), you can start working with it immediately.

Note If you open any Lotus 1-2-3 .WK? file in Microsoft Excel, the Transition Formula Evaluation option is automatically activated, which causes Microsoft Excel to use Lotus 1-2-3 rules for calculating formulas. For more information about Transition Formula Evaluation, see "Using the Transition Tab" later in this chapter.

Using Your Knowledge of Lotus 1-2-3 to Learn Microsoft Excel

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

Help for Lotus 1-2-3 Users

You can use your knowledge of 1-2-3 to learn Microsoft Excel. In the Help for Lotus 1-2-3 dialog box, you can type a 1-2-3 command and Microsoft Excel either displays a set of instructions or performs an interactive demonstration of the equivalent Microsoft Excel command. If you select the Instructions option button, the instructions for the task are placed on your worksheet so that you can view them conveniently while you finish the task.

There are two ways to open the Help For Lotus 1-2-3 Users dialog box:

• On the Help menu, click Lotus 1-2-3 Help.
• On the Tools menu, click the Options command, click the Transition tab. In the Excel Menu Or Help Key box, make sure the / (slash) character appears, and select the Lotus 1-2-3 Help option button. With these settings, pressing the SLASH key (/) displays the Help For Lotus 1-2-3 Users dialog box.

The following paragraphs describe each section of the Help for Lotus 1-2-3 Users dialog box.

Menu

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 in Help Options, as described below. For multilevel Lotus 1-2-3 menus, Microsoft Excel displays the next submenu on a status line at the bottom of the dialog box. To move down to a lower menu level, select the menu item and press ENTER or type the first letter of the menu item.

Using 1-2-3 Help

The Using 1-2-3 Help box initially displays instructions for using Help for Lotus 1-2-3 Users. If you select a Lotus 1-2-3 command, this box displays instructions for performing the equivalent command in Microsoft Excel. The title of this box changes depending on the menu item currently selected. If you select the Instructions option under Help Options and then press ENTER when the command is selected and the procedure is visible, the dialog box disappears but the procedure remains posted on your sheet for reference as you carry out the command. (This also occurs if you select the last item in a keystroke sequence, such as the "r" in \fr.)To remove the text box containing the procedure, press ESC.

Help Options

You can choose to either display a text box containing the Microsoft Excel equivalent procedure for carrying out a Lotus 1-2-3 command (the Instructions option), or to watch Microsoft Excel demonstrate the equivalent steps for you (the Demo option). To select the Instructions option with the keyboard, press ALT+I. To select the Demo option with the keyboard, press ALT+D.

For commands requiring additional information, such as cell references, you are prompted for the necessary information at the top of the Microsoft Excel window before the demonstration starts.

The Faster and Slower buttons enable you to choose from among five demonstration speeds, with 5 being the fastest and 1 being the slowest. The current speed is displayed in the box to the right of the two buttons. To select speeds with the keyboard, press ALT+F for faster, or press ALT+S for slower.

Using the Transition Tab

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

To specify how you want Microsoft Excel to operate, choose the Options command from the Tools menu and select the Transition tab. This tab contains options to help you in your transition to Microsoft Excel.

Transition Navigation Keys

Microsoft Excel for Windows provides an alternate set of keyboard commands for navigating around spreadsheets. To activate this alternate set of commands, choose the Options command from the Tools menu, select the Transition tab, and then select the Transition Navigation Keys check box. For example, in Lotus 1-2-3, pressing HOME moves the active cell highlight to cell A1. In Microsoft Excel, pressing HOME moves the active cell highlight to the first cell in the current row. When you activate the Transition Navigation Keys option, pressing HOME moves the active cell to cell A1.

The following tables list the keyboard shortcuts that are in effect when you select the Transition Navigation Keys check box.

When the Transition Navigation Keys check box is selected, use the following text-alignment prefix characters to assign alignment formats as you enter data into cells.

When in Point mode, hidden columns unhide themselves temporarily.

Transition Formula Entry

Microsoft Excel uses a different syntax than Lotus 1-2-3 in formulas and functions. Using Transition Formula Entry to learn the Microsoft Excel syntax, you can enter any formula or function exactly as you would in Lotus 1-2-3, and you will be shown how it is entered Microsoft Excel.

With the Transition tab of the Options command on the Tools menu, you can select the Transition Formula Entry check box to enter any formula or function or range name according to Lotus 1-2-3 syntax. Microsoft Excel does not automatically turn on Transition Formula Entry for Microsoft Excel worksheets or Lotus 1-2-3 worksheets.

When Transition Formula Entry is turned on, you can:

• Enter any formula or function as you would in Lotus 1-2-3. Microsoft Excel automatically translates the formula into a Microsoft 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), Microsoft 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 (the checked 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 (the checked 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 don't automatically reapply names or revert names to references. Therefore, decide whether you want to turn Transition Formula Entry on of off before you begin working on a worksheet.

After you turn on Transition Formula Entry, it remains turned on for that worksheet until you turn it off, even if you save the Lotus 1-2-3 worksheet as a Microsoft Excel worksheet.

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 created in Lotus 1-2-3 are affected by Transition Formula Entry.

Transition Formula Evaluation

Microsoft Excel and Lotus 1-2-3 evaluate certain formulas and expressions differently. The Transition Formula Evaluation option allows Microsoft Excel to calculate formulas and database criteria according to Lotus 1-2-3 rules.

The following types of expressions are evaluated differently in Excel than in Lotus:

• Cells that contain text in Lotus 1-2-3 are considered to have a value of zero (0) when the cell is used in a formula. In Microsoft Excel, you cannot combine text and numeric entries in the same formula. For example, suppose you want to calculate an average using a range of cells containing numeric entries and cells containing text. In Microsoft Excel, the cells containing text are ignored rather than included in the calcualtion as zero entries.
• Boolean expressions in Lotus 1-2-3 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. Microsoft Excel also calculates them as 0 and 1, but displays FALSE or TRUE, respectively, in the cell.
• In Microsoft 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 functions, including @MOD, @VLOOKUP, and @HLOOKUP, are evaluated differently than the equivalent Microsoft Excel functions. For example, the Lotus 1-2-3 @VLOOKUP function performs literal matches on text, whereas Microsoft Excel's 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 Microsoft Excel, Transition Formula Evaluation is turned on for that sheet. This ensures that the formulas are calculated according to the preceding Lotus 1-2-3 rules.

If your save the Lotus 1-2-3 worksheet as a Microsoft Excel worksheet, Transition Formula Evaluation remains turned on until you turn it off. Transition Formula Evaluation is not turned on automatically for Microsoft Excel worksheets.

Warning Avoid turning Transition Formula Evaluation on and off; otherwise, the values calculated on your worksheet might change. If you leave Transition Formula Evaluation turned off, your worksheet will adhere to Microsoft Excel rules. If you leave the option turned on, your worksheet will adhere to Lotus 1-2-3 rules.

Consolidating Multiple Worksheets

Using the Consolidate command, you can easily select data from other files, on your computer or on the network. The addresses of these data sources are stored in the worksheet, and you can call them whenever an update is needed by using the Consolidate command on the Data menu. You can also construct dynamic links to the source data that will 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 Microsoft 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 3-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 3-dimensional formulas to consolidate data into summary worksheets. 3-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 3-dimensional range.

While you can create summary reports for identical sheets in a workbook using the Consolidate command, 3-dimensional formulas offer several advantages. 3-dimensional formulas are dynamic. Like other Microsoft Excel formulas, the results are automatically updated to reflect changes in subordinate data. Using linked consolidation instead of 3-dimensional formulas adds an outline to the summary worksheet, inserting hidden rows and columns to store the linking formulas. 3-dimensional formulas are also easier to create and modify than linked consolidations.

Transition Terminology

The following table lists Lotus 1-2-3 terms and their Microsoft Excel counterparts. The Microsoft Excel term is not necessarily an exact equivalent of the Lotus 1-2-3 term, but rather a term you can look up in the online help system for more information. You don't need to understand all of these terms before you start using Microsoft Excel.

File Conversions

You can use Microsoft Excel with your existing Lotus 1-2-3 worksheets by simply opening your Lotus 1-2-3 worksheet in Microsoft Excel the same way you open any Microsoft Excel worksheet--using the Open toolbar button or the Open command on the File menu. When you finish editing the worksheet, you can save it as either a Microsoft Excel worksheet or a Lotus 1-2-3 worksheet.

Similarly, it's easy to save a Microsoft Excel worksheet in a Lotus 1-2-3 format and then open it in Lotus 1-2-3. After you export a Microsoft Excel worksheet to Lotus 1-2-3 format, you can edit it using Lotus 1-2-3.

When you open a WK3 file in Microsoft Excel, it is opened as a workbook. The associated FM3 formatting file is also opened and its formatting information is applied. Similarly, when you save Microsoft Excel workbooks in WK3 format, the associated FM3 file is created automatically.

Microsoft Excel opens and saves the following Lotus 1-2-3 file formats:

Note In WK3 and WK4 formats, Microsoft Excel can read and write both 2-dimensional and 3-dimensional worksheets.

When you open a Lotus 1-2-3 worksheet in Microsoft Excel, Microsoft Excel automatically opens the corresponding ALL, FMT, or FM3 file. Drop shadows and objects drawn on top of charts are not imported to Microsoft Excel. Also, double underlines and wide underlines appear as a single underline in Microsoft Excel. When you save a Lotus 1-2-3 worksheet in Microsoft 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.

For more information about Impress, see "WYSIWYG (Impress) Formatting" later in this chapter.

Unsupported Lotus 1-2-3 Release 4 and Release 5 Features

Features without direct equivalents in Microsoft Excel will not be imported:

• No direct equivalent exists in Microsoft Excel for range versions created with the Lotus 1-2-3 Version Manager. Therefore, Microsoft Excel will import only the data from the version that is currently displayed (the last time the file was saved). However, you can use the Microsoft 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 are not compatible with either ODBC or Microsoft Query, so they are not imported.
• Embedded OLE objects are not imported. This includes the Lotus Maps objects from Release 5.
• Rotated text or drawing objects are imported, but displayed with normal horizontal alignment.
• Designer Frames are not imported.
• For gradient fills, the cell or object is formatted using only the primary color from the fill.

Importing and Exporting References

When exporting Microsoft Excel files to Lotus 1-2-3, the following occur due to the lack of equivalent functionality in Lotus 1-2-3.

• Microsoft 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.
• The Microsoft Excel intersection (blank space) and union (comma) operators are not exported to Lotus 1-2-3.

In these cases, Microsoft Excel substitutes the value of the formula for the formula itself. When you save a document in a Lotus 1-2-3 file format, any references in a Microsoft Excel worksheet to rows beyond 2048 (for WKS file format) or 8192 (for WK1 and WK3 file formats) wrap around the edges of the worksheet. For example, when you save a reference to cell A8193 in WK1 format, the reference changes to A1 in Microsoft Excel.

Importing and Exporting External References

Lotus 1-2-3 Releases 1a and 2.01 don't support external references (file linking) to other worksheets, so any Microsoft Excel formulas containing external references are not exported to WKS or WK1 file formats. In these cases, Microsoft Excel substitutes the value of the formula for the formula itself.

Opening and Saving a Lotus 1-2-3 Worksheet

The majority of your Lotus 1-2-3 worksheets can easily be converted to Microsoft Excel format by opening them and saving them with Microsoft Excel.

To open and save a Lotus 1-2-3 worksheet in Microsoft Excel

1. In Microsoft Excel, click the Open button on the Standard toolbar or choose the Open command from the File menu.
2. In the Files Of Type list box, select Lotus 1-2-3 Files.
3. Switch to the appropriate folder and select the Lotus 1-2-3 worksheet that you want to convert, or type the complete filename in the File Name box.

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

4. On the File menu, click Save As to save each converted Lotus 1-2-3 worksheet as a Microsoft Excel workbook. Make sure the Microsoft Excel Workbook option is selected in the Save As Type list box.

If you have any formulas in cells that cannot be converted, Microsoft Excel displays another dialog box asking you if it should continue to alert you each time a cell does not convert. Microsoft Excel attaches a note to any cell that does not convert. Formulas that do not convert are discarded, but the result of the formula is preserved in the cell. Keep a copy of the original Lotus 1-2-3 worksheet in case there are formulas that do not convert, so that you can refer to the original Lotus 1-2-3 formula in order to rebuild it using the equivalent Microsoft Excel method. The most common conversion problems are formulas that have over eight levels of nesting in one formula, as is common with formulas that use multiple @IF functions. (For more information, see "Nested Formulas" later in this chapter.)

Auditing Your Lotus 1-2-3 Worksheet Before Conversion

Audits conducted by the industry on corporate MS-DOS--based worksheets 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 Microsoft Excel. We recommend a partial audit on both sides. Each audit catches different problems. Auditing before conversion catches problems inherent to the original worksheet, such as values that have replaced formulas, circular errors, error results, and bad range names. Auditing after conversion catches problems introduced by the conversion process or by reorganization and linking.

Auditing Your Converted Worksheet

Auditing the worksheets in Microsoft Excel helps you find formulas that did not convert, links that are incorrect, or unexpected problems for which you might need additional help. The three Microsoft Excel features that are useful for auditing are the Auditing commands on the Tools menu, the Find command on the Edit menu, and the Special button in the dialog box that appears when you choose the Go To command on the Edit menu.

Note If Microsoft Excel encounters formulas that it cannot convert when you open a Lotus 1-2-3 worksheet, only the resulting values are displayed and the original formula is discarded. Microsoft Excel indicates this by displaying a cell note (and cell note indicator) in the cell, containing the message "Formula failed to convert." For more information, see "Translating Lotus 1-2-3 Formulas," later in this chapter.

To search for cells containing formulas that did not convert correctly

1. On the Edit menu, click Find.
2. In the Find dialog box, select Notes from the Look In list.
3. In the Find What box, type formula failed to convert
4. Choose the Find Next button.

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

5. Choose the Find Next button again to go to the next cell with a note containing the text.

To select all cells with cell notes after you've loaded the file, click the Go To command on the Edit menu, click the Special button, and then select the Notes option button. This will select all cells with notes, allowing you to see where your formulas did not convert. You can also click the Page Setup command on the File menu, click the Sheet tab, and then select the Notes check box to print the notes along with the sheet. You can then use this printed document as a reference for troubleshooting. These cell notes will consist of all notes inserted by Microsoft 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 Go To Special dialog box is a powerful tool. Using various Go To Special options, you can find:

• Cells supplying values to the active cell (Precedents option).
• Cells that use the value in the active cell (Dependents option).
• Cells containing only values (Constants option).
• Cells containing only formulas (Formulas option).
• Cells containing error values (Errors option).
• Cells containing notes (Notes option).
• Cells containing different reference patterns in a row or column (Row Differences or Column Differences options).

Auditing Using Cell Tracers

Another powerful auditing feature included with Microsoft Excel are 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. You use the Auditing command on the Tools menu to display tracer arrows. Alternatively, you can choose the Auditing command and then choose the Show Auditing Toolbar command to display the Auditing toolbar, which you can use to trace the flow of data between cells on your worksheet.

In the following figure, cell J8 was selected and the Trace Dependents button 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.

Microsoft Excel Equivalents for Frequently Used Lotus 1-2-3 Commands

The following table lists Lotus 1-2-3 commands and the equivalent commands in Microsoft Excel.

Note In Microsoft Excel you select the range you want to work with before you choose a command. Many common commands can be carried out by clicking a button on a toolbar.

Lotus 1-2-3 Keyboard Equivalents

Microsoft Excel default keyboard equivalents to Lotus 1-2-3 keys are listed in the following tables. If you have not activated the Transition Navigation Keys option, these Lotus 1-2-3 keyboard equivalents will be in effect.

Function Keys

The following table compares function key assignemnts in Lotus 1-2-3 and their equivalents in Microsft Excel.

Navigation Keys

The default Microsoft Excel navigation keys are listed below. You can also turn on Microsoft Excel's transition navigation keys, which are equivalent to many of the Lotus 1-2-3 navigation keys.

Translating Lotus 1-2-3 Formatting

Many Lotus 1-2-3 users have used add-ins to assist in the formatting of their worksheets. Lotus 1-2-3 Release 2.01 and 2.2 use the Allways add-in (which produces files with filenames that end with the .all extension), while Releases 2.3 and later use the WYSIWYG or Impress add-in (which produces files with filenames that end with .fmt or .fm3). In Lotus 1-2-3, after formatting is applied to a worksheet, a separate file is created and saved along with the worksheet. In Microsoft Excel, formatting information is saved in the workbook file.

WYSIWYG (Impress) Formatting

When you open a Lotus 1-2-3 worksheet that has a corresponding FMT (WK1) or FM3 (WK3) file created with the WYSIWYG add-in in the same folder, Microsoft Excel opens the WYSIWYG file automatically and applies the equivalent formats in Microsoft Excel. Conversely, when you use Microsoft 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. You can also create an FMT or FM3 file when you save a WK1 or WK3 file, even if none existed originally.

Allways Formatting

Microsoft Excel offers full support of Allways files (.all). When you import a Lotus 1-2-3 worksheet that has a corresponding ALL file (with the same name, in the same folder), Microsoft Excel opens the Allways file automatically. When you use Microsoft Excel to save the same Lotus 1-2-3 file, Microsoft Excel will save 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.

Underlining

When you open in Microsoft Excel a Lotus 1-2-3 worksheet that contains double underlines, the double underlines are converted to single underlines. Many underlining tasks, such as double underlining or single underlining at the bottom of summed columns, will be better accomplished in Microsoft Excel with the Cells command on the Format menu, using the text underlining options in the Font tab or the cell border options in the Border tab.

Objects Drawn Over WYSIWYG Graphs

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

Formatting in Microsoft Excel

This section describes the general procedure used to apply formatting in Microsoft Excel, and describes how formatting in Microsoft Excel equates to formatting in Lotus 1-2-3.

To format numbers

1. Select the cells you want to format.
2. On the Format menu, click Cells.
3. Select the Number tab.
4. Select a format.

   Refer to the table below for Microsoft Excel equivalents to Lotus 1-2-3 formats.

   You can create other fixed decimal number formats by editing the displayed format in the Code box or by typing an entirely new one.

5. Click OK.

You use the same general procedure as described above to apply other types of formatting, using the other tabs in the Cells dialog box.

The following table lists Microsoft Excel equivalents to Lotus 1-2-3- formats, as shown on the Number tab in the Cells dialog box on the Format menu. The Microsoft Excel column indicates the category and selected options of the format.

Translating Lotus 1-2-3 Formulas

Most Lotus 1-2-3 formulas and functions will convert to their Microsoft Excel equivalents when the worksheet is opened. If Microsoft Excel finds formulas that it cannot convert when you open a Lotus 1-2-3 worksheet, then only the resulting values will be present and the original formula will be lost. Microsoft Excel indicates this in two ways: first, a message that notifies the user that Excel cannot read the record appears when a formula is encountered that it cannot convert, and second, a cell note (and cell note indicator) appears in the cell, containing the message "Formula failed to convert."

Note For more information about converting formulas, see "Transition Formula Entry" and Transition Formula Evaluation" earlier in this chapter.

Nested Formulas

Perhaps the most common reason that the "Cannot read record" message appears when loading Lotus 1-2-3 files is that a formula in your Lotus 1-2-3 worksheet uses more than eight levels of nesting. To get around this, you can break the formula in sections of less than eight nested segments each 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 workaround is to create a formula using @VLOOKUP and refer to a table on the side. Then there is no nesting 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)))))))))

The above formula has eight levels of nesting. To fix the problem, 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 load the file in Microsoft Excel, it will convert without problems, and the formula will work properly (the offset argument 1 will be automatically converted to 2, because Microsoft 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.

Lotus 1-2-3 Functions

Most Lotus 1-2-3 functions have an equivalent in Microsoft 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 Microsoft Excel equivalents. The functions you cannot enter in this way are specific to Lotus 1-2-3 Release 3 and later. To turn on Transition Formula Entry, select the Transition tab of the Options command on the Tools menu. Then select the Transition Formula Entry check box under Sheet Options.

Microsoft Excel uses different rules than Lotus 1-2-3 does 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, and are listed in the following table. To turn on Transition Formula Evaluation, select the Transition tab in the Options dialog box. See also "Entering Lotus 1-2-3 Formulas," earlier in this chapter.

The following table is an alphabetic list of Lotus 1-2-3 functions that have Microsoft Excel equivalents. For a list of Microsoft Excel functions without Lotus 1-2-3 equivalents, see the following section.

Excel Functions Without Lotus 1-2-3 Equivalents

Many Microsoft Excel functions are equivalent to Lotus 1-2-3 functions. However, The following Microsoft Excel functions have no equivalents in Lotus 1-2-3 Release 3.1 or earlier, or Lotus 1-2-3/W Release 1.0. Microsoft 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.

WK3 Functions That Do Not Convert

A number of WK3 functions--nonaggregate functions that use 3-dimensional references--will not properly convert. An aggregate function is one that is always used with a range of values, such as SUM, AVERAGE, MIN, and MAX. A nonaggregate function is one that is not commonly used with a range of values. For example, the nonaggregate @INDEX function will not convert if it uses references that encompass more than one ply of a 3-dimensional worksheet. (It converts properly if no 3-dimensional reference is used.) Unsupported WK3 functions are shown in the following list.. These functions are unsupported when they include a 3-dimensional argument, so if a normal argument is used, it may work.

Other Functions That Present Conversion Problems

Microsoft 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 arguement.

Invalid Characters Converted to Underscores

Microsoft Excel converts invalid characters in names to the underscore character ( _ ) when reading Lotus 1-2-3 worksheets. Microsoft Excel notifies the user that Excel 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. Because Microsoft Excel doesn't allow all of the special characters in names that Lotus 1-2-3 does, names that contain different special characters but are otherwise identical will be converted to an identical name, resulting in an error. Besides letters and numbers, Microsoft Excel allows only underscore and backslash (\) characters to be used in names.

For example, if you have defined the names TOTAL$ and TOTAL# on a Lotus 1-2-3 worksheet and you open the worksheet in Microsoft Excel, both names will be converted to TOTAL_ (underscore). This results in two different ranges defined with the same name. You can work around this by checking for invalid characters in your Lotus 1-2-3 worksheets before converting them to Microsoft Excel.

Lotus 1-2-3 System Command

There is no direct equivalent for the Lotus 1-2-3 System command in Microsoft Excel, but you can activate MS-DOS from the Microsoft Windows Start menu (or the Windows NT Program Manager).

To activate MS-DOS

• Click the Windows Start button, point to Programs, and then click MS-DOS Prompt.

Lotus 1-2-3 Data Distribution Command

There is no equivalent command in Microsoft Excel for the Lotus 1-2-3 Data Distribution command. Instead, Microsoft Excel provides the FREQUENCY function, which calculates a data distribution. You can also choose the Data Analysis command on the Tools menu and select the Histogram tool to calculate frequencies for a range of data.

Note If the Data Analysis command does not appear on the Tools menu, you need to install the Analysis ToolPak add-in. To do so, click the Add-Ins command on the Tools menu, and then click the Analysis ToolPak option. If the Analysis ToolPak option does not appear in the Add-ins dialog box, you need to rerun Setup to install it. For information about Setup, see Chapter 8, "Installing Microsoft Office."

Lotus 1-2-3 Data Matrix Commands

The Microsoft Excel equivalents to the Data Matrix commands are array functions, rather than commands.

Lotus 1-2-3 Data Regression Commands

The Microsoft Excel equivalents to the Data Regression commands are array functions rather than commands and do not translate directly. Instead, Microsoft Excel includes the LINEST, TREND, LOGEST, and GROWTH functions for performing regression analysis.

You can also choose the Data Analysis command on the Tools menu and select the Regression tool to perform regression analysis.

Note If the Data Analysis command does not appear on the Tools menu, you need to install the Analysis ToolPak add-in. To do so, choose the Add-Ins command on the Tools menu and click the Analysis ToolPak option. If the Analysis ToolPak option does not appear in the Add-ins dialog box, you need to rerun Setup to install it. For information about Setup, see Chapter 8, "Installing Microsoft Office."

Getting Help About Microsoft Excel Functions

For details about Microsoft Excel functions, click the Function Wizard button on the Standard toolbar, select the function you want, and click the Help button. 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.

Mathematical Operator Evaluation in Lotus 1-2-3 and Microsoft Excel

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

In Lotus 1-2-3, the exponentiation operator (^) is evaluated before the negation operator (--); in Microsoft Excel, negation is evaluated first. Thus, the formula

=--2^4

produces the value --16 in Lotus 1-2-3, and 16 in Microsoft Excel. To fix this, use parentheses to force the correct order of evaluation, for example:

=--(2^4)

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

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

To link from a Lotus 1-2-3 worksheet to a Microsoft Excel worksheet

1. In Microsoft Excel, open both the Lotus 1-2-3 worksheet and the Microsoft Excel worksheet you want to link.
2. Switch to the Lotus 1-2-3 worksheet (ALT, W, worksheet number.)
3. Select the cell or range containing the data you want to link to the Microsoft Excel worksheet (SHIFT+arrow keys).
4. On the Edit Menu or the shortcut menu, click Copy (ALT, E, C or CTRL+C).

   A moving border appears around the selected cell or range.

5. Switch to the Microsoft Excel worksheet (ALT, W, worksheet number).
6. Select the cell or the upper-left corner of the range that you want linked to the Lotus 1-2-3 worksheet (arrow keys).
7. On the Edit menu, click Paste Special (ALT, E, S)
8. Click the Paste Link button (ALT+L).

Microsoft 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, Microsoft Excel enters the formula that links the worksheets as an array formula. You cannot clear, delete, or move individual cells that contain an array formula. If you want to be able to edit individual cells, copy and link each cell individually.

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

• WKS
• WK1
• WK3
• WK4

If you use Lotus 1-2-3 to edit a Lotus 1-2-3 worksheet linked to a Microsoft Excel worksheet, the linked cells are updated when you open the Microsoft Excel worksheet.

You can use the Links command on the Edit menu to open the Lotus 1-2-3 worksheet that contains the source data for the Microsoft Excel worksheet you're editing. You can also redirect links to refer to another worksheet.

Working with Charts

Microsoft Excel and Lotus 1-2-3 take different approaches to formatting charts. This section discusses Microsoft Excel equivalents for Lotus 1-2-3 chart Format commands.

Lotus 1-2-3 Graph Options Format Commands

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 Microsoft Excel

1. Select a data series.
2. On the Format menu, click Selected Series.
3. Click the Patterns tab.
4. Under Line, select a line style, color, and weight.

   If you don't want any lines, click the None option button.

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

   If you don't want any markers, click the None option button.

Lotus 1-2-3 Graph Options Grid Commands

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

To add and delete axes in a chart in Microsoft Excel

1. On the Insert menu, click Axes.
2. Turn axes off or on.

Lotus 1-2-3 Graph Type Commands

In Microsoft Excel, you change the chart (graph) type after you create the chart (graph). Use the Chart Type command on the Format menu when a chart window is active. It also offers additional chart types, such as 3-dimensional charts.

Lotus 1-2-3 Graph View Command

After you create a chart (graph) in Microsoft Excel, it remains visible on a worksheet as a embedded chart, or as a separate chart sheet in the workbook. Therefore, the procedure for creating a chart in Microsft Excel is the closest equivalent to the Lotus 1-2-3 Graph View command.

Lotus 1-2-3 Graph Options Color Command

There is no direct equivalent in Microsoft Excel for the Lotus 1-2-3 Graph Options Color command. However, you can change the color of individual chart items by clicking the chart item you want to format, clicking the Format menu and pointing to the Selected (Item) command on the Format menu. Note that the name of the command changes, depending on the chart item selected.

Lotus 1-2-3 Graph Options Scale Commands

This section discusses Microsoft Excel equivalents for the Scale commands in Lotus 1-2-3.

Auto

Microsoft Excel creates the scale automatically. If you designated any aspect of the scale as manual, you can return it to automatic by following these steps:

To set the axis scale to automatic

1. Select the x (category) or y (value) axis.
2. On the Format menu, click the Selected Axis command.
3. Click the Scale tab.

   The options are different for the x-axis and the y-axis.

4. Select the Auto check box for any option you want to return to automatic.
5. Click OK.

Manual, Lower, Upper

To control the scale manually

1. Click the x (category) or y (value) axis.
2. On the Format menu, click the Selected Axis command.
3. Click the Scale tab.

   The options are different for the x-axis and the y-axis.

4. Change any options you want.

Format

To change the number format on the scale

1. On the source worksheet, select the numbers you want to format.
2. On the Format menu, click Cells.
3. Click the Number tab.

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

4. Click OK.

Indicator

To select or clear scale indicators

1. Select the chart.
2. On the Insert menu, click Axes.
3. Select the Category (X) Axis or Value (Y) Axis check box.
4. Click OK.

Working With Printers

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

Lotus 1-2-3 Worksheet Global Default Printer Commands

Lotus 1-2-3 Line Print Command

There is no command in Microsoft Excel that is equivalent to the Lotus 1-2-3 Line Print command. To provide the same functionality, Microsoft Excel includes a LINE.PRINT macro function that provides compatibility with Lotus 1-2-3. However, the LINE.PRINT macro function does not use the Microsoft Windows printer drivers. Unless you have a specific need for the LINE.PRINT macro function, use the Print command on the File menu or the PRINT function instead.

Note The LINE.PRINT macro function is only available from a Microsoft Excel 4.0 macro sheet. To insert one, choose the Macro command on the Insert menu, and click the MS Excel 4.0 Macro command.

The LINE.PRINT macro function has three forms:

Syntax 1: Go, Line, Page, Align, and Clear

LINE.PRINT(command, file, append)

Syntax 2: Worksheet settings

LINE.PRINT(command, setup_text, leftmarg, rightmarg, topmarg, botmarg, pglen, formatted)

Syntax 3: Global settings

LINE.PRINT(command, setup_text, leftmarg, rightmarg, topmarg, botmarg, pglen, wait, autolf, port, update)

The following macro formula prints the currently defined print area to the currently defined printer port:

LINE.PRINT(1)

Command

A number corresponding to the command you want LINE.PRINT to carry out. For syntax 2 of the LINE.PRINT function, the command argument must be 5. For syntax 3, the command argument must be 6.

File

The name of a file to which you want to print. If omitted, Microsoft Excel prints to the printer port determined by the current global settings.

Append

A logical value specifying whether to append text to file. If TRUE, the file you are printing is appended to file; if FALSE or omitted, the file you are printing overwrites the contents of file.

Setup_text

Text that includes a printer initialization sequence or other control codes to prepare your printer for printing. If omitted, no setup text is used.

Leftmarg

The size of the left margin measured in characters from the left side of the page. If omitted, it is assumed to be 4.

Rightmarg

The size of the right margin measured in characters from the left side of the page. If omitted, it is assumed to be 76.

Topmarg

The size of the top margin measured in lines from the top of the page. If omitted, it is assumed to be 2.

Botmarg

The size of the bottom margin measured in lines from the bottom of the page. If omitted, it is assumed to be 2.

Pglen

The number of lines on one page. If omitted, it is assumed to be 66 (11 inches with 6 lines per inch). If you're using an HP LaserJet or compatible printer, set pglen to 60 (the printer reserves six lines).

Formatted

A logical value specifying whether to format the output. If TRUE or omitted, the output is formatted; if FALSE, it is not formatted.

Wait

A logical value specifying whether to wait after printing a page. If TRUE, Microsoft Excel waits; if FALSE or omitted, Microsoft Excel continues printing.

Autolf

A logical value specifying whether your printer has automatic line feeding. If TRUE, Microsoft Excel prints lines normally; if FALSE or omitted, Microsoft Excel sends an additional line feed character after printing each line.

Port

A number from 1 to 8 specifying which port to use when printing.

Update

A logical value specifying whether to update and save global settings. If TRUE, the settings are saved in the Windows registry; if FALSE or omitted, the global settings are not saved.

Note The default values for print settings on your worksheet are determined by the current global settings.

Lotus 1-2-3 Print Printer Commands

Lotus 1-2-3 Macros

Most Lotus 1-2-3 users are concerned with how Microsoft Excel works with the multitude of macros that they have invested their time in building over the years.

The Macro Interpreter

The Macro Interpreter for Lotus 1-2-3 Users will run your large Lotus 1-2-3 macro applications, including custom menus, without modification. With the Macro Interpreter, you can run 1-2-3 macros that are compatible with Lotus 1-2-3 Release 2.01.

To run a macro on a Lotus 1-2-3 worksheet, hold down the CTRL key and press the macro letter name that is normally used with the backslash (\) key to define the macro in Lotus 1-2-3. The name assigned to a macro in Lotus 1-2-3, such as \a, is defined in Microsoft Excel as a Lotus 1-2-3 macro name when you open a Lotus 1-2-3 worksheet in Microsoft Excel. You can run any macro that is assigned to a macro name consisting of a backslash (\) followed by a single letter. Microsoft Excel assigns a lowercase letter to each macro name. You can only run your existing Lotus 1-2-3 macros and cannot create new Lotus 1-2-3 macros in Microsoft Excel.

Note If you have any Lotus 1-2-3 macros on the Lotus 1-2-3 worksheet and you save the worksheet as a Microsoft Excel worksheet, you can continue to run the macros on the Microsoft Excel worksheet. Some commands, such as /File Combine, work only when applied to WK1 files.

Macros Created Using Lotus 1-2-3 Release 2.2

Microsoft 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}. Microsoft Excel also reads linking formulas created by Release 2.2. However, Microsoft 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 1-2-3 macro libraries (macros in Lotus 1-2-3 Release 2.2 MLB file format), convert the macro library files to a Lotus 1-2-3 WK1 format. Then open the worksheet in Microsoft Excel to run the macro.

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 Microsoft Excel. To open a worksheet without running the autoexec macro, click the Open button or choose Open from the File menu, select the filename, and then hold down SHIFT and click OK. If you have both a Microsoft Excel name Auto_Open that refers to a macro sheet and a \0 macro on the same worksheet, the Auto_Open macro runs first, and then the \0 macro runs.

Lotus 1-2-3 Add-ins Are Not Supported

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

1-2-3 Macros Cannot End in a Menu

When you run a Lotus 1-2-3 macro in Microsoft 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}, Microsoft 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}, a Menu dialog box appears displaying your menu choices.

To get Help with macro prompt dialog boxes, press F1.

Verifying Options Before Running 1-2-3 Macros

When you run a Lotus 1-2-3 macro, the Transition Formula Entry and Transition Navigation Keys options, on the Transition tab of the Options command on the Tools menu, are temporarily turned on and the Move Selection After Enter Check Box option on the Edit tab 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 "Transition Formula Entry" and "Transition Formula Evaluation," earlier in this chapter.

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

For best visual results, you can maximize the Microsoft 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.

Macros That Don't Need to Be Converted

Approximately half of all Lotus 1-2-3 macros do not need to be converted. They were built to aid the user with formatting or printing from Lotus 1-2-3 and are replaced by the standard features of Microsoft Excel. Some of the most common Lotus 1-2-3 macros and the features that replace them are in the table below.

Macros That Don't Convert

The Macro Interpreter runs your Lotus 1-2-3 macros with a few exceptions:

• Macros that are not compatible with Lotus 1-2-3 Release 2.01, or that contain keystrokes for Release 2.2-specific menu commands.
• 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 use to see more information about possible causes of the error. If you have macros that call Lotus 1-2-3 add-ins, you will need to remove these macro statements from your Lotus 1-2-3 macros.

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

Two Microsoft Excel macro commands allow you to call or branch to Visual Basic procedures or Microsoft Excel 4.0 macros written in Microsoft Excel from within a Lotus 1-2-3 macro: {XLCALL} and {XLBRANCH}. These functions take the form:

{XLCALL xl_name} 
{XLBRANCH xl_name}

Substitute the procedure name for the placeholder xl_name shown above. One use of these commands might be to replace calls to Lotus 1-2-3 add-ins with calls to Microsoft Excel add-ins or Visual Basic procedures. Using XLCALL, you can run a Microsoft Excel procedure, after which control returns to the Lotus 1-2-3 macro. Using XLBRANCH, Microsoft Excel takes over and control 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

Microsoft Excel files are called workbooks, and each can contain multiple worksheets. You can have more than one workbook open at once, so you can keep the active workbook open while starting a new one. The following procedure corresponds to the Lotus 1-2-3 Worksheet Erase command, which removes the active worksheet from memory, but not from your disk, so you can start a new one.

To remove the active workbook from memory

1. On the File menu, click Close.

   If you made unsaved changes to the active worksheet, a dialog box appears asking if you want to save your changes.

2. To erase the active worksheet without saving changes, choose the No button.
3. On the File menu, click New to open a new worksheet.

Borland Quattro Pro for MS-DOS Compatibility

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

Opening Quattro Pro for MS-DOS Files in Microsoft Excel

To open Microsoft Excel worksheet files from Quattro Pro for MS-DOS

1. On the File menu, click Open.
2. In the Files Of Type box, select Quattro Pro/DOS.
3. Select the Quattro Pro worksheet you want to open or type the complete filename in the File Name box.
4. Click OK.

Microsoft Excel opens the worksheet. If for some reason Microsoft Excel cannot read a formula in the worksheet, it substitutes the value of the formula for the formula itself.

Running Quattro Pro for MS-DOS Macros in Microsoft Excel

Microsoft Excel for Windows runs Lotus 1-2-3 macros that are compatible with Lotus 1-2-3 Release 2.01. If your Quattro Pro for MS-DOS macro is compatible with Lotus 1-2-3 Release 2.01, then you can run it in Microsoft Excel. For more information, see the Lotus 1-2-3 macro information earlier in this chapter.

To run a Quattro Pro for MS-DOS macro

1. On the File menu, click Open.
2. In the Files Of Type box, select Quattro Pro.
3. Select the Quattro Pro worksheet you want to open or type the complete filename in the File Name box.
4. Click OK.
5. On the Tools menu, click Macro.
6. In the Macro Name/Reference box, type or select the name of the Quattro Pro macro you want to run.
7. Click OK.

Borland Quattro Pro for Windows Compatibility

You can use Microsoft Excel with your existing Quattro Pro for Windows Notebooks by simply opening your Notebook in Microsoft Excel the same way you open any Microsoft Excel workbook -- using the Open button on the Standard toolbar, or the Open command on the File menu. When you finish editing the workbook, you can save it as either a Microsoft Excel workbook or a Quattro Pro for Windows Notebook.

Unsupported Borland Quattro Pro for Windows Version 5 Features

Features without direct equivalents in Microsoft Excel will not be imported.

• Quattro Pro for Windows graphs are stored in a very different manner than Microsoft Excel stores its charts. Therefore, graphs are not imported.
• The Microsoft Excel Scenario Manager is not directly equivalent to the Quattro Pro for Windows Scenario Manager. Therefore, Microsoft Excel will import only the data from the scenario that is currently displayed (the last time the file was saved). You can use the Microsoft Excel Scenario Manager to create, store, and retrieve what-if assumptions for multiple sets of up to 32 changing cells.
• Hot Links to external database tables or to the Data Modeling Desktop are not compatible with ODBC, Microsoft Query, nor Microsoft Excel Pivot Tables, 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.

Multiplan Compatibility

To transfer Multiplan files to Microsoft Excel, you must save the Multiplan worksheet in SYLK format.

To open a worksheet in Microsoft Excel that was created with Multiplan version 3.04 and earlier and saved in SYLK format:

1. On the File menu, click Open.
2. In the Files of Type list box, select SYLK Files.
3. In the list box, locate and select the Multiplan worksheet.
4. Click OK.

You can save your Microsoft Excel workbooks so that you can open them in Multiplan. When you do this, Microsoft Excel saves only the first worksheet in the workbook, so you'll need to save each sheet separately.

To save a Microsoft Excel workbook so you can open it in Multiplan version 3.04 and earlier

1. On the File menu, click Save As.
2. In the Save As Type list box, select SYLK (Symbolic Link).
3. Click OK.

   A dialog box informs you that only the active sheet will be saved.

4. Click OK.

Repeat this procedure for each sheet in the workbook you want to open in Multiplan.

To save a Microsoft Excel workbook so you can open it in Multiplan version 4.0 and later.

1. On the File menu, click Save As.
2. In the Save File As Type box, select Microsoft Excel 2.1 Worksheet.
3. Click OK.

   A dialog box informs you that only the active sheet will be saved.

4. Click OK.

Multiplan version 4.0 and later can open and save files in a Microsoft Excel file format.

Multiplan Format Options Command

Multiplan Function and Special Keys

Selecting and Carrying Out Commands

Editing Cells and Commands

Multiplan Options Command

Multiplan Print File Command

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

Multiplan Print Options Command

Multiplan Run Command Command

There is no direct equivalent to the Multiplan Run Command command in Microsoft Excel, but you can activate an MS-DOS window by using the Microsoft Windows Start menu.

To activate an MS-DOS window

1. Click the Windows Start button.
2. On the Programs menu, click the MS-DOS Prompt command.

To return to Microsoft Excel

1. Type exit
2. Click the Microsoft Excel button on the Windows taskbar.

Multiplan Run Report Commands

Multiplan Transfer Clear Window Command

There is no direct equivalent to the Multiplan Transfer Clear Window command in Microsoft Excel.

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

Microsoft Works Compatibility

Microsoft Excel lets you open files created with Microsoft Works 2.0. Now you can easily move up to Microsoft Excel or just use its analytical and presentation abilities to finish work you started in Microsoft Works.

To open Microsoft Works worksheet files in Microsoft Excel

1. On the File menu, click Open.
2. In the Files Of Type box, select Microsoft Works 2.0 Files.
3. Select the Works worksheet you want to open or type the complete filename in the File Name box.
4. Click OK.

Microsoft Excel opens the worksheet. If for some reason Microsoft Excel cannot read a formula in the worksheet, it substitutes the value of the formula for the formula itself.

Send us your comments

Go To TechNet Home Page

Go To Microsoft Home Page