NOS : Certificate in Computer Applications
| Home | Table of Contents |

Up: Online Course Material

This lesson presents the basic concepts you need to know for working on electronic spreadsheet LOTUS 1-2-3. Various parts of a 1-2-3 worksheet have also been discussed.

At the end of this lesson, you would be able to:

• identify fundamental elements of a spreadsheet
• differentiate between table, values and formula
• use various types of operators in a spreadsheet
• use various pointer movements and function keys
• specify the concept of range and its different selection ways
• define a file and how to protect it from unauthorised users
• use HELP menu while working in 1-2-3.

A spreadsheet is simply a table or matrix of rows and columns, very similar to an accounting journal. The intersection of each row and column is called a cell. A cell can hold any type of data, including numbers, formulas and texts. The major difference between an electronic spreadsheet and an accounting journal is the enhanced flexibility, speed and accuracy provided by an electronic spreadsheet.

The spreadsheet era began in 1978 when Robert Franuston, Dan Bricktin and Dan Fylstra designed and marketed Visicalc, the most popular microcomputer software prior to Lotus. VisiCalc was quite impressive package for its time and included a matrix of 254 rows and 63 columns, many commands and several built in functions. However, if had some serious shortcomings which were improved later on in upgraded versions.

SuperCalc developed in 1980 by Sorcim Corporation, a CP/M-based program, was positively an improvement on Visicalc. This package also included 254 rows and 63 columns. Later releases of VisiCalc, such as ProCalc and SuperCalc tried to remove the shortcomings of the earlier Visicalc. Integrated packages were introduced that could perform spreadsheet analysis, data management, graphics, word processing and communication operations. These new packages included Multiplan (by Microsoft Corp.), Framework (by Ashton-Tate) and Lotus 1-2-3 (by Lotus Development Corporation).

Lotus 1-2-3 Release 1 was introduced in 1982. Within six months, it was upgraded to Release 1A and in mid-1985, Release 2 appeared on the market. Later on Release 2.1 (simple upgrade over Release 2.0) and Release 3 (major upgrade over Release 2.1) were introduced during the last quarter of 1988. Lotus 1-2-3 Release 3 has been totally re-written in C language to provide support for different hardware and has maintained compatibility with all previous releases of 1-2-3. Lotus Development Corporation has been continuously improving this product. After VisiCalc, Lotus has been one of the best sellers of all time. The numbers ‘1-2-3’ in LOTUS 1-2-3’s name indicate the three major features available in this package. These are:

• Electronic Spreadsheet
• Business Graphics
• Database Management

These three features, all of which are programs in themselves, had previously not been combined into one system. Lotus 1-2-3 combines the largest and most advance electronic spreadsheet, with state-of-the art graphics and a complete information management capacity. Thus, the combination of these features in Lotus 1-2-3 make it the first timely integrated program and one of the finest of its kind produced to date. The 1-2-3 worksheet consists of a grid of 256 columns (labelled from A-Z, AA-AZ, BA-BZ and so on) and 8192 rows (labelled from 1,2,3,4, ….,8192).

Various spreadsheet packages are available in the market such as VISICALC, SUPERCALC, QUATTRO-PRO, LOTUS 1-2-3, VP-PLANNER and SYMPHONY, etc. In this booklet, all important and commonly used features of LOTUS 1-2-3 Release 2.2 is discussed. It is a simple, powerful and well-crafted electronic worksheet program that allows you to perform all kinds of calculation and displays result on the screen. Lotus 1-2-3 can run on an IBM PC or PC-XT/PC-AT or any IBM compatible PC, though the developer recommends the following:

• Either two floppy disk drives or one hard disk drive system with at least 640 KB memory.
• A monitor (colour monitor for using colour graphics capabilities).

Top

1. What is a spreadsheet?
2. What is maximum number of rows and columns that we can have in the Lotus 1-2-3?
3. What does 1-2-3 in ‘Lotus 1-2-3’ stand for?

If your PC has a hard disk and 1-2-3 program has been installed in a directory Lotus in the hard disk then Lotus 1-2-3 can be invoked/loaded in the memory in two ways:

First method: At the C:\> prompt select the lotus directory by giving the following command:

• C:\>CD\LOTUS and press ENTER
• Type LOTUS and press ENTER

This command will display the Access System Main Menu as shown in Fig.14.1.

This menu has various options, namely, 1-2-3, Printgraph, Translate, Install, Exit. The job of these options are as follows:

Initially option 1-2-3 is highlighted.

• Press ENTER to activate 1-2-3. It will display the COPYRIGHT NOTICE as shown in Fig 14.2.

	Copyright, 1985, 1989 Lotus Development Corporation All Rights Reserved 1S00116-8661243
Licensing Information: User Name: ABC Company Name : ABC COMPANY LTD Use, duplication, or sale of this software, except as described in the Lotus License Agreement, is strictly prohibited. Violators may be prosecuted.

After a few seconds 1-2-3 worksheet will be displayed on the screen as shown in Fig. 14.3.

Second method: Select the directory Lotus containing the 1-2-3 program by giving command

• C:\>CD\LOTUS ENTER To load 1-2-3 from system prompt:
• Type 123 and press ENTER

1-2-3 will display the COPYRIGHT NOTICE and after a few seconds a blank worksheet will appear on the screen. The difference between both the methods is that in the second method you directly go into a worksheet while in the first method you will be in the Access System main menu.

Once you have started 1-2-3 or selected it from the Access System your screen will display a blank worksheet similar to the one shown in Fig14.3. To work with spreadsheet every spreadsheet user should have a clear understanding about the various spreadsheet concepts.

Various components of a worksheet as shown in Fig. 14.3 are as follows:

(a) Control Panel: The top three rows located above the horizontal border that controls the worksheet forms the Control Panel

Status line: The first line of the control panel is known as status line. It contains information about the Current Cell (see A1 at the top left corner of Fig. 14.3). Its cell address Cell contents and type of entry present in the cell, either Label, Value or Formula.

Input line: The second line of the Control Panel is called Input Line. It displays the current entry when you are creating or editing the entry. It takes the entry from the keyboard and also shows the existing value of the cell if some value is already present in the cell.

Command line: The third row of the Control Panel is called the Command Line. It displays various interactive commands and menu supported by Lotus 1-2-3. It also asks for the information required to complete a command you have selected.

(b)  Worksheet Area

Worksheet is a grid made up of rows and columns. It is the most important part of the 1-2-3 screen because all data you enter and results of calculations are displayed here. The top horizontal border of the worksheet indicates columns and the vertical border indicates the rows. The worksheet contains 256 columns and 8192 rows but it displays at a time only 8 columns and 20 rows on the screen. In a 1-2-3 worksheet row number is referred by numerals from 1 through 8192 and column number is referred by alphabets A…Z, AA….AZ, ….upto IV. (The first 26 columns are labelled A,B,C...Z. The 27th column is labelled AA the 28th AB and so on. The 256th Column is called IV). The area formed by the intersection of a column and row is called a cell. The cell is the smallest unit in the worksheet, which is used to store data. With 8192 rows and 256 columns there are a maximum number of 2,097,152 cells. The currently active cell is indicated by a highlighted video bar or cell pointer. Each cell has a unique address that consists of the column-number suffixed by the row-number. For example, the cell pointer can be moved to any cell by using , , , and arrow keys or by specifying the specific cell address along with the F5 function key.

(c)  Mode Indicator

The Mode indicator tells you in what state or condition 1-2-3 is currently operating. It is present at the top right corner of the screen. The mode indicator shown in Fig. 14.3 is MENU because the command menu is active. it changes when you begin an action. Other common mode indicators are READY, ERROR, HELP, FIND, EDIT, LABEL, VALUE, FORMULA etc.

It is located at the bottom left corner of the screen. This indicates the current system date and time. As the present date shown in Fig 14.3 is 11-OCT-1997 and time shown is 10:09 AM.

The Status Indicator appears on the bottom right corner of the screen indicating a particular program condition or key condition in Fig. 14.3. The CAPS indicator that the CAPS LOCK key has been turned on. Similarly NUM indicator indicates that the Numeric Lock key has been turned on.

Moving around the worksheet is necessary to be able to do the following:

• Make a cell entry
• Modify a cell entry
• View a cell entry

You can move around the current worksheet using the following pointer movement keys:

Keys	Explanation
or	To move cell pointer one cell up or down.
or	To move cell pointer one cell right or left.
CTRL or CTRL	To move the cell pointer one screen full to right or left.
Home	To move the cell pointer to the top of the worksheet (i.e., to the first cell A1).
Tab	To move the cell pointer 8 columns right
Shift+Tab	To move the cell pointer 8 columns left
Pg Up or Pg Dn	To move the cell pointer one full screen (20 rows) up or one full screen down

(b) Function Keys

The function keys are a group of 10 keys labeller F1 through F10 and are located on the left side of the standard IBM keyboard. These keys are used for special task. Their function in 1-2-3 are given below:

Function Key Explanation

F1 :  Activates the online help menu of 1-2-3

F2 :  Puts you in the edit mode. Thus you can modify the contents of a cell without having to retype the entire cell’s contents.

F3 :  This will allow you to enter a range name instead of typing the actual cell locations.

F4 :  This is called the absolute addressing key. When you are pointing at a cell and intend to use it in your formula, pressing this key can define the reference to be an absolute address or relative address.

F5 :  This is the GoTo key. It lets you to jump directly to the cell you typed in.

F6 :  This allows you to split the display screen into two, either horizontally or vertically.

F7 :  This key is used for advanced database applications in which you want to query the database.

F8 :  This key is used for advanced data operations. Pressing F8 will cause to repeat the last Data Table command.

F9 : This key forces the spreadsheet to recalculate. It updates all formulas in all active files.

F10 :  This key displays the most current graph if created, or create a automatic graph using the data from the worksheet.

Top

1. Match the following:

2. Match the use of the function keys.

Top

In 1-2-3, it is possible to make three type of entry. They are

(i) Text or labels

(ii) Numbers or values

(iii) Formula

When you start typing an entry, 1-2-3 determines whether the entry is a label or values based on the first character you type and change the entry mode indicator accordingly. A cell entry can be made only in the currently active worksheet cell. To enter data in a cell, you should do the following:

(i) Move cell pointer to the desired cell.

(ii) Type the data (label, Value or formula).

(iii) Press the ENTER to complete the entry.

(iv) Press the ESC key in case you want to cancel the entry.

(i) A label is a text description, such as NATIONAL, COMPUTER, SCHOOL

(ii) A label is used to increase the readability of the worksheet

(iii) A label can be up to 240 characters long and can contain any characters or numbers.

(iv) By default, a label is left aligned is a cell. The display can be altered by using the following prefixes:

(v) 1-2-3 knows that a cell contains a label if the entry begins with a letter, single quote, double quote, caret or backslash. In such a case, mode indicator displays LABEL.

While entering numbers or values in a worksheet you have to take care of the following:

(i) It is a signed or an unsigned integer or real number.

(ii) It begins with a digit (0 to 9), a plus or a minus sign (+ or -), a period (.) or a dollar sign ($).

(iii) All numbers are right aligned and the alignment cannot be changed. Trying to use one of the label prefix characters would make 1-2-3 treat this number as a label. (see part ‘v’ in the above para). In that case, if we use this number is a formula, its value would be taken as zero.

(iv) Do not use space bar to align the number or erase the number in a cell because it would then be treated as a label. While entering numbers in the worksheet, one need not to type commas (,) between digits e.g. 22,500 is not correct.

(v) While you are entering a number in a cell, mode indicator displays VALUE.

While entering formulas in a worksheet you have to take care of the following:

(i) A formula is a mathematical expression. It displays the resulting number from that expression in the cell location that contains the formula.

(ii) A formula must begin with +,-,(,@,#,$, or with pure numbers (i.e. 0 to 9). It can not start with a cell location, since address of a cell begins with a letter (e.g. A1) and would be interpreted by 1-2-3 as a label. If a formula begins with a cell address, it should be preceded by a plus (+) sign or surrounded by parentheses, for example, we can enter +A1+A2 or (A1+A2).

(iii) The symbol @ is used to form sophisticated formulas involving functions (e.g. SUM). The $ sign is used to assign absolute value.

(iv) When a formula entry is over (i.e., ENTER is pressed), only the value of the formulas appears is the cell and the formula is displayed on the first line of the control panel.

(v) When you are entering a formula, the mode indicator displays VALUE.

(vi) When the contents of any cell location being used in a formula are changed, the result is immediately reflected in the value of the formula.

(vii) Longest possible length of formula, which can be stored in a cell, is 240 characters. The various signs and symbols used in formula with their explanations are given below:

+   Addition

_   Subtraction

*   Multiplication

/    Division

^   Exponentiation

( ) Brackets

=    Equal to

< > Not equal to

>    Greater than

<    Less than

>= Greater than equal to

<= Less than equal to

# NOT #      Logical Not

# AND #      Logical And

# OR #         Logical OR

&      String combination

It should be kept is mind that all the relational operators have equal priority and are evaluated from left to right. In case of Logical operators # NOT # is evaluated first (from left to right) and after that # AND #, # OR # (from left to right.)

The order of evaluation for mathematical operators is as follows:

(i) Expressions within parentheses
(ii) Exponentiation (from left to right)
(iii) Multiplication Division (from left to right)
(iv) Addition and Subtraction (from left to right)

A cell entry can be modified as follows:

• Take the cell pointer to the cell you want to modify
• Press F2 key
• Cell contents will appear in the input line, which can be modified
• After completing the modification, press ENTER
• The modified entry replaces the existing entry in the cell.

A range is a set of adjacent cells or a rectangular block of adjacent cells. A range can be a single cell, an entire row, an entire column or even several rows and columns. You can use range in formula or commands to perform operation.

It is important to note at this stage that while working with LOTUS, the user has to define the cell range with different commands. It can be done is the following ways:

• Direct Way
• Pointing Method

Direct way: In this method, the user has to type the cell range directly with the desired cell address

This method is used in case the user remembers the cell addresses to be defined in the range.

Pointing method: In case the users do not remember the cell addresses to be specified in the range one can set the range in an interactive mode. The steps are as follows:

1. Move the cell pointer to the beginning of the range to be defined using arrow keys (e.g. cell A2)
2. Press the decimal (.) key.
3. Move the cell pointer to the end of the cell range to be defined (e.g., F15) using arrow key.
4. After completion of the range definition, press the ENTER key. This is known as anchoring of a cell and the method is known as pointing method for defining a cell range.

Since the computer’s internal memory gets wiped out by power failure, it becomes necessary to save your work you do during a 1-2-3 session. Unless you save your spreadsheet in 1-2-3, you will not be able to use the same spreadsheet again at some other time.

Once you create a worksheet file with designated command, you can read this file (Copy the file from disk to computer memory) at any time. A worksheet file in memory is called an active file. 1-2-3 has provision to have more than one file in the memory at the same time. In that case, current file is available wherever the cell pointer is displayed.

(a) Specify a File

A 1-2-3 file can be identified by specifying its path, file name and its extension. For example,

The path specifies where exactly the displayed file resides in the entire disk. In the above example C:\LOTUS\STUDENT means that the named file is available in ‘C’ drive in the sub-directory STUDENT under directory lotus. If the path is not mentioned, 1-2-3 assumes that the named file can be located in current directory.

As you are aware that every file has a unique name, which must be assigned when it is first created. While assigning a file name, you should follow the operating system rules for naming a file.

1-2-3 automatically adds a three characters extension (if not mentioned specifically) according to the type of file. The following table lists the types of extension .

Except for backup and temporary file, you can change the extension name by typing an extension when you type the file name.

You can create a worksheet file by using a /File New, /File Extract, /File Save commands of 1-2-3. Similarly you can read a file by using /File retrieve or /File Open command.

(b) File Protection

We can externally protect a file by assigning a password at the time of saving it to keep out all unauthorised users. A file saved with a password can be retrieved only if we enter the correct password. In case we forget the password, file cannot be retrieved. Password should be correct in all respect. Even it distinguishes between upper and lower case letters. For assigning a password we should follows the following steps:

1. Type /FS to select file save command
2. Now enter the file name STUDENT followed by a space, type P and press ENTER key.
3. The computer will ask for a password, type any combination of numerals and Letters up to a maximum of 15 characters and press ENTER key.
4. It will ask us to verify, type exactly the same password again and press ENTER key.
5. Type R to replace your file with password protection.

Our file will now be saved with password protection. Retrieving password protected file, we should use / File Retrieve command. When we type the name of the password protected file. The computer will ask us to type in the password. After entering the correct password, the required file will be retrieved.

Cancelling password protection: If we want to cancel password protection, we should save the file again with file save command. When the file name appears, press the backspace key to cancel the password option. Now type the file name without P and replace the file by typing R.

Changing a password: To change a password, first cancel the earlier password using the procedure as stated above. Now save the file with a new password protection using the various steps as stated earlier.

In many cases, we will find it easier and more practical to spread the large volume of data into a number of worksheet. However, if the data volume is a small one, we prefer to have single worksheet file for that. But in case of a large volume of data, use of multiple worksheets gives us more flexibility in organising and formatting the data. We can have better methods for protecting such a large volume of data while moving with multiple worksheets, and the retrieval of data can be faster and easier also. In 1-2-3, a file can contain as many as 256 worksheets, depending on the availability of computer memory. We can use /Worksheet Insert Sheet to create multiple worksheet in the memory and use /File Save command to create a single file for all the worksheets.

To get assistance while working in 1-2-3 you can press F1 key or select HELP from the menu. 1-2-3 has the facility to provide a series of help on its various commands and Functions. These helps are quite useful in the sense when you press F1, the screen 1-2-3 displays directly relating to what you are currently doing in 1-2-3. For example, if you have selected a command and pressed F1, 1-2-3 displays the help screen for that command.

If you press F1 while 1-2-3 is displaying an error message, 1-2-3 displays the Help screen related to that error message.

Top

1. What are the three types of data entry that is possible in 1-2-3.
2. Fill in the blanks.

1. A ________ is a set of adjacent cells or a rectangular block of adjacent cells.
2. By default a label is _________ aligned in a cell.
3. All the relational operators have _________ priority and are evaluated from _______ to ________.
4. A 1-2-3 file can be identified by specifying its _________, __________ and ________.

In this lesson you learnt the basic skills involved in creating and saving the spreadsheet effectively. Various components of a worksheet, pointer movement keys and use of function keys, in 1-2-3 have been properly defined.

1. What is the difference between the command line and status line?
2. What are the steps we have to follow while entering numbers or values in Lotus?
3. Define direct way and pointing method while selecting a range.
4. What are the steps to protect a file with a pass word?

1. A spreadsheet is simply a table or matrix of rows and columns, very similar to an accounting journal.
2. 8192 rows and 256 columns.
3. The three components of Lotus 1-2-3 are Spreadsheet, Graphics, and Database.

1.

1. a
2. e
3. b
4. d
5. c

2.

i) c

ii) d

iii) e

iv) a

v) b

1.

(i) Text or Label

(ii) Numbers or Values

(iii) Formula

2

(i) Range

(ii) Left

(iii) Equal, Left, Right

(iv) Path, File Name, Extension

Top