|
|
|
|
|
123 to ExcelConversion issuesExcel opens and saves the following Lotus 123 file formats.
I can’t open my Lotus file in Excel Excel will open files created in Lotus 123 versions 2.x5.x. Most data and formatting created in Lotus 123 are fully supported by Excel. The latest versions of Lotus 1-2-3 use a file type (.123) not currently supported by Excel. You must save these as .WK4 files before you try and open them in Excel. Auditing Converted Worksheets Audits conducted by the industry on corporate MSDOSbased worksheets have found that approximately 30 percent of all worksheets contain serious errors. In some cases, major decisions have been made using worksheets that have been incorrect for years. The only way to catch these errors is with a worksheet audit. You can do the audit while the worksheet is in Lotus 123 or after it is converted to Excel. The best course is a partial audit on both sides, since each audit catches different problems. Auditing Your Worksheets Before Conversion Auditing your Lotus 123 worksheets before conversion catches problems inherent in the original worksheet, such as values that have replaced formulas, circular errors, incorrect results, and bad range names. Auditing Your Worksheets After Conversion Auditing after conversion catches problems introduced by the conversion process or by reorganization and linking. Auditing worksheets in Excel helps you find formulas that did not convert, links that are incorrect, or unexpected problems for which you might need additional help. The following Excel features are particularly useful for auditing:
Note If Excel encounters formulas that it cannot convert when you open a Lotus 123 worksheet, only the resulting values are displayed. The original formula is discarded. Excel indicates this by displaying a cell comment (and cell comment indicator) in the cell, containing the message "Formula failed to convert.".Opening and Saving Lotus 123 Worksheets in Microsoft Excel The majority of your Lotus 123 worksheets can be converted to Excel format by opening them and then saving them in Excel. To open a Lotus 123 worksheet in Excel
Excel converts the Lotus 123 worksheet and opens it. How do I know if my work has been successfully converted? We recommend that you print the key pages or reports from your Lotus files before you convert them, remembering to ensure they are calculated (function key F9) first. After you have converted the files, print the same pages and reports from Excel and make a tick-check comparison between them. If the results are the same, your conversion has been successful. My results are not the same after conversion. If you have formulas in cells that cannot be converted, Excel notifies you that it cannot read the record, and then displays another dialog box asking you whether to continue alerting you each time a cell does not convert. Formulas that do not convert are discarded, but the result of the formula is preserved and Excel attaches a comment to the cell, containing the message "Formula failed to convert." What is the file conversion wizard? If you want to convert multiple worksheets to or from Lotus 123 format, you can also use the File Conversion Wizard addin. I’ve lost all the formatting from my converted file Lotus 123 files When formatting is applied to a Lotus 123 WK1 or WK3 worksheet, a separate file is created and saved along with the worksheet. Check that the formatting file was in the same directory as the .WK* file. How do I search for cells containing formulas that did not convert correctly
After you have converted the worksheet, you can select all cells with cell comments. How do I find all cells with cell comments
This selects all cells with comments, allowing you to see where your formulas did not convert. You can also print the comments along with the sheet and then use this printed document as a reference for troubleshooting. How do I print cell comments
Cell comments consist of all comments inserted by Excel during the conversion process, as well as all cells converted from WK3 files that contain Lotus 123style text notes in their formulas. The Special button in the Go To dialog box (Edit menu) is a powerful tool for auditing converted worksheets. Using the options in this dialog box, you can find cells that:
Another auditing feature included with Excel is cell tracers. Cell tracers are arrows drawn on a worksheet that point to the precedents or dependents of a selected cell, or trace the error path of a cell containing an error value. Use the Auditing submenu commands (Tools menu) to display tracer arrows. Alternatively, you can point to Auditing and then click Show Auditing Toolbar to display the Auditing toolbar, which you can use to trace the flow of data between cells on your worksheet. Translating Lotus 123 Nested Formulas Perhaps the most common reason for the "Cannot read record" message when converting Lotus 123 formulas to Excel occurs when a formula in your Lotus 123 worksheet uses more than seven levels of nesting. To get around this, you can break the formula into sections of less than seven nested segments before conversion. However, many such nested formulas exist in order to construct elaborate alternative calculations based on a range of current conditions, such as @IF statements. In this case, a better solution is to create a formula using @VLOOKUP and refer to a table elsewhere on the worksheet. Then no nesting is needed, and you end up with a more readable and structured formula. For example, suppose that in one cell you have the following Lotus 123 formula that arrives at a value, based on the name of a month from January to September: @IF(a1="Jan",12,@IF(a1="Feb",2,@IF(a1="Mar",4,@IF(a1="Apr",34,@IF(a1="May",32,@IF(a1="Jun",8,@IF(a1="Jul",43,@IF(a1="Aug",3,@IF(a1="Sep",67,0))))))))) This formula has nine levels of nesting. To make conversion to Excel easier, rewrite the formula like this in Lotus 123: @VLOOKUP(a1,table,1) where table is a range name that refers to the following twocolumn table, located anywhere on the worksheet. Jan 12 When you then open the file in Excel (and transition formula evaluation is automatically turned on), the formula converts without problems, and it works properly. (The offset argument 1 is automatically converted to 2 because Excel starts counting at 1, not 0.) Using a table in this way is not only easier to read than the original formula, but it is also easy to modify by changing or adding new values.Do my old macros work in Excel? Excel includes the Macro Interpreter for Lotus 123 users, which provides macro conversion support.
Terminology and Equivalents Microsoft Excel Terms for Lotus 123 Users The following table lists Lotus 123 terms and their Excel counterparts. The Excel term is not necessarily an exact equivalent of the Lotus 123 term, but rather a term you can look up in online Help for more information.
Microsoft Excel Equivalents for Lotus 123 Commands The following table lists frequently used Lotus 123 commands and the equivalent commands in Excel.
The Navigation Keys are different The following table compares navigation key assignments in Lotus 123 and their equivalents in Excel.
Is there an equivalent for the Lotus 123 System Command (Windows only) There is no direct equivalent for the Lotus 123 System command in Excel, but you can activate the command prompt from the Start menu. How do I use three dimensional formulas? If you store a group of worksheets with identical layouts, such as monthly reports, in the same workbook, you can use three-dimensional formulas to consolidate data into summary worksheets. These three-dimensional formulas allow you to specify sheet ranges in a workbook, which are similar to cell ranges on a worksheet. You can apply a number of different functions, such as SUM and AVERAGE, to the resulting three-dimensional range. For example, the formula SUM(Sheet1:Sheet4!$A$1) sums the contents of cell A1 on the contiguous sheets named Sheet1, Sheet2, Sheet3, and Sheet4. You can also use the Consolidate command (Data menu) to create summary reports for sheets that have an identical or similar layout. Where can I get help on Excel’s functions? For details about Excel functions, click the Paste Function button (Standard toolbar), select the function you want, and then click the Question Mark button. In the Office Assistant, click Help with this feature, and then click Help on selected function. Functions are divided into categories in the Function Wizard dialog box. For example, the FREQUENCY, LINEST, LOGEST, GROWTH, and TREND functions are located in the Statistical category, while the MINVERSE and MMULT functions are located in the Math & Trig category. Help for the Analysis ToolPak is available by clicking the Help button in any Analysis ToolPak dialog box. What is the order of mathematical operators?
In Lotus 123, the exponentiation operator (^) is evaluated before the negation operator ( – ); in Excel, negation is evaluated first. Thus, the formula =–2^4 produces the value –16 in Lotus 123, and 16 in Excel. To change this, use parentheses to force the preferred order of evaluation in Excel. For example: =–(2^4) Can I use my Lotus 1-2-3 macros in Excel? Most Lotus 123 users have invested time over the years
building macros. Excel includes the Macro Interpreter for Lotus 123
Users, which provides limited macro conversion support. We strongly recommend that you convert any Lotus 1-2-3 macros that you use into Excel’s VBA at the earliest opportunity. We have a team of specialists available to help you convert your macros. Running Macros Created in Lotus 123 Release 2 Excel can run macros that contain any Lotus 123 Release 2.2 advanced macro commands, such as {BORDERSON}, {BORDERSOFF}, {FRAMEON}, {FRAMEOFF}, {GRAPHON}, and {GRAPHOFF}. Excel also reads linking formulas created by Release 2.2. However, Excel cannot run macros that use slash menu commands that are specific to Release 2.2. Converting Lotus 123 Release 2.2 Macro Library Files If you have Lotus 123 macros in macro libraries (macros in Lotus 123 Release 2.2 MLB file format), you can convert them to Excel. To convert Lotus 123 MLB files
Will my autoexec macros run? If you have a Lotus 123 autoexec macro (named \0) on your worksheet, the macro runs automatically when you open the worksheet in Excel. Can I stop the autoexec macro from running?
I have a \0 Lotus macro, and I’ve now created an Excel Auto_Open macro If you have both an Excel macro named Auto_Open that refers to a macro sheet and a Lotus 123 \0 macro on the same worksheet, the Auto_Open macro runs first, and then the \0 macro runs. Can I use my Lotus 1-2-3 add-ins in Excel No. You must be sure to remove any occurrence of keystrokes or command names that attach, start, or use a Lotus 123 addin, such as the Allways addin and its menu structure. For example, remove statements such as /a and {app1}. Lotus 1-2-3 macros that end in a menu When you run a Lotus 123 macro in Excel, the Lotus 123 macro cannot end in a menu, such as the keystrokes /PP (Print Printer). If a macro does end in a menu, a message appears stating that macros cannot end in a menu. Then the macro terminates. The macro can, however, end in a prompt for more information, such as the keystrokes /PPR (Print Printer Range), so that you can specify the print range. Substituting Standard Microsoft Excel Features for Lotus 123 Macros Many Lotus 123 macros do not need to be converted. These macros, which aid the user with formatting or printing from Lotus 123, are replaced by standard features in Excel. Some of the most common Lotus 123 macros and the Excel features that replace them are described in the following table.
Using Lotus 123 Charts in Microsoft Excel Introduction Excel and Lotus 123 format charts differently. This section discusses Excel equivalents for Lotus 123 chart format commands. Lotus 123 Graph Options Format Command Equivalents The Lotus 123 Graph Options Format commands apply to line and xy (scatter) charts only. How do I format a line or xy (scatter) chart in Excel?
– or – If you do not want any markers, click None. Lotus 123 Graph Options Grid Command Equivalents The Lotus 123 Graph Options Grid commands apply to all graph types with axes.
How do I add and delete gridlines in a chart in Excel?
Lotus 123 Graph Type Command Equivalents In Excel, you change the chart type after you create the chart. Click the Chart Type command (Chart menu) when a chart is active. This command also allows you to select additional chart types, such as threedimensional charts. Lotus 123 Graph View Command Equivalents After you create a chart in Excel, it remains visible on a worksheet as an embedded chart, or as a separate chart sheet in the workbook. Therefore, the procedure for creating a chart in Excel is the closest equivalent to the Lotus 123 Graph View command. Lotus 123 Graph Options Color Command Equivalents There is no direct equivalent in Excel for the Lotus 123 Graph Options Color command. However, you can change the color of individual chart items. How do I change the color of a chart item in Excel?
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 123 Graph Options Scale Command Equivalents The following sections describe Excel equivalents for Lotus 123 Scale commands. Auto Excel creates the scale automatically. If you designate any aspect of the scale as manual, you can return it to automatic. How do I set an axis scale to automatic in Excel?
Manual, Lower, and Upper You can control the chart scale manually. How do I control a chart scale manually in Excel? Click the xaxis (category) or yaxis (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 xaxis and the yaxis. Change the options you want. Format You can change the number format on the chart scale. How do I change the number format on a chart scale in Excel? In the chart, click the yaxis (value). You can change the xaxis 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.
How do I display or hide chart scale indicators in Excel?
.
Printing What are the Excel Equivalents for Lotus 123 Worksheet Global Default Printer Commands?
What is the Excel Equivalent for the Lotus 123 Line Print Command? There is no command in Excel that is equivalent to the Lotus 123 Line Print command. Instead, use the LINE.PRINT macro function. What are Excel’s equivalent features for typical Lotus 123 print printer commands?
Which WK3 functions do not have Excel equivalents? A number of Lotus 123 WK3 functions — nonaggregate functions that use three-dimensional references — do not properly convert to Excel. A nonaggregate function is one that is not commonly used with a range of values. An aggregate function is one that is always used with a range of values, such as SUM, AVERAGE, MIN, and MAX. For example, the nonaggregate function @INDEX does not convert if it uses references that encompass more than one ply of a three-dimensional worksheet. (It converts properly if no three-dimensional reference is used.) WK3 functions are unsupported when they include a three-dimensional argument; if a normal argument is used, they may work. These unsupported functions are shown in the following list.
pppp In addition, Excel cannot convert formulas with more than one table argument using @DSUM, @DAVG, @DMIN, @DMAX, @DSTD, @DVAR, @DSTDS, or @DVARS. Other functions that present conversion problems include @DQUERY when using the DataLens addin, and @CELLPOINTER when using the sheet argument. Why have some of my range-names changed? Because Excel does not allow all of the special characters in names that Lotus 123 does, names that contain different special characters but are otherwise identical are converted to an identical name, resulting in an error. Besides letters and numbers, Excel allows only underscore and backslash ( \ ) characters to be used in names. Excel converts any invalid characters in names to the underscore character ( _ ) when reading Lotus 123 worksheets. Excel notifies the user that it cannot read the record if two or more defined names on the worksheet contain special characters that cause them to resolve to the same name. For example, if you have defined the names TOTAL$ and TOTAL# on a Lotus 123 worksheet and you open the worksheet in Excel, the first defined name TOTAL$ is converted to TOTAL_, and the second defined name TOTAL# is lost. You can work around this by checking for invalid characters in your Lotus 123 worksheets before converting them to Excel. Do Lotus 123 Releases 4 and 5 have features without Microsoft Excel equivalents? Lotus 123 features without direct equivalents in Excel are not imported. These include the following:
Because these are not compatible with either ODBC or Microsoft Query, they are not imported.
These include the Lotus Maps objects from Release 5.
The cell or object is formatted using only the primary colour from the fill. Does Excel have functions that were not available in Lotus 123? The following Excel functions have no equivalents in Lotus 123 Release 3.1 or earlier, or Lotus 123/W Release 1.0. Note Excel also provides many addin functions and statistical functions in the Analysis ToolPak that don't have Lotus 123 equivalents; these are not included in this list.
Excel's help for Lotus users Tell me about the Lotus 123 Help feature in Excel. In Excel, the Lotus 123 Help feature allows you to use familiar Lotus 123 keys and commands while you learn how to use Excel. For example, you can choose a Lotus 123 key or command and have Excel display stepbystep instructions for the corresponding action in Excel. Or Excel can demonstrate and actually carry out the corresponding action. Note The Lotus 123 Help feature also provides Help topics for users switching from Lotus 123 for MSDOS. You can press a Lotus 123 key and have Excel either automatically demonstrate the corresponding feature or list the steps you need to perform it in Excel. If you are not sure which Lotus 123 key to press, you can choose from a list of Lotus 123 commands. To use Lotus 123 Help 1. On the Tools menu, click Options, and then click the Transition tab. 2. Under Settings, click Lotus 123 Help. Next you need to indicate whether you want to see stepbystep instructions or demonstrations when you press Lotus 123 keys. To see stepbystep instructions or demonstrations 1. On the Help menu, click Lotus 123 Help. 2. Under Help options, click Instructions to see stepbystep instructions. – or – Click Demo to see demonstrations. When you're working in an Excel document and you press the SLASH key ( / ), or the key you specified on the Transition tab in the Options dialog box (Tools menu), the Help for Lotus 123 Users dialog box appears. Select the Lotus 123 command you want, and then click either Instructions or Demo.
The following sections describe options in the Help for Lotus 123 Users dialog box. Menu The Menu box in the Help for Lotus 123 Users dialog box displays a list of Lotus 123 menu items. Type the Lotus 123 keystrokes you would use to choose a command. Depending on the type of help you select under Help options, Excel either begins a demonstration or displays instructions for carrying out the equivalent actions in Excel. For multilevel Lotus 123 menus, Excel displays the submenu at the bottom of the dialog box. To move to the next menu level, select the menu item in the Menu box, and then press ENTER or press the first letter of the menu item. Help Options In the Help options box in the Help for Lotus 123 Users dialog box you can choose either to display a text box containing the Excel equivalent procedure for carrying out a Lotus 123 command (the Instructions option), or to watch Excel demonstrate the equivalent steps for you (the Demo option). For commands requiring additional information, such as cell references, you are prompted for the necessary information at the top of the Excel window before the demonstration starts. The Faster and Slower buttons allow you to choose from among five demonstration speeds, with 5 being the fastest and 1 the slowest. The current speed is displayed in the box to the right of the two buttons.
Excel and the Year 2000 Overview
How can I tell if my spreadsheets are millennium compliant? If your work does not contain any date formulas, or
formulas that act on date entries, or formulas that give dates as
results, then you do not have a problem. What formulas could present millennium compliance problems? This is a list of Excel’s date and time formulas. It is how you use them that can generate problems.
Which formulas rely on the system clock? NOW and TODAY
Other Can Excel sort on more than three fields? First, sort on the minor fields and progressively work towards the major fields. How do I get the © or the ® or the ™ symbols in my work? Use (c) and (r) and (tm). Excel will automatically change them to ©, ® and ™ using its "AutoCorrect" feature which is on the "Tools" menu. I don’t want my work corrected as I type. On the Tools menu, click "AutoCorrect". To prevent all automatic corrections, clear the Replace text as you type check box. To prevent specific types of corrections, clear the check box for the corresponding option. Can I turn "AutoCorrect" off? "AutoCorrect" has five check boxes that allow you to control what it does or doesn’t correct. "AutoCorrect" is on the "Tools" menu. Facts and figures
Row heights are measured in points. What is a "Point"? A point is a unit of measurement that determines the height of a character. A point is approximately 1/72 of an inch. How are column widths measured? The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell. The "Standard Font" is the default text font for worksheets defined in the "Normal" style. How do I make the cursor move down to the next cell when I press "Enter"? From the Tools menu select Options. Click on the Edit tab, then check the box Move selection after Enter, and finally select the direction you want to move by selecting Down, Right, Up, or Left from the Direction drop-down box. Why aren’t my VLOOKUP formulas producing the same results? Excel’s VLOOKUP works in a different manner to 123’s. It
has an optional fourth argument which you may need to use in order to
get the same result. Check the Excel Help file for more information. Excel is changing what I am typing into the cell. Why? The most likely cause is an entry in "AutoCorrect" table. Click on "Tools" then select "AutoCorrect" and see if there is an entry for your problem word. Why are you recommending that we do not use "Transition formula evaluation"? "Transition formula evaluation" lets Excel treat formulas as though they should be evaluated according to Lotus 123 rules. This can lead to unexpected or incorrect results when data is shared between users who do not follow the same evaluation rule. When I enter a date, e.g., 3/4/99, I get "=3/4/99" in the formula bar and "0.0075758" in the cell. Why? You have got "Transition formula entry" turned on. To tuen it off, click on "Tools, Options", then click on the "Transition" tab and uncheck the "Transition formula entry" box. How do I find the last day of a month? To find the last day of December 1998, use the formula
"=DATE(1999,1,0)". The "zeroth" day of the current month represents the
last day of the previous month. Note: This document is culled from many sources.
Originally published: 2000 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
