By now, we are in a position to appreciate the ease of entering the data in a 1-2-3 worksheet. We shall now learn how we can present the data in various formats, alter column widths or insert/delete rows or columns, besides various other functions in the worksheet by pressing only a few keys as compared to the manual worksheet where you have to recreate the worksheet for performing any of these simple changes. Since the size of the display screen is small, 1-2-3 provides a very useful function, which can help you splitting the screen and viewing both the parts of the worksheet simultaneously. Also, 1-2-3 allows you to set titles which would stay on the screen and it will not disappear when you move around the worksheet. Another strong feature of 1-2-3 is that you can protect cells against accidental changes.

After completing this lesson, you should be able to:

• know the general structure of 1-2-3 commands
• change the default parameters of the whole or part of the worksheet.
• insert or delete few columns or rows in between the existing ones.
• erase whole or part of the worksheet
• set titles and windows in the worksheet
• protect cells from unintentional changes or deletion.

It has been mentioned earlier that 1-2-3 is exclusively menu driven. A menu is a collection of items or options; the user selects a menu item in order to execute a specific function. The 1-2-3 commands are organised in a tree like structure of menus and submenus; a command is issued to 1-2-3 through the selection of a menu item from each of the menus (sub-menus) supplied automatically on the screen. In order to understand how commands are given in 1-2-3, let us consider a fictitious menu structure which consists of menu options such as the one depicted in Fig. 15.1.

Level - 1   Level - 2   Level - 3

Fig. 15.1 Fictitious Menu Structure

Here, the program is designed to permit the user to either read a mystery novel, a historical novel, or a textbook on physics or chemistry; or play either music or a game. Clearly, if the user wishes to read a historical novel the choice to be made are "Read" from Level 1, "Novel" from Level 2 and "History" from Level 3. Similarly if the user wishes to play a game, the options "Play" and "Game" are to be selected respectively. (Note that this programme does not allow the user to decide which particular book of a particular category will he/she be allowed to read or what kind of music will he/she have to listen to.)

Let us now return to 1-2-3 menus. The main command menu (level 1) is accessed by typing a slash (/). The menu options appear on the second line of the control panel and the first menu item is highlighted. The highlight can be moved to the other items by using the right or left arrow keys. As each options is highlighted, the third line of the control panel lists either the sub-menus classified under the highlighted menu item on the second line or an explanation of the highlighted item. A menu option is selected by highlighting the appropriate option and pressing the ENTER key or by typing in the first letter of the item. (Thus the item FILE in the menu may be selected by (i) highlighting the option "File" and pressing ENTER or (ii) by typing in the first letter of the option that is `F').

The 1-2-3 main menu options presented below:

1-2-3

Worksheet  Range  Copy  Move  File  Print  Graph   Data  System  Quit

Worksheet: This command enables you to format the overall appearance of the worksheet in terms of setting the display of entries: controlling the width of the column; setting recalculation patterns and default parameters; etc. Some other worksheet commands permit insertion and deletion of rows/columns; freezing the display of rows/column; dividing screen display into two parts; etc.

Range: A major function of the Range command is to extend settings achieved by the worksheet command to specific blocks or ranges or cells. These include controlling the display of numeric and label entries erasing; protecting or un-protecting defined ranges. Other Range commands enable the user to assign names to ranges; facilitate data entry in a range; freeze the numerical values of formulas; and, transpose data in a row range to a column range.

Copy: This command enables you to copy the contents of a range of cells on the another range of cells; the two ranges may be of unequal sizes.

Move: By invoking this command a block (range) of cells may be moved from one location of the worksheet to another.

File: File command permits you to save the worksheet as a permanent file on a diskette/hard disk; retrieve such file on a later date; save parts of worksheet as separate worksheet files; combine different files to yield a larger worksheet; import data from other programme files, etc.

Print: By this command you can take a printout of the worksheet or you can save it as ASCII file. A large number of print options enable you to produce reports as desired by you. Some Print features are: controlling the number of lines on the printed page; setting margins at left, right, top, and bottom; printing header/footer on each page; and numbering the output pages.

Graph: Five different kinds of graphs (bar, stacked bar, line, XY and pie) may be drawn using this command. Each graph may contain up to six different data curves (except pie chart). Titles may be added; legends may be attached to the data curves; data points may be represented in a various formats; etc. Graphs may also be assigned names and saved for printing or reviewing.

Data: Data commands are used for performing data analysis and database management functions. Typical analysis includes setting up what-if analysis tables; performing regression analysis; creating frequency tables; and performing matrix arithmetic. Data commands permit sorting and querying databases.

System: You can temporarily invoke Disk Operating System (DOS) while working with 1-2-3 by selecting this option.

Quit: This option will take you out of 1-2-3 and return to DOS.

The worksheet commands provide the user with most extensive menu structure of 1-2-3. The options available to you in worksheet command is presented below:

Worksheet

Global Insert Delete Column Erase Titles Window Status Page

We will discuss each of these options in detail. We begin with the option Global and its submenus.

As the name suggests, this set of commands invoked by/Worksheet Global is used to make a change for the entire worksheet. On invoking/Worksheet Global Command, a menu appears with the following options.

Global

Format Label-Prefix Column-Width Recalculation Protection Default Zero

Let us discuss each one of them beginning with Format.

This Command is used to present the numbers in the worksheet in the desired format. On invoking this command by pressing/WGF, the following options for the format appear.

Let us discuss these options one by one.

(i) Fixed: This option allows you to specify the number of places to be fixed to the right of the decimal point for the display of value entries. The permissible range for the number of places if from 0 to 15. For example, /WGFF3 will with display the value 2000 as 2000.000 (note three places to the right of decimal point).

(ii) Scientific: This option displays the values in exponential terms on the basis of the specified number of places to the right of the decimal. For example, /WGFS2 will display the value 725646 as 7.25E+05 and /WGFS1 will display the same value as 7.2E+05.

(iii) Currency: This option displays a dollar sign ($) to the left to the value and separates thousands with commas. For example, 3558976 will be formatted as $ 3,558,976 for display if 0 is specified for number of places after decimal.

(iv) , (Comma): This option is generally used to improve the vendorability by separating thousands from millions, millions from billions and so on with commas.

(v) General: This option is the default display format for 1-2-3. In this format, the insignificant zeros are suppressed for value entries.

(vi) +/-(Plus/Minus): This format creates a horizontal bar graph of + or - signs depending on the value of the cell contents. A period (.) is displayed if the cell value is zero.

(vii) Percent: This option allows you to display percentages with the number of decimal places.

(viii) Date: This option on allows you to display a date corresponding to the entered value. It provides three date display formats, which are as follows.

• Day-Month-Year
• Day-Month
• Month-Year

(ix) Text: This option makes 1-2-3 display the formulae as they are entered and not the computed values that 1-2-3 display by default.

(x) Hidden: This option hides the contents of all cells. You can use/ Range Format to hide a range of cells.

Label-prefix option may be selected to align labels with left, right or center justification. The label prefix specification under Global option changes the setting for the entire spreadsheet. As we have already discussed that for alignment of a single label entry, it may be prefixed with a single quotation mark (') for left justification, with a double quotation mark (") for right justification and with a caret sign (^) for centering. By default, all label entries are left justified.

Column-width may be selected to specify the number of characters that can e accommodated in a cell. By default the column-with for a spreadsheet cell is 9 characters. It can be set anywhere in the range of 1 to 72 characters. The command/WGC provides for two options SET and RESET. While the former allows you to change the current setting, the latter clears the setting and activates the default setting.

One of the most important features of 1-2-3 is its `what if' analysis. You can use 1-2-3's recalculation facility to see the effect of changes in the cell values. Recalculation offers the following options:

Natural Columnwise Rowwise Automatic Manual Iteration

Natural: Recalculates as and when formulas are encountered in the worksheet.

Columnwise: Recalculate column by column.

Rowwise: Recalculate row by row.

Automatic: Automatically recalculates after any change.

Manual: Recalculate only when F9 key is pressed.

Iteration: Set the number of times a calculation is to be made.

After having done the basic designing of a worksheet, there are certain cells, which would not require any change. Such cells have to be protected against unintentional changes or erasure with/WGPE command. The cell protection is enabled, i.e., no allowed to change the present contents of the protected cells unless the protection is disabled.

/Worksheet Global Default

Before we explain the options available under this command, we will once again repeat that you will be able to appreciate these commands only if you try them out. When /Worksheet Global Default command is invoked, the following six options appear:

Printer Directory Status Update Other Quit

Let us discuss these options one by one.

Printer: This sets the printer default setting. The sub-menus in the printer option are;

Interface: The computer printer interface type. The options are:

1. Parallel (Default)

2. Serial

3. 2nd parallel

4. 2nd Serial

Auto-LF:

Automatic line feed by printer. The options in this are:

Yes

No (Default)

Left:

Set left margin (default 4)

Right:    

Set right margin (default 76)

Top:      

Set top margin (default 2 lines)

Bottom:

Set bottom margin (default 2 lines)

Page-length:

Set page length (default 66 lines)

Wait :

Wait for paper change. There are two options in this;

NO

YES (default)

Set up:

Set default set up string (e.g., 015_ _ _ _ Compressed print 018_ _ _ _ turns off compressed print)

Name:

specifies which printer to use of more then one printer were selected under install command

Quit :

jumps to previous command level

Directory:

This option specifies the directory to be used for retrieval or saving of files.

Status:

This option displays the current configuration (Pointer, Help, Status, etc.)

Update:

Save the current settings to configuration file.

Other:

Specify Help, Clock and International date and time settings.

Quit:

Turn to previous command level

This command can be used to suppress the zero values on the screen. When you invoke this command, the following select options appear:

NO

The selection of Yes would suppress the zero values in the worksheet. The cells with zero values would appear blank after /Worksheet Global Zero Yes has been invoked.

Top

1. Explain the steps how you would:

a) Present the formulas in the worksheet rather then their values

b) Set the following printer parameters:

i) double spacing in the printer matter

ii) pause after printing of each page for paper all adjustment

iii) top margin = 4 lines

iv) bottom margin = 4 lines

Top

(b) The Worksheet Insert Command

This command is used to insert one or more rows (or columns) anywhere on the worksheet. Obviously, the two options categorised under this command are:

Column Row

Column (/WIC): When this optional is selected, 1-2-3 prompts the user to specify the range of columns to be inserted. The range may be specified either by typing directly from the keyboard or by pointing out to the range by expanding the cell pointer; any column falling within the range specified will automatically be shifted to the right when the command is executed. It must be pointed out here, that a range such as A1..A1 or A5..A5 implies that one new column will be inserted to the left of the existing column A. The new column just inserted will be labelled A and the old column now shifted to the right will be re-labelled B. Similarly, a range such as D6.G6 or R15..U15 results in four new columns being inserted to the left of the old columns D or R respectively.

Row (/WIR): This command works exactly like the /WIC command, except that in this case, one speaks of rows. Insertion causes the existing rows (at the current cell pointer position and below) to move downwards.

This option is used to delete a row or column from the worksheet. There are two options classified under the Worksheet Delete menu. These are:

Column Row

The two commands (/WDC or WDR) are executed in the same way as the corresponding Worksheet Insert commands. As a result of execution of the /WD or /WDR command the remaining columns/rows of the worksheet are moved to the left (in case of/WDR) or upwards (in case of /WDR). All formulae are adjusted accordingly. The formulae referring to a deleted cell will show an error (ERR).

Width of individual column may be controlled and columnar data may be hidden from view (or re-displayed) through the use of these commands. The four options available are discussed below.

Set-width Reset-Width Hide Column Display Column

Set-Width (/WCS): This option permits the user to decide the new width (in terms of number of characters) of the current column. You are prompted to indicate the new width, either by typing in the number or by expanding or contracting the cell pointer (by using the right and left arrow keys). This command is thus used to change the default setting. The maximum permissible width of a column is 240 characters.

Reset-Width (/WCR): This adjusts the width of the current column to conform with the global column width/WGC) setting.

Hide Column (/WCH): Columns (containing confidential information) maybe hidden from view by using this command: you are prompted to supply the appropriate range. Any hidden columns lying within a print range (see Print Range command) will not be printed when the worksheet data is outputted.

Display Column (/WCD): This command is used to redisplay hidden columns. You are asked to enter the range of columns to be redisplayed: the hidden columns are indicated with asterisks beside the column letter in the border area.

This (potentially dangerous) command may be used to erase the entire worksheet. 1-2-3 confirms the user's intentions by supplying the submenu: No Yes

The option No cancels the command, i.e., no data is lost. The option Yes causes all data on the current worksheet to be lost; the user is supplied with a new blank worksheet.

Selected rows towards the top of the screen, and/or selected columns towards the left edge of the screen may be frozen on the screen; these rows/columns will not scroll off the screen when the worksheet is scrolled up/leftwards. The 4 options available are described below.

Horizontal Vertical Both Clear

Horizontal (/WTH): All rows above the cell pointer will be considered to be title by 1-2-3. These will not scroll up and down, but will scroll left and right as the worksheet is scrolled.

Vertical (/WTV): All columns to the left of the cell pointer will be treated as titles. These will not scroll left or right, but will up and down.

Both (/WTB): All rows above and all columns to the left of the cell pointer will be considered by 1-2-3 to be titles. Vertical titles will not scroll left or right, and horizontal titles will not scroll up or down. The vertical and horizontal titles, will however, scroll up/down and left/right respectively as the worksheet is scrolled.

Clear (/WTC): All Worksheet Title settings are cancelled when this option is selected.

 

As mentioned earlier, 1-2-3 permits the to split the screen display into two parts. Two different, and may be widely separated, areas of the worksheet maybe viewed in both the windows. The latter is useful when, for example, you want top view the same data in two different display formats. The sub-menu provided with the Worksheet Window command offers the following options.

Horizontal Vertical Sync Unsync Clear

Horizontal (/WWH): This option causes the screen display to be split horizontally in to two parts. Rows lying above the current position of the cell pointer constitute the display in the first, or the top window. The current row and rows below it constitute the second or, the bottom window. The top window becomes the current window. The cell pointer is moved form one window to the other by pressing the F6 key and the two windows may be scrolled independently.

Vertical (/WWV): The screen display is split vertically into two parts. Columns to the left of the current column constitute initially the first window and the current column and those to its right, the second window. The F6 key causes the cell pointer to jump from one window to the other; and as with horizontal splitting the worksheet may be scrolled independently in the two windows.

Sync (/WWS): The two window scroll synchronously when the worksheet is scrolled. Synchronous scrolling means that for horizontally split windows the same column (columns with the same labels, i.e., A,B, etc) appear in both the windows; and for vertical windows, the same numbered rows appear in the two windows as the worksheet is scrolled. Synchronous scrolling is the initial default setting.

Unsync (/WWU): Different sets of twenty rows, and differently labelled columns may be viewed in the two windows for vertical and horizontal splitting respectively.

Clear (/WWC): This option is used to clear all settings that may have been achieved through the Worksheet Window commands.

Note that, if one wishes to create unsynchronised windows, either horizontal or vertical, one has to execute the Worksheet Window command twice, i.e. /WWU and, /WWH, or /WWV. The two commands may, however, be executed in any order.

 

The function of this command is to provide a report on the Worksheet Global settings, memory use, and hardware options. The information is provided on the screen and temporarily replaces the worksheet data. Pressing any key returns the user to the worksheet in the `READY' mode. The items of information provided are: the amount of available memory: the name of the coprocessor chip, the recalculation order, method and number of iterations; circular references; selected information on cell display, i.e., format, label-prefix, column-width, the appropriate zero suppression/option; and the protection status.

This command inserts a page break into a worksheet. When the worksheet is printed, data lying below the page break is printed on a new page. Before this command is executed, the cell pointer must be brought to the row below where the user wishes the page to end.

Top

1. State whether the following statements are true or false.

(i) /Worksheet Insert Column command inserts columns after the column at which cursor is positioned.

(ii) /Worksheet Column Reset Width requires columns-width as a parameter.

(iii) /Worksheet Column Hide and /Worksheet Column Display both require Column-Range as parameter.

(iv) /Worksheet Title Clear command clears the titles by erasing information in horizontal vertical titles.

(v) /When you create a window in a worksheet and move the pointer to one of the windows, the contents of the other window do not follow.

Top

  15.5 HANDLING A RANGE

By now you have learnt the Worksheet commands. In this section we will proceed to Range commands.

Many of the options under Range are similar to that of Worksheet, with the only difference that they work on a specified range rather than the whole worksheet (As the name suggests). In such cases, we shall refer to the corresponding section of worksheet command and not discuss the option in detail.

When you invoke the Range command you will see the following options:

Format Label prefix Erase Name Justify Protect Unprotect Input Value Transpose

We will discuss these options below.

When you invoke the /Range Format command, the following options are available:

If you look into the options available under Worksheet Format (see Fig. 15.5) and the Range Format (see Fig. 15.7) there is a close similarity between them. (/RF has one extra select option -Reset.) The options available under Range Format have the same usage and parameters required as /WF. The difference between the two is that while /WF works for the whole worksheet, /RF is applicable to certain range within the worksheet. Thus options offered by /RF has one additional parameter - Range of cells to be formatted. Suppose that you wish to present the numbers in cells A1, A2, A3 and A4 as percentage with three digits after the decimal. Following are the steps involved:

1. Position the cell pointer at A1

2. Invoke /Range Format Percent.

3. System responds: Enter number of decimal places (0..15):2.

    Type 3 and press ENTER .

4. System responds : Enter range of cells A1...A1

Note that the courser is anchored at A1. Move it downward to include A2, A3 and A4 and press (ENTER). You will note that the numbers in the range A1..A4 are presented as percentages with three digits after the decimal.

On invoking the /Range Label-Prefix command, the following select options for the alignment of labels in the cells are offered

/Range Erase Command is used to erase the entries in a range of cells in the worksheet. On invoking this command, you are required to specify the range to be erased and you will find that the cells in this range become blank. Note that the cells protected by /Range Protection will not be erased by this command. When you use /RE, make sure that the formulas using any of these cells are not affected by the erasure.

Lotus 1-2-3 permits the user to name a range of cells. The naming of ranges is particularly useful in macros, about which we shall study later on. Name of a range can be specified instead of specifying the range. When you invoke the /Range Name command, the following options appear:

/Range Name Create is used to name a range of cells. The steps involved to create a range name are:

• Position the pointer to one corner of the range to be named.
• Invoke /RNC
• System prompts Enter Name
• Type in the name you wish to give to the range and press ENTER.
• Expands the cell pointer to the other corner of the rage and press enter.

/Range Name Delete is used to delete a specified range name.

/Range Name Labels is used to create the range names but here the range names are the labels in the cells adjacent to the cells to be named.

/Range Name Reset can be used to delete all range names.

/Range Name Table is used to create a table of two columns with all the range names of the worksheet in one column and their corresponding address in the other. The table is created in an empty area of the worksheet. To create a table:

• Position the pointer at the chosen table location, say, H26
• Invoke /RNT.
• All range names appear in alphabetical order in column H from row 26 onwards and their corresponding cell address in column 1 from row 26 onwards.

Top

IN-TEXT QUESTION 15.3

1. Write the steps involved in:

i) Centering the labels in the range A1..H20.

ii) Use the row headings A1..J1 as range names for the entries in the range A2..J2 and subsequently create a table of range names at IA1.

Top

/Range Justify command is used to change the paragraph width of a column of labels: A paragraph is a single column of consecutive labels. A number, formula or a blank cell marks the end of the paragrpah. When you invoke /RJ, you are required to specify the justify range. Expand the reverse video bar (or the highlight tea bar) to specify the new paragraph width and press ENTER

Consider the following entries in the column A of the Worksheet (see Fig. 15.6).

1. Invoke/RJ.

2. Mark the range A1. B1 in response to Enter justify range: A1..A1

3. Press ENTER.

The display of column A changes to (see Fig. 15.9)

/Range Protect command is used to protect a range of cells from accidental change or deletion. This command is typically used to freeze a range of cells containing formulas and macros to protect them from accidental change or deletion. Use /Range Unprotect to unprotect a range of cells. Remember that for using both /Range protect and /Range Unprotect, the protection feature must first be set using /Worksheet Global Protection Enable command. With both /RP and /RU, you are required to specify the range to be protected and unprotected respectively.

(g) Input

/Range Input command is used to mark a range of cells of input by limiting the movement of the cell pointer to this marked range only. When you invoke /RI, the computer prompts you to enter the range of cells to be marked for input. This command is typically used in a situation where the worksheet has been fully developed and a few cells need updation from time to time. Within the range that you wish to mark for input, if there are any cells that do not need updation, you can protect them using /WGPE and then /RP commands.

Many times we need to copy the value in one cell to another. If the cell contains a formula then such formula needs to be converted to value. For example, cell C2 contains a formula (A2+B2). If A2 contains 5 and B2 contains 6 then C2 will show 11. To copy the values of C2, that is 11, and not the formula (A2+B2) to another cell, you can make use of /Range Value command. When you invoke this command, you are required to specify the range to copy from and the range to copy to.

If you wish to copy a range of cells to another part of the worksheet but in a transposed manner (i.e., row information appears in columns and vice versa), you can make use of /Range Transpose command. You will be required to specify the range to copy from and the range to copy to.

Top

IN-TEXT QUESTIONS 15.4

1. In the range A1..H20, only cells B2, B3, C2, C3 need changes. Protect the rest of the range from accidental changes.
2. Copy the range A1...D2 To H5...H18.

Top

In this lesson, we have discussed the various worksheets and range handling commands. The main points discussed were:

• values in whole or part of worksheet can be presented in various formats, e.g. fixed, scientific, currency, per cent, etc.
• The alignment of labels in the whole or part of worksheet can be changed.
• Columns/rows can be inserted/deleted; column width may be changed.
• Horizontal and/or vertical titles can be created. These titles remain stationary when you move around the worksheet
• Windows can be created to view two different parts of the worksheet simultaneously, thus removing the handicap of a small display screen.
• The cells containing formulas, macros, etc need not be changed often. Such cells can be protected against unintentional changes or deletion.

1. Describe three options for defining a cell range.
2. List out various options available under WORKSHEET command.
3. List out various options available when you invoke /Range Format command.
4. List out various main menu options under 1-2-3.

1.

(a) Invoke/Worksheet Global Format Text.

(b)

i) Invoke/Worksheet Global Printer Auto-LF Yes

ii) Select option/Worksheet Global Printer Wait Yes

Press 4 followed by ENTER

iii) Select option/Worksheet Global Printer Bottom

Press 4 and then ENTER.

IN-TEXT QUESTION 15.2

1.

i) False

ii) False

iii) True

iv) False

v) True

IN-TEXT QUESTION 15.3

1.

(i) Position the cell pointer at A1.

Invoke/range Label-Prefix Centre

Move the pointer to mark the range A1..H20 and press ENTER

(ii) Position the cell pointer at A1

Invoke/range Name Label Down

Move the pointer to mark the range A1..JI

Press ENTER

Move the cell pointer to (A)

Invoke/Range Name Table

IN-TEXT QUESTIONS 15.4

1.

Invoke/Worksheet Global Protection Enable

Position the cell pointer at A1

Invoke/Range Protect

Mark the range A1...H20

Move the cell pointer to B2

Invoke/range Unprotect

Mark the range B2..C3

2.

Move the cell pointer to A1

Invoke/range Transpose

Mark the range A1..D2

Move the pointer to H5 and press ENTER.