12 ELANGO RMK

VI. Spreadsheet

Paragraph Answers

1.What are the advantages using Electronic spreadsheets

The following are some of the main advantages of electronic spreadsheets:

• Calculations are automated through the built-in mathematical, financial and statistical functions.

• Accurate results to any desired level of decimal points are possible

• Worksheets can be quite big in size

• Any part of the worksheet can be viewed or edited.

• Worksheet can be saved and retrieved later.

• Any part or whole of an existing worksheet can be merged with any existing or new worksheet.

• Any part or whole of the worksheet can be printed in a desired format.

• Worksheet data can be viewed in the form of graphs or charts

• The worksheet information can be transferred to any database or word processing software.

2.Define spreadsheet and describe the staroffice calc window.

• A Spread Sheet is a software package
• A spreadsheet is divided into columns and rows.
• The intersection of row and a column is called a cell.
• It is like a grid of cells with a programmable calculator attached to each cell
• At the top of the window is the Menu bar.
• The menu will displaying a list of options and you can select any option by clicking on it.
• Below the Menu bar is the Main toolbar.
• Below the Main toolbar are the Function bar and the Object bar.
• These bars have shortcut icons for frequently done tasks.
• Below these bars is the Formula bar.
• This bar is used to display the current cell and its contents.
• It also has a few more shortcut icons.
• Below the formula bar are
• The column headings & the row headings of the worksheet.
• The data area is in the middle of the window.
• At the extreme bottom are the Status Bars and Scroll bars

3.How to enter Data in the worksheet?

• The active cell is identified by the cell pointer which is a rectangular box covering that cell.
• The data to be entered in the active cell

• To begin with, the cell pointer is always in cell A1.
• The position of the cell pointer can be changed by mouse or keyboard.
• After selecting the cell, enter the data.
• If the data entered is a number, the program recognizes that as a number and allows you to perform calculations on it.
• If the data entered is a word, the program recognizes it as a label and does not permit you to perform calculations on it.
• The syntax of formula begins with an equal sign(=) followed by a combination of values, operators and cell references.
• To enter the date and type it in as MM/DD/YY.
• To enter the time and type it in as HH:MM:SS.

4.How to edit the data in the worksheet?

To edit the data present in a worksheet,

first open the worksheet by clicking on File→ open.

Next, move the cursor to the cell, which you want to edit.

You can edit the contents in the following two ways:

1. Type in the new data. The new data will simply overwrite the old contents of the cell.

2. Click on the formula bar with the mouse, press the F2 function key or simply double-click on the cell.

A vertical cursor appears on the formula bar.

Move the cursor to the left using the left arrow key or the backspace key and edit the data.

5.Explain the types of operators of staroffice Calc with an example.

The various operators available for calculations, in StarOffice Calc are given below:

• Arithmetic Operators
• Comparative operators
• Text operators
• Reference Operators

Arithmetic Operators

These operators return numerical results

Operator Name Example

+ Addition 1+1

- Substraction 2-1

- Negation -5

* Multiplication 2*2

/ Division 9/3

% Percent 15%

^ Exponentiation 3^3

Comparative operators

These operators return either true or false

Operator Name Example

= Equal A1=B1

> Greater than A1>B1

< Less than A1<B1

>= Greater then or equal A1>=B1

<= Less then or equal A1<=B1

< > Not equal A1< > B1

Text operators

The operator combines sections of text to the entire text.

Operator Name Example

& (AND) “star & “office” gives Staroffice

Reference Operators

These operators are used to combine cell areas

Operator Name Example

: (colon) Range A1:C5

! (Exclamation) Intersection SUM(A1:C5!E1:H5)

6.How can you generate a series of value? Explain with an example. Or Explain Fill command in Starcalc.

• AutoFill automatically generates a data series based on a defined pattern.
• On a sheet, click in cell, and type a number.
• Drag the fill handle in the bottom right corner of the cell across the cells that you want to fill, and release the mouse button.
• The cells are filled with ascending numbers.

To copy the contents of a cell,

• Click and drag to highlight all the cells where you want to copy the contents.
• select Edit ->Fill ->Down (or Left).
• The content of first cell will be copied in all the highlighted cells

To generate a series of data

• Select the cells .
• Choose Edit ->Fill -> Series.
• Select the direction and type of series from the options
• Enter Start value,End value and Increment
• Click on OK

7.What are the functions? How can you use them in your worksheet? Explain with an example.

Functions are predefined formulae that are available in StarOffice Calc.

The functions are available in Function Wizard window.

To select a function,

Click on Insert -> Function (or) click on Function wizard icon

The Function Wizard dialog box appears.

• Functions are displayed in the Function box
• In the Category box.
• If All category is selected all the function are displayed
• If a category (e.g. Mathematical) is selected that category alone will be displayed in the function box.

• Select the category in the Category box.
• Click once on the function name to see a short description of that function on the right side of the window.
• Double – click on it to insert it into the worksheet.

For example

To insert the SQRT function

• Click on the cell
• click on the Function Wizard icon.
• Select Mathematical from Category.
• Select the SQRT function and double click on it.
• Enter the number or the cell address on which the function should work

8.Explain the different formatting options available in Staroffice Calc.

Listed below are some of the formatting options available in StarOffice Calc.

Bold icon is used to display data in bold.

Italic icon is used to display the data in italics.

underline icon is used to underline the data in highlighted cells.

• Change Font icon. Is used to change the font style of the data
• Font size is used to change the font size of the data..
• Font Colour icon is used to change the font colour.
• These are the Align Left, Align Center, Align Right and Justify icons.

They are used to align the contents of cells.

• Number Format: Currency icon will display the contents of the selected cells in currency format,that is with a $ in front and with two decimal digits.
• Number Format: Percent icon will display the current contents in percentage format.
• It multiplies the contents of the cell by 100 and displays the result with 2 decimals.
• Number Format: Standard icon will display the contents of the selected cells in default format.
• Number Format: Add Decimal and Number Format: Delete Decimal icons.
• They are used to increase or decrease the number of decimal digits that are to be displayed in the selected cells.

9.Explain AutoFormat sheet facility in Staroffice Calc.

The AutoFormat Sheet facility helps to format the worksheet with different predefined styles and colours.

To do this,

• Select the cells
• Click on the Format - > AutoFormat The AutoFormat dialog box appears,
• Displaying various predefined format styles.
• A preview for each style is also displayed in the box.
• Select any one style
• Click on OK.

10.How to change the column width and row height of a worksheet?

To change the row height,

Select the row .

Click on Format -> Row - > Height

Type New Row Height in the dialog box

• Click on OK.

Another method

• Place the mouse pointer on the line separating the row
• The mouse pointer becomes a double – headed arrow.
• Click and drag this pointer to up and down

To change the column width,

• Select the column whose width we want to change.
• Click on Format -> Column - > Width

• Type the new column width in the dialog box,
• Click on OK

Another method

• Place the mouse pointer on the line separating the column
• The mouse pointer becomes a double – headed arrow.
• Click and drag this pointer to the left to decrease the width
• and to the right to increase the width.

11.How to insert cells,Rows and columns in a worksheet?

To insert cells,Rows and columns in a worksheet,

Click on View - > Toolbar -> Insert Cell

A floating toolbar with four icons appears.

These icons are Insert Cells Down, Insert Cells Right, Insert Rows and Insert Columns icons

Insert Cells Down icon is used to insert an empty cell and move the existing cells down.

Insert Cells Right icon is used to insert an empty cell and move the existing cells to the right.

Insert Rows icon is used to insert the new row.

Insert Columns icon is used to insert the new column.

12.How to insert pictures and special characters in spreadsheet?

StarOffice Calc provides for inserting pictures and special characters

To insert Picture,

• Place the cell pointer in any cell,
• Choose File - >Insert -> Picture
• The Insert Picture dialog box appears.
• In the File name combo box, type or select the file that contains the picture or
• Select file from the gallery directory of StarOffice.
• Click on Open.

To insert special characters,

Select the cell

• Click the Insert ->Special characters
• The Special Character dialog box.
• Select the desired special characters
• Click on OK

13.What are the various facilities for drawing, available in staroffice calc? How can they be used? How to insert Special character?

StarOffice Calc provides the facilities for drawing lines, circles,ellipse, square, rectangle, etc. within a worksheet.

For this purpose,

Click the Show Draw Functions icon in the Standard toolbar.

The Draw Functions bar appears as shown below:

• Click on a tool.
• For example, to draw an ellipse in the worksheet,

Click the Ellipse tool.

Keep the pointer in the worksheet area and drag it till you get the desired shape and size.

The resultant screen is shown in the following figure.

14.How to delete cells,Rows and Columns in starcalc?

To Delete Contents of the cell or cell range

Select the cell or range.

Choose Edit_->Delete Contents

Delete Contents window appears

Click on the Delete all check box

Click OK.

To delete all

• Select the cell or range.
• Select Edit -> Delete Cells
• A window appears as shown below.
• Select any one option
• Shift cells up fills the space produced by the deleted cells with the cells underneath it.
• Shift cells left fills the resulting space by the cells to the right of the deleted cells.

To delete an entire row or column,

• Select Delete entire Row(s) or Delete entire Column(s).
• Clicking OK.

15. Explain the icons of inserting objects floating toolbar.

A floating toolbar appears with the following icons in the order listed below:

1. Insert Chart Icon

2. Insert Formula Icon

3. Insert Floating Frame Icon

4. Insert Movie and Sound Icon

5. Insert OLE Object Icon

6. Insert Applet Icon

Insert Chart Icon

It is used to insert a chart based on the data in the worksheet

Ex.Bar Chart, Pie Chart, Lines, XYplot, etc.

Insert Formula Icon

It is used for inserting a formula in the worksheet for performing calculations.

Insert Floating Frame Icon

It is used to generate a scrolling screen within a worksheet.

Insert Movie and Sound Icon

It is used to insert sound or video files into the current worksheet.

Insert OLE Object Icon

It is used to insert objects from other application into a worksheet.

Insert Applet Icon

It is used to import Applets written in Java programming language into the worksheet

16.How to insert (Draw) a chart in Staroffice Calc.(or) What spreadsheet feature allows you to represent data visually as a data – analysis tool? Explain.

One of the most popular features of StarOffice Calc software is the ability to generate charts based on numeric data.

To draw a chart,

• Select the data you want to chart.
• Click on Insert -> Chart (or)
• Click on the Insert Chart icon
• The Autoformat Chart window appears
• The cursor becomes a + sign with a small picture of the graph.

Place this cursor where you want to insert the chart and click.

1^st Window of Autoformat Chart appears

• Enter the range
• Click on Next

2^nd Window of Autoformat Chart appears,

• Select the Type of chart
• The preview will be shown by clicking on Show text elements in preview check box.
• Click on Next

3^rd Window of Autoformat Chart appears

• For each type there are different formats are available
• Select any one format
• Click on Next

4^th Window of Autoformat Chart appears

• Type the Title for the chart, Legends , X and Y axes
• Click on Create

Now,graph will appear on the worksheet

17.Explain working with multiple sheets and printing worksheets.

• In StarOffice Calc, a spreadsheet contains multiple sheets.
• Each sheet has its own name
• A list of sheets appears as tabs at the bottom of the window.
• To select a different sheet,
• Click the tab with the sheet’s name.
• The tab of the selected sheet appears in white.
• Each sheet of a spreadsheet can be used entirely independently of the other sheets.

You can also make them dependent on each other by referring to the data in another sheet or using the data from another sheet in calculations.

For example,

Enter data in Sheet 1 and Sheet 2

You can calculate the sum of the numbers in the cell A1 of Sheet 1 and A1 of Sheet 2

and store the result in A3 of Sheet 2.

To do this, type the 3-D formula as = SUM (Sheet 1.A1; Sheet 2.A1)

18.How will you print the worksheet?

To Print the Worksheets by using print icon

To print all the data in all the sheets

• Click on the Print icon on the function bar (Standard toolbar),

To print a part of the worksheet.

• Click on Format -> Print Ranges ->Edit
• Select the print range.
• Click on the Print icon,
• Now only the selected range will be printed.

To remove the print range setting

Click on Format ->Print Ranges ->Delete.

By using File menu

• Select File - > Print or press Ctrl + P
• The Print dialog box appears

In the Print dialog box,

Under Print range choose any one of the following option

• All to print all the sheets in the document.
• Pages to print specific pages
• Selection to print only the selected area in a worksheet.

To show the preview of the worksheet

Click on File -> Page View

To print the worksheet with grids

• Select Format - > Page
• Click on the sheet Tab in the dialog box
• Click the Grid check box ,click on OK

Short Answers

1. What is electronic Spreadsheet.

An electronic spreadsheet is a worksheet used in a computer

to create and quickly perform “What if” analysis of interrelated columnar data in workspaces

"The visible calculator" (VisiCalc) was the first electronic Spreadsheet.

2. What is a spreadsheet & Worksheet?

spreadsheet

• A Spread Sheet is a software package
• A spreadsheet is divided into columns and rows.
• The intersection of row and a column is called a cell.
• It is like a grid of cells with a programmable calculator attached to each cell Ex. MSExcel, starcalc

Worksheet

• Worksheet refers to the file that we create with spreadsheet software Ex. myfile.xls

3.Define Cell and What is Cell pointer?

Cell

• The intersection of row and a column is called a cell.
• A cell is referred to by its address – created by combining its column and row headings.
• Cells can contain values, labels, or formulae.

Cell pointer

• It is a rectangular box that appears on one cell in the worksheet
• It is used to identify the current cell

4..Name a few applications that are possible using electronic spreadsheets.

A few of the on applications are given below:

1. Payment of bills. 2. Income tax calculations 3. Invoices or bills 4. Account Statements 5. Inventory Control 6. Cost-Benefit-analysis 7. Financial Accounting 8. Tender Evaluation 9. Result analysis of students.

5. Name a few spreadsheet programs

The few popular spreadsheet programs are:

1. MS-Excel; 2. Lotus 1-2-3; 3. StarOffice Calc and 4. QuattroPro.

6. How can you change the position of the cell pointer?

In keyboard ,keys like Arrow keys ,Tab, Shift+Tab,Home, End, Pageup and Pagedown are used or

Clicking the Mouse on the concerned cell.

7. How will you open a new spreadsheet in StarOffice Calc?

To work with StarOffice Calc,

Open StarOffice and click on the File ->New -> Spreadsheet.

8.Name the bars which have shortcut icons for frequently done tasks.

1. Function bar and 2. Object bar

These bars have shortcut icons for frequently done tasks.

9.How does the data recognize by the cell?

• If the data entered is a number, the program recognizes that as a number and allows you to perform calculations on it.
• If the data entered is a word, the program recognizes it as a label and does not permit you to perform calculations on it.

10. How will you open an existing worksheet?

Select the File - > Open or

Click the Open icon on the Standard toolbar

A open dialog box appears.

Select the file that you want by clicking on it

Click on Open.

11.How to close & Quit staroffice ?

To close

Select File -> Close option.

To quit

Select File -> Exit option.

12.What are the different types of arithmetic operators in StarOffice Calc?

Refer detail Ans.

13. How does a formula in StarOfiice Calc begin?

The formula begins with an equal sign (=) followed by a combination of values, operators and cell references. Eg. =SUM (A2:A5)

14. What are the various (relational) comparative operators in StarOffice Calc?

Refer detail Ans.

15. List out the reference operators in StarOffice Calc. Mention their uses.

Refer detail Ans.

16. How will you copy a content of one cell in other cells?

To copy the contents of a cell

• Click on the cell.
• Click and drag the mouse to highlight all the cells where we want to copy the contents.
• Then select Edit -> Fill -> Down (or Left) .
• The contents of first cell will be copied in all the highlighted cells.

17. What is Range? Or What is a range address?

A continuous group of cells in a worksheet is called a Range

• A Range is referred to be the range address.
• A range address is the address of the first cell in the range,
• Followed by a colon (: ) Followed by the address of the last cell in the range.
• For example, the cells G1, G2, G3, G4 and G5 can be called G1 :G5.

18. Which shortcut icon on the formula bar can be used to select and insert functions?

The Function Wizard shortcut icon on the formula bar can also be used to Select and insert functions

19. How will you insert the SQRT function?

To insert the SQRT function

• Place the cursor in the cell where we want to insert the function
• Click on the Function wizard icon.
• Select Mathematical from Category.
• Select the SQRT function and double clicking on it.

20. How will you change the column width of a column in a worksheet?

To change the column width,

Select the column

• Click on Format -> Column - > Width
• Type the new column width in the dialog box,
• Click on OK.

21.How will you change the row height in a worksheet?

To change the row height,

• Click on Format -> Row - > Height or
• Right click on the selected row
• Select Row Height from the menu that appears.
• Type the new height
• Click on OK.

22. List some of the objects that can be inserted in a worksheets

1. Charts 2.Images from image editor 3. Formula and 4. Pictures.

23. List some of the drawings that are possible with the help of Drawing Functions toolbar

1. Circle, 2. Straight line, 3. Ellipse, 4. Square and 5. Rectangles.

24. How will you print only the selected range of a worksheet?

Select the range to be printed

• Click on Format -> Print Range -> Edit.
• Then click on the Print icon,

Now the selected range will be printed.

25. How will you remove the print range settings?

To remove print range setting,

Click on Format -> Print Ranges -> Delete.

26. What are the various steps involved in creating the worksheet?

The various steps involved in creating the worksheet are

Organising the Data,

• Entering the Data,
• Creating formulae,
• Editing the Worksheet, Formatting values, Labels and Cells,
• Addict charts if required,
• Analyzing the Data and
• Printing the Worksheet.

27. How will you save the Worksheet created?

To save the worksheet created,

• Select File-> Save or Save As option.
• Save as dialog box appears,
• Type a File name and click on Save.

28. How will you close the Worksheet?

To close the worksheet,

Select File -> Close option.

29. What are the different types of operators available in StarOffice Calc?

1. Arithmetic Operators. 2. Comparative Operators.3. Text Operators. 4. Reference Operators.

30. What is the order of procedure of Operators in StarOifiice Calc?

The rule of precedence followed in Mathematics. The order is as follows.

• Exponentiation. (6)
• Negation. (-)
• Multiplication and Division. (*, /)
• Addition and Subtraction. (+, -)

31. What is the use of Automatic Fill feature of StarOfflce Calc?

It is used to copy the contents of a cell or to create a series..

32. What is a formula?

• A formula is used to perform calculations on the data in the worksheet.
• The syntax of formula begins with an equal sign followed by a combination of values, operators and cell references.

Ex. = A1+B1

33. How are pictures inserted in the worksheet?

To insert the Pictures

Choose File -> Insert -> Picture

The Insert Picture dialog box appears.

In the File name combo box,Type the path of the file or Select the file from the gallery directory

Click on Open.

34. Write about two possible ways of cell reference (or)

Differentiate between absolute cell addressing and relative cell addressing. (or)Name the different types of cell referencing

References to the cell can be Relative or Absolute.

Relative cell addressing

• It is the default type of cell addressing
• Relative cell address changes when copied
• Ex. To add the contents in C4 and D4 cells = C4 + D4.

Absolute cell addressing

• A cell address can be made absolute by using the $ (dollar) sign in front of row and column names.
• Absolute cell addresses do not change when copied
• Ex. C4 becomes absolute when we enter it as $C$4.

35.What must you include in a formula, to ensure that the formula will operate on a specific value, no matter where the formula might be moved or copied?

• We must include cell reference
• We must follow relative cell addressing.
• Because Relative cell addressing automatically recalculated every time the contents of the cells used in the formulae change

36. Which icons appear in the floating tool bar?

The icons tilde Insert cells Down, Insert Cells Right, Insert Rows and Insert Columns icons appear in the floating tool bar.

37. What is the use of AutoFormat sheet? (or) Write a note on Auto Format in StarOffice Calc

• AutoFormat sheet helps to format the worksheet with Deferent predefined styles and colours.
• Click on Format- > Auto Format
• Auto Format dialog box appears , with various predefined format styles
• Select any one style
• Click on OK.

38. Is it possible to insert an empty cell in a column? If so, how will we do it?

• Yes, it is possible to insert an empty cell.
• Place the cursor in the cell where one wants to insert the new cell
• Click on the Insert Cells Down icon.

39. List out the icons that appear in a floating toolbar used to insert objects.

The icons that appear in a floating toolbar are:

1. Insert Chart icon.

2. Insert Formula icon.

3. Insert Floating Frame icon.

4. Insert Movie and Sound icon.

5. Insert OLE Object icon.

6. Insert Applet icon.

40. What do you mean by date arithmetic in StarOffice Calc?

Manual date calculations can be tricky because we have to keep track of the number of days in a month.

• In StarOffice calc we can add a number to a data and arrive at a new date
• It is used to find the difference between two dates
• It is also used in wide variety of function and formats.

To find the difference between 05/10/05 and 12/08/70.

Enter =first cell – second cell

41.Define Sum function

This function calculates the sum of a given set of numbers.

To use this function,

• In Function Wizard window
• select SUM function (or )
• Click on the Sum icon on the formula bar.
• Enter the range of cells
• Press Enter

42.Define Active cell.

• The active cell is identified by the cell pointer which is a rectangular box covering that cell.
• The data to be entered in the active cell

43.Can we change the data present in a cell? If so,how.

To edit the data present in a worksheet, first open the worksheet

Click File→ open.

Next, move the cell pointer to the cell, which you want to edit

Do the following

Type in the new data. The new data will simply overwrite the old contents of the cell.

Click on the formula bar with the mouse, press the F2 function key (or)

Simply double-click on the cell.

A vertical cursor appears on the formula bar. Move the cursor by using arrow keys or the backspace key and edit the data.

44.How to change the cell format?

Select the cell or cell range

• click on Format_ Cells
• Format Cells dialog box appears
• choose the type of cell attribute you want to use
• Click on OK

45.Write a Procedure to list all Sundays in a given

period, say March to May 2005.

Enter the date as 3/5/05 into a cell

• Select this cells

• Select the command Edit→Fill→ Series

• In the dialog box, select Day as the Date Unit

Enter the Increment as 7. Click OK.