NOS : Certificate in Computer Applications
| Home | Table of Contents |
LESSON 17
MANAGING A SPREADSHEET
17.1 INTRODUCTION
After you have entered the data and designed your worksheet as per your requirement, you would probably like to take a print out of this worksheet or store it for future reference through the print on the paper. Also, after having designed the worksheet you may like to use this data along with the data from another related worksheet. Nevertheless, you would invariably like to analyse the data of the worksheet. The graphs help in illustrating the behaviour of the data. For example, the dependence of two variables on each other, i.e., how one changes with a change in the other; how different variables behave over a period of time, etc. With the help of graphs, these behaviours are brought out more clearly. Lotus 1-2-3 helps you in achieving all these objectives with a lot of ease.
17.2 OBJECTIVES
After going through this lesson, you should be able to
17.3 1-2-3 FILE FUNCTIONS
When you invoke the File command by /File you will see the following menu options:
File
Retrieve Save Combine Xtract Erase List Import Directory
We discuss below the use of these options one by one.
/File Retrieve command is used to retrieve an existing worksheet file from the disk and make it the current worksheet. You have already known the use of /File Save command in the previous lesson (Refer, Section 16.6)
/File Combine is a very useful command of Lotus 1.2.3. It permits you to make use of the information of two separate worksheets. You can copy the entire worksheet or a part of it to another worksheet. When you invoke /File Combine command the system provides you with three options as follows:
Combine
Copy Add Subtract
The role of these options is as follows:
In each of these options, the following choice is offered:
Entire file Named/Specified Range
If you choose to Copy, Add or Subtract an entire file, you are required to name the file to be combined with the current file. On the other hand, suppose you want to copy the range A1..D5 of file NOS.WK1 to file NOS2.WK1. In this case, you select Named/Specified Range. The computer will prompt you to 'Enter Range Name or Coordinates'. You specify the coordinates A1..D5. Another way is to give a name the range A1..D5 by the /Range Name Create command (discussed earlier) and enter that name. The next step is to give the file name from where you want to copy.
The worksheet format settings of the specified worksheet are not copied to the current worksheet. The absolute as well as relative cell references in the specified worksheet get reset with the reference cell as the upper left hand corner of the range combined.
All References in the macros of the specified file do not change when combined with the current file.
/File Xtract is another command of Lotus 1-2-3 which allows handling of two files simultaneously. In contrast to /File Combine command which allows you to bring in data from a specified file, this command allows you to transfer values from the current file to a specified file. This command could be used to break up the current worksheet into smaller worksheets or to transfer values to a file related to the current file. When you invoke /FX, the following options appear:
Formulas Values
When you select the option formulas, i.e., you invoke /FXF, in the extracted file, formulas are copied as formulas. On the other hand, if you select option Values, i.e., you invoke /FXV, only the values of the formulas appear in the extracted file. Make sure that the extracted formulas do not refer to cells outside the range to be extracted.
In each of these options, you are required to specify the file name to which extracted information is to be transferred and the range to be extracted.
(c) File Erase Command
/File Erase command can be used to erase a file. When you invoke /FE, a menu with the following options appears:
Erase
Worksheet Print Graph Other
/File Erase Worksheet command can be used to erase a worksheet file (i.e., file with extension ".WK?" . Note that in '.WK?' the '?' is a wild character which means that any single character to follow WK. System prompts you to enter the name of the file to be erased.
/File Erase Print is used to delete a print file, i.e., one with extension '.PRN'. System prompts you to enter file name.
/File Erase Graph deletes a picture file created using Graph command of Lotus 1-2-3. These would be the files with extension " .PIC'. The system asks you to enter the file name.
/File Erase Other deletes a file other than a Worksheet, Print or Graph file, i.e. a file with extension other than: "WK?", ".PRN" or ".PIC".
(d) List Command
/File List command lists all the files of a particular type in the current directory. The options for this command are exactly same as /File Erase command.
(e) Import Command
/File Import command enables you to copy information from a print file into a worksheet. We shall learn about the print files in the next section of this lesson. /FI combines information in a print file with the information in the current worksheet file. It allows you to import standard ASCII files having an extension of ".PRN". When /FI is invoked, the following menu is presented:
Text Numbers
/File Import Text would enter each line of the imported file as a single label. The computer asks you to enter the name of the print file to be imported, i.e., a file with extension ".PRN".
/File Import Number would enter only numbers from each line of imported file. You are prompted to enter a print file name.
(f) File Directory Command
/File Directory command is used to change the default directory, i.e., the directory the files are saved into, retrieved, or listed from, for the current 1-2-3 session. When you invoke this command, you are required to enter the name of the directory (with its path) to be made current. Suppose that you are currently in C:\ LOTUS directory and you wish to perform all future file functions from a sub directory NOS in the directory LOTUS.
When you invoke /FD the computer prompts you:
Enter current directory :C:\ LOTUS
You have to type in \LOTUS\NOS and press ENTER. The systems change to the sub-directory NOS.
IN-TEXT QUESTIONS 17.1
(i) /File List and /File Directory.
(ii) /File Xtract and /File Combine
17.4 PRINTING A FILE
/Print command presents the following menu:
Printer File
/Print Printer command sends the output to the printer, whereas the /Print File command sends the output to a disk text file. This text file is given extension ".PRN" by Lotus 1-2-3 and can be edited by the Word Processor. This file can be printed using TYPE command at Disk Operating System (DOS). Whether you invoke /PP or /PF, the following menu options appear:
Printer File
Range Line Page Options Clear Align Go Quit
We will now discuss the use and usage of each one of them using /PP.
(a) Range Specification
Range: /Print Printer Range command is used to specify the range of the worksheet to be printed. When you invoke /PPR, the system prompts you to inter the range to be printed.
(b) Paper Manipulation Commands - Line, page and Align
These commands are used to manipulate the paper on the printer to make the printed worksheet more presentable.
Line: /Print Printer Line command is used to insert blank lines between two printed ranges. When you invoke /PPL, you will notice that the paper would move up one line.
Page: /Print Printer Page command is used to advance the current paper on the printer to the top of the next page.
Align: /Print Printer Align command is used to align the paper in the printer. This command resets the line number counter and 1-2-3 answers that it is at the top of the page.
(c) Options
/Print Printer Options command is used to introduce some printing enhancements to increase the readability of the printed worksheet. It is used to set up header, footer, margins, borders, page length, etc. When you invoke /PPO, the following menu is presented:
Header Footer Margins Borders Set-Up Pg-length Others Quit
/Print Printer Options Header allows you to enter a header line which shall appear on top of each page of the printed worksheet.
/Print Printer Options Footer allows you to enter a footer line which shall appear on the bottom of each printed page. You will notice that though you have specified a footer, it may not appear at the bottom of the last printed page when you ordered the print out by /Print Printer Go (which we will learn in this section). This may happen because the number of lines printed in the last page may be less than the specified printed page length in a /Print Printer Range command. In such cases, footer is printed only when you invoke /Print Printer Page command.
/Print Printer Options Margins allows you to enter the number of lines/characters to be used as margins for the option selected. There are four options: Left Right Top Bottom. These are the options presented when /PPOM is invoked.
/Print Printer Option Borders command presents the following options:
Columns Rows
/PPOBR selects rows to be used as column headings on top of every page.
/PPOBC selects columns to be used as row descriptions on left side of every page, irrespective of the range selected for printing.
/Print Printer Options Set-up sends coded instructions, for special printing features, to the printer whenever/Print Printer Go command is invoked.
/Print Printer Options Pg-Length is used to specify the number of lines on each page of the printed worksheet.
/Print Printer Options Other command presents the following menu:
As-displayed Cell-formulas Formatted Unformatted
/PPOOA prints the worksheet range as it is displayed.
/PPOOC prints the formulas rather than their results for each cell in the worksheet.
/PPOOF prints the worksheet range with the headers, footers and page breaks where they occur.
/PPOOU prints the worksheet range ignoring the headers, footers or page breaks in the worksheet.
/Print Printer Options Quit causes 1-2-3 to jump to one level higher in the menu, i.e., to Print Printer menu.
(d) Clear, Go and Quit
Clear: /Print Printer Clear command is used to cancel all or some of the current print settings. When you invoke /PPC, the following menu is presented:
All Range Borders Format
/PPCA would clear all print settings and restore default settings.
/PPCR clears the specified print range.
/PPCB clears the borders.
/PPCF clears all format settings like margin, page length and set-up strings.
Go: When the range to be printed has been specified and options and alignments required have been specified, the printing of the worksheet range may be ordered using /Print Printer Go.
Quit: /Print Printer Quit command is used to quit the print menu.
IN-TEXT QUESTIONS 17.2
1. Write the commands to achieve the following:
Print the whole of current worksheet giving a title "BALANCE SHEET: 1997-98 on top of each printed page of the worksheet. Give margins of four characters on top and left in the printed page.
17.5 GRAPHS IN LOTUS 1-2-3
Graphs or charts are the pictorial presentation of the data and thus easy to comprehend and effective for the purpose of analysis. The graphs made by Lotus can be stored, viewed or can be printed through a printer or plotter.
The graphs can be made, if the data is present on the currently active worksheet or is stored under same or different graph file names. These graph file names form different files with the extension of .PIC (PIC indicates that it is a picture file).
1-2-3 facilitates to make 5 different types of graphs.
| A | B | C | D | E | F | |
| 1 | PKG COMPANY LIMITED |
|||||
| 2 | (SALES FIGURES DURING 1997-98) |
|||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | OFFICE | MAR-MAY | JUN-AUG | SEP-NOV | DEC-FEB | |
| 7 | ||||||
| 8 | ||||||
| 9 | BOMBAY | 60000 | 45200 | 58000 | 65000 | |
| 10 | MADRAS | 87800 | 83000 | 77058 | 95000 | |
| 11 | DELHI | 12670 | 22500 | 22058 | 19200 | |
| 12 | CALCUTTA | 50000 | 57000 | 48000 | 60000 | |
| 13 | ||||||
| 14 | ||||||
| 15 | ||||||
Fig. 17.5
Assume that data present in the worksheet as shown in Fig. 17.5 reflects the sales at their various offices in each quarter cycle of the Year 1997-98. Now suppose management wants to take the company's future planning decision by analysing the previous year's sales trend. The best and easy way of analysing the data will be in the graphical form.
17.6 HOW TO PLOT GRAPH
In graphs plotting two axes named as X axis and Y axis are required as displayed in Fig. 17.6.
Fig. 17.6
Lotus allows you to have one data range on X-axis and maximum of six data ranges on Y-axis, except for pie graph. In case of pie graph, LOTUS allows you to have only one X-axis and one Y-axis.
As mentioned above LOTUS 1-2-3 supports 5 types of graphs. GRAPH Menu can be invoked by pressing /G. This menu will display the following sub-options on the command-line:
Graph
Type X A B C D E F Reset View Save Options Name Quit
The explanation of the above sub-options is as follows:
Type option is used to select the type of graph from various graph types. (e.g. Line, Bar, XY, Stacked bar, Pie Charts Etc.)
X option is used to select the data to be specified along the X-axis. Usually range defined is range of labels. But it can be numbers also. In case of XY graph, it should be numbers only.
A, B, C, D, E, F are used to select one or maximum six data ranges allowed along the Y-axis which is applicable for all types of graphs except for the pie graph.
RESET option is used to remove the previous setting of the graphs made.
SAVE option saves the graph under graph filename.
OPTION is used to select the various options (e.g. Legends, Data-labels, titles) to be used with the presently drawn graph.
NAME option is used to assign a name to the setting for a graph so that the graph can be retrieved by the specified name. The procedure of which is explained in the following pages.
QUIT option is used to exit the Graph menu: you return to the "ready" mode.
The Type option mentioned in the beginning has following sub-options:
Type
Line Bar XY Stacked-Bar Pie
You can choose the appropriate graph type by typing the first character of the sub-option or by moving the cursor at the desired sub-option and press ENTER key.
(a) Line Graph
This option is used to draw a Line graph. Two coordinates, viz., X an Y, have to be specified.
Consider the sample worksheet Fig 17.5 again. To project the performance of the various offices against various quarters with the help of the line graph, i.e., sales of each office in MAR-MAY, JUN-AUG, SEP-NOV and DEC-FEB respectively, the procedure is as follows:
In LOTUS 1-2-3, only one range can be defined as the coordinates for X-axis. Range defined is usually a range of labels but it can be numbers also. Consider the sample worksheet as shown in Fig 17.5. The X-axis range in this can be A9 to A12.
In LOTUS 1-2-3, maximum 6 ranges can be specified along the Y-axis (i.e., from A to F in the sub-options). This range is usually a range of data. The Y-axis ranges can be defined as per the Fig 17.5, i.e., B9.. E12 (4 data ranges).
(b) Viewing A Graph
Graph can be viewed by pressing F10 function key or by invoking the View command from the Graph menu.
A graph can be re-drawn after modifying the values with single keystroke F10 (as shown in Fig 17.9).
The F10 key in Ready mode automatically draws a graph according to the current graph settings. If any changes have been made in the values (which fall in the graph data range). LOTUS 1-2-3 will display a new graph.
Fig. 17.9
(c) Saving Graphs
Graphs are saved on the disk by the following command:
The computer will ask you to specify the name of the current graph to store on the disk. Graph filenames are saved with extension of PI,.
(d) Bar Graphs
Define X-axis as defined in the Line Graph, the data range along X-axis is (A9..A12). A bar graph displays values in the form of bars of different heights which can be compared to other data bar or multiple bars. Multiple shades, colors or labels can be used to differentiate one bar from another. A bar graph is shown in Fig 17.10.
Fig. 17.10
A comparative study of the sales-figures of various offices in different quarters (i.e., sales of Bombay, Madras, New Delhi and Calcutta in MAR-MAY, JUNE-AUG, SEP-NOV and DEC-FEB respectively). This can be better visualised with the help of a BAR graph, the procedure of which is as follows:
For a single range bar graph, sub-option (A) is used to indicate the single range of values along y-axis, see the Fig 17.5 for a multiple range bar graph, sub-options (A-F) are used along Y-axis. In the Fig 17.5 the ranges along the Y-axis are:
B9..B12
C9..C12
D9..D12
E9..E12
(e) Pie Graphs
In pie graphs data range can be shown as quantitative percentage of the whole data.
Consider the sample worksheet Fig 17.5 again. A comparative study of sales of various offices in a particular quarter can be made easily using PIE graphs because only one range can be mentioned in case of pie graphs. The pictorial display of the sales can be produced in a better way with the help of the PIE graphs. The procedure to draw a PIE graph with the help of the data given in the fig 17.5 is as follows:
Now view the graph with help of VIEW command as explained earlier. The graph made with data displayed in fig 17.5 is shown in Fig 17.11.
Fig 17.11.
(f) XY Graph
The format of XY graph is similar to Line graph. The only difference is that it should have numeric calculus on both the axes. That means Labels cannot be used in XY graphs. Consider the worksheet displayed in Fig. 17.12.
| A | B | C | D | E | F | |
| 1 | POPULATION IN THOUSANDS | |||||
| 2 | ||||||
| 3 | AGE GROUP | TAMILNADU | KERALA | |||
| 4 | 0-9 | 400 | 200 | |||
| 5 | 10-19 | 200 | 450 | |||
| 6 | 20-29 | 250 | 300 | |||
| 7 | 30-39 | 150 | 250 | |||
| 8 | 40-49 | 220 | 275 | |||
| 9 10 |
50-59 | 350 | 175 | |||
Fig. 17.12
To project the population of different cities against various age groups (as shown in Fig 17.12) use following commands:
Fig. 17.13
(g) Stacked-Bar Graph
In this type of graph, the comparative values are stacked together, one above another in the form of bar. Consider the Fig 17.5 again to make a pictorial presentation of the data values in the form of data blocks stacked together. This can be better visualised with the help of Stacked-Bar graph. The procedure to draw a stacked-bar graph is as follows:
The graph (STACKED-BAR) made by the above-mentioned steps is shown below.
Fig. 17.14
17.7 PRESENTING A MEANINGFUL GRAPH
Once a graph have been created, one can make it more presentable by putting legends, defining data labels, assigning colours, etc. For this kind of requirement, Lotus Graph menu offers various Options. The moment /GRAPH OPTION is selected, it will display the following sub-options:
LEGENDS FORMAT TITLE COLOUR B&W DATA-LABELS
The explanation of each sub-option is given below:
(a) Legends
Legends are used to uniquely identify what each symbol or color represents in a graph as shown in Fig 17.15. This can be obtained using the following commands:
Legends can be specified for A, B, C, D, E or F data ranges.
(b) Data-Labels
Data labels are the labels of the data value put on the appropriate parts of the graph as indicated in Fig. 17.15. The Data-Labels can be defined by:
These data labels can be put on the graph as Central/Left/Right aligned. If a data label cell contains a formula, only the data value is reflected on the screen.
(c) Titles
Titles can be put as top heading and side heading of a graph. Lotus allows to type maximum four titles, viz; First, Second, X-axis and Y-axis.
FIRST : This is to define the first top heading line. A maximum of 39 characters can be entered in this option (see Fig. 17.15, e.g., Plant production Chart of XYZ Company)
SECOND: The second top heading (if any) can be defined in this option . This is going to appear below the top heading line, i.e., First Title.
X-AXIS: appears on the X-axis horizontally (see Fig. 17.15, e.g., Years)
Y-AXIS: appears on the Y-axis vertically (see Fig. 17.15, e.g., Production Units).
(d) Color or B & W
Usually a graph can be set with shades of B & W unless you have a colour monitor. These shades/colours are used to distinguish the data ranges. To define the colour use the command:
Redraw the graph by pressing F10 key and note the difference.
Fig. 17.15
17.8 NAMING A GRAPH
The sequence of following commands is used to store the settings of current graph under a specified name to use that in future:
Maximum 15 characters can be used to name a particular graph.
The sequence of following commands reinstates a named group of settings and automatically redraws the graph.. The settings can be modified and saved under a graph name.
17.9 PRINTING OF GRAPH
Graphs can be printed by the PRINTGRAPH program from the main menu of Lotus. One can use the PRINTGRAPH to produce one or more graphs. Print Graph can only print graphs on a graphics printer or plotter. Print Graph main menu has the following options:
Image-Select Settings Go Align Page Exit
Before you print the graph, you must choose Settings in PrintGraph. SETTINGS option allows you to specify the following sub-options:
Image Hardware Action Save Reset Quit
IN-TEXT QUESTIONS 17.3
17.10 WHAT YOU HAVE LEARNT
In this lesson, you learnt the FILE, PRINT and GRAPH commands of LOTUS 1-2-3. The main points discussed are:
A saved worksheet can be made current by using /File Retrieve command. Current worksheet can be combined with another worksheet on the disk by copying, adding or subtracting their data ranges. A part or whole of a worksheet can be printed using options that would make the printed data more descriptive. Various graphs such as Line, Bar, Stacked bar or Pie can be created to illustrate the behaviours of the data in the worksheet. Simultaneously six different data ranges can be plotted on the same graph. Descriptive notes can be given for the graph, X and Y-axis, data ranges, data labels to make the graph self-explanatory. On a colour monitor, the graph can be presented in colour.
17.11 FEEDBACK TO IN-TEXT QUESTIONS
IN-TEXT QUESTIONS 17.1
1. (i) /FILE LIST command lists all the files of a particular type in the current directory. /FD is used to change the default directory for the worksheet.
(ii) /FX command allows handling of two files simultaneously.
/FC is very useful command of LOTUS 1-2-3, which allows you to make use of the information of two separate-worksheets.
2. /FILE COMBINE
IN-TEXT QUESTIONS 17.2
1. Press HOME to position the cursor at A1
Type BALANCE SHEET 1997-98
Type 4 and press ENTER
Type 4 and press ENTER
IN-TEXT QUESTIONS 17.3
1. Different types of graphs are:
2. In case /GRAPH OPTION is selected, is will display the
following options:
LEGENDS FORMAT TITLE COLOR B&W DATA-LABELS
3. Print Graph program has the following options:
IMAGE-SELECT SETTINGS GO ALIGN PAGE EXIT