The following quick reference guide to the Lotus 1-2-3 spreadsheet software for DOS was prepared by Kathy Lovell, a computer trainder at Harvard University. ---------- BASIC LOTUS 1-2-3 VOCABULARY CELL The individual unit of a worksheet, formed by the intersection of a ROW and a COLUMN, Columns are given letter names A, B, C etc., and rows are given numbers. ADDRESS A cell's name is also referred to as its ADDRESS. The cells address is the combination of its column and row in that order. For example, the intersection of Column A and Row 1 is called A1 (WITHOUT ANY SPACES). MENU There are two ways to execute a command. Either press a function key (F1 - F10), or press the MENU KEY(/) and choose a command from the menu. To choose a command from a menu use the cursor keys to point to a command (highlight it) and press ENTER, or type the first letter of the command. When choosing a menu option by typing the first letter, do not use the ENTER key. HELP Whenever you run into a problem with 1-2-3, get HELP by pressing the F1 key, or press the ESC (escape) key to back up to the last command option you entered. You can press ESC as many times as you want until you get back to READY mode. Pressing CTRL- BREAK is like pressing as many ESCAPES as it takes to get back to READY mode. CELL POINTER The extended CURSOR that points to the current cell is called the CELL POINTER or HIGHLIGHT. LABELS A label is a cell entry that is neither a number nor a calculation. Labels generally start with an alpha character. All labels are preceded by a LABEL PREFIX. This is a character which determines the alignment of the text within the cell, either left aligned, right aligned, centered, or repeating. If no label prefix is typed 1-2-3 will automatically insert a left aligned prefix, an apostrophe. Labels may consist of numbers. For example, a zip code (02124) is a label (if entered as a number/value the leading zero is removed). To enter a label that looks like a number, you must start with a label prefix. The default label alignment in 1-2-3 is left. To change the way a label is aligned in its cell either start typing it with one of the other label prefixes, or change its label prefix with /Range Label-Prefix.) This is how you make labels appear left aligned, right aligned, or centered. To make a character fill a cell (for example, the dash is often used for underlines), enter a backlash before the dash in the cell. LABEL PREFIXES The four label prefixes are: ' APOSTROPHE Left-aligned ^ CARET Centered " DOUBLE QUOTE Right-aligned \ BACKLASH Repeating VALUES A number may begin with a digit, a plus or minus sign, a period, or a dollar-sign. Never type commas in a number. Commas may be added with /Range Format. A number may end with a percent sign. Ending the number this way has the same effect as dividing it by 100. To change the way a number looks, use /Range Format. This is how you make numbers appear with dollar signs, or as percentages, etc. Number are always right justified. You cannot change their alignment. ANCHORING In certain operations, a process called ANCHORING stretches the cell pointer so that you can point to more than one cell at the same time. When referring to several cells at once (as in the Copy command), point to the upper-left most cell in the range, anchor the cursor by pressing the period key, drag the cursor to the lower-right cell in the range, and press ENTER. Pressing the period key also moves the blinking cursor around the perimeter of the highlighted range, allowing you to change the leading edge of the range. TO USE LOTUS 1. Always point to the correct cell. 2. To enter a number or label, point to the cell, type the entry, and press ENTER or a cursor arrow key. To enter a formula, point to the cell where the result of the formula will appear, and press the = key. Next, point to the input cells and type the operators (+, *, etc.) that are needed for the formula. Finish the formula with ENTER. 3. LEARN HOW TO USE THE MENUS. To call up the menu, you always press the slash/key. To pick an option from a menu, either point to the option and press ENTER, or type the first character (type C for Copy, R for Range, etc.) 4. LEARN HOW TO HIGHLIGHT A RANGE. Do this by pointing to the first cell in the range (or group) of cells you want to point to, anchor with the period, then use the cursor arrow keys to indicate the other cells. CURSOR MOVEMENT These keys move the cell pointer or the window: LEFT ARROW: Moves the pointer one cell to the left. RIGHT ARROW: Moves the pointer one cell to the right. UP ARROW: Moves the pointer one cell up. DOWN ARROW: Moves the pointer one cell down. SHIFT + TAB: Moves the window one screen to the left. TAB: Moves the window one screen to the right. HOME: Moves the cursor to the upper left corner of the active screen (Cell A1, unless Titles have been turned on). PGUP: Moves the window twenty rows up. PGDN: Moves the window twenty rows down. END - LEFT ARROW: Moves the pointer to the next border of filled and empty cells to the left. END - RIGHT ARROW: Moves the pointer to the next border of filled and empty cells to the right. END-UP: Moves the pointer to the next border of filled and empty cells above. END-DOWN: Moves the pointer to the next border of filled and empty cells below. GO TO: Moves the pointer to the specified cell address or named range. END-HOME: Moves the pointer to the lower right corner of the current spreadsheet. FORMULAS A formula begins with a number, a plus sign (+), a minus sign (-), a parenthesis, or an @. Formulas do not contain any spaces. Formulas may contain numbers, cell references, @functions, or any combination of the three. Formulas follow the standard order or operations. That is, multiplication and division within a formula are performed before addition and substraction. You may use parenthesis to specify the intended order or operation. For example: (2+3)*4=20 but 2+3*4=14 Formulas are entered either by pointing to the cell(s) needed to complete the formula, or typing the cell's address. If you use the point method, start with a + and then point to the cell you are referencing; follow with the appropriate operator (such as a plus or minus sign, etc.) Continue pointing and typing operators until the formula is complete. As you do this formula is being built on the editing line. Press ENTER when the formula is complete. Formulas may be copied. When Lotus formulas are copied, the cell-references adjust to create a new formula for the new location. This uses a process called Relative Addressing. ADDRESSES When a cell address is used in a formula it's called a REFERENCE. IN FORMULASThere are two types of references in 1-2-3; RELATIVE and ABSOLUTE. Most 1-2-3 formulas use relative references; that is, the addresses in the formulas will adjust appropriately for their new location if the formula is copied. On some occasions it is necessary to create an Absolute Reference; one in which the cell addresses do not change if they are copied. ABSOLUTE A formula with an absolute reference does not change the cell REFERENCE reference as the formula is copied. RELATIVE Relative address the default in 1-2-3. Any formula that uses a cell REFERENCE address by just indicating the column and row, such as A2 or G15, is written in relative address. CREATING AN ABSOLUTE ADDRESS $B$6 Neither the reference to the row nor the column will change if the formula containing this reference is copied. B$6 Only the reference to the column will change if the formula containing this reference is copied. $B6 Only the reference to the row will change if the formula containing this reference is copied. B6 Both the reference to the column and the row will change if the formula containing this reference is copied. FUNCTIONS FUNCTIONS are a very important part of Lotus 1-2-3. Functions are pre-defined formulas which allow you to do many simple and complex calculations quickly and easily. As with formulas, functions are typed in a cell and the result of the function is placed in the cell as an answer. All functions start with an @ sign and have a name which describes what their job is. The function for adding up the values in a range of cells is @SUM, the function for determining the average of a range of cells is @AVG. Most functions have a set of parenthesis following their name. The parenthesis contain an ARGUMENT. An argument is the value, or cell address that the function needs in order to work. FUNCTION @SUM(Range) the sum of all values in the range @AVG(B10.B100) the average of all values in the range @MAX(C1..D20) the largest number in the range @MIN(C1..D20) the smallest number in the range @COUNT(range) the number of cells in the range RANGE FORMAT If you want the number in a cell or range to be displayed with a style other than the global format, the cell must be given a new format. The available styles are listed below. Fixed Set a number to always display a certain number of decimal places, but with no comma. You may choose 0 to 15 decimal places. For example: 12.00 or 12345.00 ScientificSet a number to be displayed in Scientific Notation. For example: 12.2E+08 Currency Set a number to be displayed with a dollar sign, commas separating the thousands, in parenthesis if it is negative, and a certain number of decimal places. For example: $12,345.00 or ($12,345) Comma Set a number to be displayed with commas separating the thousands, negative numbers in parentheses, and a certain number of decimal places. For example: 12,234.00 or (12,345) General Set a number to be displayed with truncated trailing zeroes, no commas, and negative numbers with a minus sign. For example: 12.4 or -12345 +/- Set a number to be displayed as just + or - signs, with as many + signs or - signs as the value. For example, if you entered the value 3, the cell would display +++; if you entered -3, the cell would display --- Percent Set a number to be displayed as a percentage with a certain number of decimal places. For example: 12% or 12.345 % Date Set a number to be displayed as a date, or as a time value. For example: 07-Jun-88 or 12:54 PM Text Set a formula to be displayed as the formula itself, rather than its computed value. For example: +C5-@SUM(B2..B5) Hidden Set a cell to appear as if it were blank even if there is information in it Reset Reset any cell to the current Worksheet Global Default Format WORKSHEET GLOBAL FORMAT Every cell in a spreadsheet has a format. The standard format, that is the format which will be used if you do not specifically change it, is said to be the Worksheet Global Default Format. If you have not changed the default format all new numbers entered will be given the format General, which means 1-2-3 will: 1. Truncate leading and trailing zeroes (enter 0012.200 in a cell and you'll see 12.2) 2. Display negative numbers with a minus sign. 3. Display numbers without commas. CHANGING COLUMN WIDTHS Every column in a spreadsheet has a certain width; from one to 240 characters. When you create a new spreadsheet, the columns are always nine characters wide. To change the width of one column, place the cell pointer in the column whose width is to be changed, then choose / Worksheet Column Set-Width from the menu. 1-2-3 will then prompt you to enter the new width (either by typing the new width, or by using the left and right cursor keys to increase/decrease the column's width) and press ENTER. If a spreadsheet needs to have all the columns set to width other than 9, choose / Worksheet Global Default Column-Width from the menu, then type in the new width (1 - 240 characters), and press ENTER. COPYING CELLS The Copy command allows you to replicate information. It saves time by cutting down on repetitive data entry. You may copy labels, numbers or formulas. There are three possible variations on the copying procedure. They are: 1. Copy from ONE cell into ONE other cell. 2. Copy from ONE cell into MANY other cells. 3. Copy from MANY cells into MANY other cells. COPY FROM ONE CELL INTO ONE OTHER CELL. 1. Place the highlight on the cell to be copied (the FROM range). 2. Type / and choose Copy from the menu. 3. Indicate the FROM range by pressing ENTER to select the current cell. 4. Move to the destination cell (the TO range) and press ENTER. COPY FROM ONE CELL INTO MANY OTHER CELLS. 1. Place the highlight on the cell to be copied (the FROM range). 2. Type / and choose Copy form the menu. 3. Indicate the FROM range by pressing ENTER to select the current cell. 4. Move to the upper left corner of the destination range (the TO range). 5. Press . (the anchor key) to lock the beginning point of the TO range. 6. Highlight the rest of the TO range and press ENTER. COPY FROM MANY CELLS INTO MANY OTHER CELLS. 1. Place the highlight on the cell in the upper left corner of the range to be copied (the FROM range). 2. Type / and choose Copy from the menu. 3. Highlight the rest of the FROM range and press ENTER. 4. Move to the upper left corner of the destination range (the TO range). 5. Press . (the anchor key) to lock the beginning point of the TO range. 6. Highlight the rest of the TO range and press ENTER. MOVING CELLS Moving simply allows you to relocate a cell or range. The basics are the same as with copying with these exceptions: 1. Moving does not leave a copy of the range in the original position. 2. Moving does not perform relative address readjustment in formulas. The formula, and therefore the answer, in a cell that has been moved will be identical to the formula in the original location. MOVE FROM ONE CELL TO ONE OTHER CELL. 1. Place the highlight on the cell to be moved (the FROM range). 2. Type / and choose Move from the menu. 3. Indicate the FROM range by pressing ENTER to select the current cell. 4. Move to the destination cell (the TO range) and press ENTER. MOVE FROM MANY CELLS TO MANY OTHER CELLS. 1. Place the highlight on the cell in the upper left corner of the range to be moved (the FROM range). 2. Type / and choose Move from the menu. 3. Highlight the rest of the FROM range and press ENTER. 4. Move to the upper left corner of the destination range (the TO range). 5. Press ENTER to conclude the move. SAVING A WORKSHEET To permanently store the current worksheet on disk you must save the file. To do so choose / File Save, and type the name of the file. (Remember, file names cannot have more than eight characters, and cannot contain any spaces.) If you try to save a file with the same name as a file that is already on the disk, you will see another menu, with the options to Cancel or Replace. Cancel will allow you to abort the / File Save if you wish to save your file under a new name (thus preserving the original file on the disk). After choosing Cancel, redo your / File Save command and give your file a new name. Replace will erase the file on the disk and replace it with the current worksheet. You would only choose Replace if the spreadsheet you are saving is a newer, updated version of the one already on the disk. PRINTING 1. Choose / Print. 2. The next menu will ask you to choose the destination of the printout. Choose Printer to print the spreadsheet on a piece of paper; pick File to create and ASCII file on the disk. 3. The most important part of printing a spreadsheet is defining the Range. This is how you tell 1-2-3 what part of the spreadsheet to print. Anchor on the first cell in the range to be printed, and drag to the lower right-hand corner of the range and press ENTER. 4. Select the Options Menu to enter a Header, Footer, Margins, Set-Up String, or Borders. Choose Quit to return to the Print Menu. 5. Choose Align from the print menu when you want to tell 1-2-3 that the printer is at the top of the paper (do this before you actually print). 6. To print the spreadsheet (after specifying the range to be printed), pick Go from the Print Menu. 7. Choose Page from the print menu when you want to eject the paper (similar to the form-feed button on most printers). 8. Choose Quit to leave the print menu and return to the READY mode. RETRIEVING A FILE To load a spreadsheet file on a disk into 1-2-3, choose / File Retrieve, and choose the file from the menu of file names. (You can also type the name of the file to be retrieved.) A worksheet already on the screen will be erased if you retrieve another file, therefore you should save your work before retrieving a file. OTHER FILE COMMANDS To list all the files on the disk, choose / File List. Then indicate which type of file you wish to see listed. The choices are Worksheet, Graph, Print, and Other. To erase a file from the disk, choose / File Erase. You will get another menu, from which you choose the type of file to be erased (Worksheet, Graph, or Print). Obviously, you must be cautious when you erase files; once they are gone, they can't be retrieved. To save a range of the current spreadsheet as a separate worksheet, choose / File Extract. To combine an existing spreadsheet from the disk with the current spreadsheet (i.e., the one you are working on), choose / File Combine. The other spreadsheet will be combined with the current spreadsheet starting in whatever cell you are pointing to. As always, it is important to be pointing to the appropriate cell. You have the choice of pulling in either the other spreadsheet in its entirety, or just a range. (You can set up range names in the other spreadsheet prior to combining it to make this easier.) To change the disk drive and/or subdirectory into which worksheets are being saved, choose /File Directory, and type the name of the new drive/directory. GRAPHS The Basics of Creating a Graph 1. Select the type of graph you want to use 2. Specify the data on the worksheet that you want to plot on the graph 3. Specify the labels you want defined on the x-axis 4. Place title on the graph 5. Define the legends Choosing Graph Type 1. Select Graph from the menu 2. Select Type from the graph menu The following choices appear: Line Bar XY Stacked-Bar Pie 3. Select your choice Assigning Values to a Graph 1. Select A (first data range) ù Move cursor to first cell you want on the first series ù Press . (period) to anchor the cell ù Move cursor to the last cell you want defined on the first series ù Press Enter (You can have up to six series defined in a graph (only one data range in a pie chart) 2. Select View 3. Press any key to exit View Labeling the X-Axis 1. Select X from the graph menu ù Move the cursor to the first label you want defined ù Press . (period) to anchor the cell ù Move cursor to the last cell you want defined on the first series ù Press Enter 2. Select View 3. Press any key to exit View Placing Titles on a Graph 1. Select Options from the graph menu 2. Select Titles 3. Select First and enter the graph title (first line) on the command line 4. Select Titles 5. Select Second and enter the graphs title (second line) on the command line 6. Follow the same procedure for placing a title on the X-Axis (horizontal line) and Y-Axis (vertical line) 7. Press Esc to return to the Graph menu 8. Select View 9. Press any key to exit View Define the Legends 1. Select Options from the graph menu 2. Select Legends 3. Select A and enter the legend for the A range on the graph 4. Repeat for all the data ranges plotted on the graph 5. Press Esc to return to the Graph menu 6. Select View 7. Press any key to exit View MACROS To Create a Macro 1. Move to an empty cell on the spreadsheet 2. Select Range from the menu 3. Select Name 4. Select Create 5. To name a macro use \ and a letter (ex. \a) 6. Enter the location for the macro (ex. B20..B20) 6. Now type the repetitive keystrokes Executing a Macro 1. Move to the correct cell 2. Press Alt and the letter of the macro DATABASES Basics for Searching and Selecting Records 1. Establishing the input range 2. Setting up the Criterion Range (this tell the 1-2-3 what to look for!) 3. Extracting records (moving the selected records to another part of the worksheet) Establishing the Input Range 1. Select Data from the Menu 2. Select Query 3. Select Input 4. Enter the range you want the records selected from (usually the entire database) 5. Select Quit to exit the Data menu Setting Up the Criterion Range 6. Copy the output block (fields you want selected) to another area of the worksheet 7. Under the field name enter the criteria (ex. 3 under the field BDRMS) 8. Select Data Query Criteria from the menu 9. Highlight the 2 lines with the output block and criteria (see instruction 6 and 7) 10. Select Find and 1-2-3 automatically highlights the first record fitting the criteria 11. Use the down arrow to highlight each record until 1-2-3 beeps 12. Press Enter to end Find To Extract the Selected Records 1. Copy the fields you want extracted to another section of the worksheet 2. Select Data Query Output 3. Highlight the range (the field names in instruction 1) 4. Select Extract 5. Select Quit to return to the ready mode Sorting a Database 1. Select Data from the menu 2. Select Sort 4. Select Data-Range 5. Highlight the cells to be sorted 6. Select Primary-Key to tell 1-2-3 the Primary key to sort 7. Press Enter 8. Specify Ascending or Descending order 9. Select Go to perform the sort INDEX Page ABSOLUTE REFERENCE . . . . . . . . . . . . . . . . . . . . . . .6 ADDRESS. . . . . . . . . . . . . . . . . . . . . . . . . . . . .1 ADDRESSES IN FORMULAS. . . . . . . . . . . . . . . . . . . . . .5 ANCHORING. . . . . . . . . . . . . . . . . . . . . . . . . . . .2 CELL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1 CELL POINTER . . . . . . . . . . . . . . . . . . . . . . . . . .1 CHANGING COLUMN WIDTHS . . . . . . . . . . . . . . . . . . . . .8 COPYING CELLS. . . . . . . . . . . . . . . . . . . . . . . . . .9 CREATING AN ABSOLUTE ADDRESS . . . . . . . . . . . . . . . . . .6 CURSOR MOVEMENT. . . . . . . . . . . . . . . . . . . . . . . . .4 DATABASES. . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Sorting a Database. . . . . . . . . . . . . . . . . . . . 15 To Search and Select Records. . . . . . . . . . . . . . . 15 FORMULAS . . . . . . . . . . . . . . . . . . . . . . . . . . . .2 FUNCTIONS. . . . . . . . . . . . . . . . . . . . . . . . . . . .6 AVG . . . . . . . . . . . . . . . . . . . . . . . . . . . .6 COUNT . . . . . . . . . . . . . . . . . . . . . . . . . . .6 MAX . . . . . . . . . . . . . . . . . . . . . . . . . . . .6 MIN . . . . . . . . . . . . . . . . . . . . . . . . . . . .6 SUM . . . . . . . . . . . . . . . . . . . . . . . . . . . .6 GLOBAL FORMAT. . . . . . . . . . . . . . . . . . . . . . . . . .8 HELP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1 LABEL PREFIXES . . . . . . . . . . . . . . . . . . . . . . . . .2 LABELS . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1 MACROS . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Creating a Macro. . . . . . . . . . . . . . . . . . . . . 14 Executing a Macro . . . . . . . . . . . . . . . . . . . . 14 MENU . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1 MOVING CELLS . . . . . . . . . . . . . . . . . . . . . . . . . 10 OTHER FILE COMMANDS. . . . . . . . . . . . . . . . . . . . . . 12 File Combine. . . . . . . . . . . . . . . . . . . . . . . 12 File Directory. . . . . . . . . . . . . . . . . . . . . . 12 File Erase. . . . . . . . . . . . . . . . . . . . . . . . 12 File Extract. . . . . . . . . . . . . . . . . . . . . . . 12 File List . . . . . . . . . . . . . . . . . . . . . . . . 12 PRINTING . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 RANGE FORMAT . . . . . . . . . . . . . . . . . . . . . . . . . .7 Comma . . . . . . . . . . . . . . . . . . . . . . . . . . .7 Currency. . . . . . . . . . . . . . . . . . . . . . . . . .7 Date. . . . . . . . . . . . . . . . . . . . . . . . . . . .7 Fixed . . . . . . . . . . . . . . . . . . . . . . . . . . .7 General . . . . . . . . . . . . . . . . . . . . . . . . . .7 Hidden. . . . . . . . . . . . . . . . . . . . . . . . . . .7 Percent . . . . . . . . . . . . . . . . . . . . . . . . . .7 Reset . . . . . . . . . . . . . . . . . . . . . . . . . . .7 Scientific. . . . . . . . . . . . . . . . . . . . . . . . .7 Text. . . . . . . . . . . . . . . . . . . . . . . . . . . .7 RELATIVE REFERENCE . . . . . . . . . . . . . . . . . . . . . . .5 RETRIEVING A FILE. . . . . . . . . . . . . . . . . . . . . . . 12 SAVING A WORKSHEET . . . . . . . . . . . . . . . . . . . . . . 11 USING LOTUS. . . . . . . . . . . . . . . . . . . . . . . . . . .3 VALUES . . . . . . . . . . . . . . . . . . . . . . . . . . . . .