402-Information Technology Class-IX-electronic spreadsheet.pdf

4 Electronic Spreadsheet Introduction In our daily life or at workplace we have to perform various calculations. Various methods are used depending on...

4 Electronic Spreadsheet Introduction In our daily life or at workplace we have to perform various calculations. Various methods are used depending on the nature of calculations. We use our fingers or mental calculations to perform simple calculations and use a calculator for longer calculations. In science and engineering, scientific calculators are used to a perform complex calculations. Spreadsheet application is tool which is used to perform all kinds of calculations easily and accurately. In your childhood, you might have used a special notebook with small cells marked with rows and columns to practice mathematics. We have also learned the multiplication table. Spreadsheet is a long sheet of rows and columns on the computer screen to do data analysis and calculation. In other words, a spreadsheet is a grid which interactively manages and organises data in rows and columns. It is also called as Electronic Spreadsheet. It is used for managing financial and accounting documents, creating data reports, generating invoices, data analysis from scientific and statistical researches, and for doing a variety of calculations on data. Unit 4.indd 98 9/5/2018 4:58:42 PM  A spreadsheet software can also store, manipulate and create graphical representations of data. User can enter data into a spreadsheet, do the formatting, calculate and analyse the data for decision making. Besides user's data, spreadsheet packages also provide built-in formulae and functions for common mathematical, financial, statistical, and logical operations in a very sophisticated manner. Spreadsheet packages are widely used for data analysis and accounting applications. Because of these abilities the spreadsheet packages are used as a universal programme for structured data preparation and processing. As spreadsheets became larger, they became difficult to manage. To handle the increasing size of the spreadsheet, the concept of a workbook was identified. LibreOffice Calc is used to perform the following activities accurately and efficiently. Tabulation of data Simple mathematical calculations Complex calculations using formula and functions Arranging data in ascending and descending order (sorting) Filtering the required data Check the validity of data Protection of data using passwords Saving for future use Spreadsheet Getting Started with Libreoffice Calc Softwares Microsoft Excel There are various types of spreadsheet applications LibreOffice Calc developed by various software corporation. LibreOffice OpenOfficeCalc Calc is the spreadsheet application of LibreOffice suite. Apple Inc. Numbers Starting LibreOffice Calc When installing the operating system Linux (Ubuntu), the LibreOffice gets installed by default along with the icons for each component of LibreOffice (Writer, Calc, Impress, etc.). These icons are then placed on the launcher. In Windows, you need to download LibreOffice from its official website and install it on your computer. Electronic Spreadsheet 99 Unit 4.indd 99 9/5/2018 4:58:42 PM  (a) To start the LibreOffice Calc in Windows In Windows, find the shortcut of LibreOffice on Start menu or on the desktop. Double click the shortcut to open LibreOffice. Or Click the window menu, select LibreOffice application, then click LibreOffice Calc (Figure 4.1). The Calc spreadsheet window will open. Fig. 4.1 : Starting LibreOffice Calc in Windows 100 Domestic Data Entry Operator – Class IX Unit 4.indd 100 9/5/2018 4:58:42 PM  (b) To start LibreOffice Calc in Linux In Ubuntu Linux, find the Calc icon on application launcher or search it by clicking on “Show Applications” as shown in Figure 4.2. Parts of LibreOffice Calc Figure 4.3 shows the parts of LibreOffice Calc. Fig. 4.2: Starting LibreOffice Calc in Ubuntu Linux A brief explanation about the parts is given below. Fig. 4.3: User interface showing the parts of LibreOffice Calc Electronic Spreadsheet 101 Unit 4.indd 101 9/5/2018 4:58:42 PM  Notes (a) Title bar: The Title bar, located at the top, shows the name of the current spreadsheet. When the spreadsheet is newly created, its name is Untitled X, where X is a number. The first created spreadsheet takes the name as Untitled 1, second is Untitled 2 and so on. (b) Menu bar: Menu bar is located just below the Title bar. It contains the menus with commands for various tasks. Each menu item has a submenu called pull-down menu. The various menu items are briefly explained below. (i) File: contains commands applied to entire document — Open, Save, Wizards, Export as PDF, Print, Digital Signatures and so on. (ii) Edit: contains editing commands — Undo, Cut, Copy, Paste, Select, Find & Replace and so on. (iii) View: contains commands for modifying the user interface — Toolbars, Column & Row Headers, Full Screen, Zoom and so on. (iv) Insert: contains commands for inserting elements into a spreadsheet — Image, Media, Chart, Object, Shapes, Date, Time, Headers and Footers. (v) Format: contains commands for modifying the layout of a spreadsheet — Cells, Rows, Columns, Page, Styles and Formatting, Alignment and so on. (vi) Styles: for managing styles. (vii) Sheet: contains commands to insert and delete cell, rows and columns, insert sheet, rename sheet, fill cell, etc. (viii) Data: contains commands for manipulating data — Define range, sort, and so on. (ix) Tools: contains various functions to check and customise spreadsheet — Spelling, Language, Gallery, Macros and so on. (x) Window: contains commands to display window — New Window, Split and so on. (xi) Help: contains links to the help system included in the software and other miscellaneous functions — Help, License Information, Check for Updates and so on. 102 Domestic Data Entry Operator – Class IX Unit 4.indd 102 9/5/2018 4:58:42 PM  (c) Toolbars: The Calc opens with the Standard Notes and Formatting toolbars at the top of the workspace by default. These toolbar provide a wide range of common commands and functions. Placing the mouse cursor over any icon displays a small box called a tooltip. It gives a brief explanation of the icon function. (i) Standard toolbar: The standard tool bar shows the icons for most common operations, such as editing, arranging, filtering, etc., used while working on the spreadsheet. (ii) Formatting toolbar: Formatting toolbar has the most common operation related to formatting datasheet. It includes buttons for font selection, size of text, alignment, cell value formatting and indentation, etc. (iii) Formula toolbar: It allows entering and editing the formula in the cell. Formula bar consists of the following: Name box: shows the cell reference, for example A1. Functions wizard: search the function from the list of available functions. Sum: used to total the numbers in the cells above the selected cell. The sum is placed in the selected cell. Function: clicking on the Function icon inserts an equals (=) sign into the selected cell and the Input line allow formula to be entered. Input line: displays the contents of the selected cell (data, formula, or function) and allows editing the cell contents. To edit inside the Input line area, click in the area, then type the changes. To edit within the current cell, just double-click in the cell. (d) Worksheet: The worksheet in Calc is also referred to as spreadsheet. The spreadsheet can have many sheets. Each sheet can have many individual cells arranged in rows and columns. The sheet tab shows its default name as Sheet1, Sheet2, Sheet3, …. Electronic Spreadsheet 103 Unit 4.indd 103 9/5/2018 4:58:42 PM  (e) Rows and columns: The sheet is divided into vertical columns and horizontal rows. Each sheet can have a maximum of 1,048,576 (220) rows and 1024 (210) columns. The rows are numbered as 1,2,3,4,… and columns are numbered as A, B, C, D, …., Z, AA, AB, AC, …., AZ, BA to BZ, CA,…., AMJ. Fig. 4.4: Rows, columns and active cell (f) Cell and cell address: The intersection of a row and column is called a cell. It is the basic element of a spreadsheet. It holds data, such as text, numbers, formulas and so on. A cell address is denoted by its column (letter) and row number. For example, D4, E9, Z89 are the valid example of cell address. (i) Active cell: In a spreadsheet, cell is the place where we enter the data. Before entering any data in the cell, it has to be first selected by placing a cursor on it. When we position the mouse cursor on a cell, it gets selected, and is ready to take data from the user. This selected or activated cell is called as active cell. It is always highlighted, with a thick border. The address of the active cell is displayed in the name box. The figure shows, B3 as the active cell in the worksheet. Let's Practice 1 Write the cell address of the following First row and first column ……….. First column and last row ………... First row and last column ……….. Last column first row …….. Seventh column and tenth row ……. Tenth column and nineteenth row …….. The cell address LK89 is situated in row number …. and column letter ….. (ii) Active cell in a worksheet: To enter data in the cell, it should be selected first. Active cell in a worksheet can be moved and selected by the key or a combination of keys. 104 Domestic Data Entry Operator – Class IX Unit 4.indd 104 9/5/2018 4:58:42 PM  Key or Result of Key or Combination Key Combination Arrow keys Move a single cell in arrow direction (←↑→↓) Ctrl + Arrow Moves the cell to the end of the data Keys range in a particular direction Moves to column A along the row Home where the active cell is Ctrl + Home Moves the cell to A1 position Moves to bottom right cell of the data Ctrl + End range Page Up Moves the worksheet one screen up Page Down Moves the worksheet one screen down (iii) Range of cells: A block of adjacent cells in a worksheet which is highlighted or selected is called a range of cells. Observe the worksheets below. The column range is the number of cells spread across the column. The cell address is represented by single column letter and multiple row number in a sequence. In this example the column letter C is constant with rows varying from 2 to 7. The range of cells starts in C2 and ends in C7. This range of cells consists of the cells, namely C2, C3, C4, C5, C6, and C7. The range of cells starts in C2 and ends in C7 Fig. 4.5: Column Range and it is represented by C2:C7 as shown in Figure 4.5. The row range is the number of cells spread across the row. The cell address is represented by single row number with columns varying from B to D columns. The range of cells Fig. 4.6: Row Range starts in B3 and ends in D3. This range of cells consists of the cells, namely B3, C3, D3, and it is represented by B3:D3 as shown in Figure 4.6. The row and column range is the number of cells spread across the row and columns. This Electronic Spreadsheet 105 Unit 4.indd 105 9/5/2018 4:58:42 PM  range is a matrix with number of rows and number of columns. In this example, the range starts in cell B2 and ends in cell C7. This range of cells consists of the cells from B2 to B7 and cells from C2 to C7. The range it is represented by B2:C7 as shown in Figure 4.7. Note: The control key is used to select more than one ‘range of cells’ Fig. 4.7: Row & Column Range in a worksheet. The control key is marked as ‘Ctrl’ on the keyboard. Let's Practice 2 Answer the questions based on the following worksheet. 1. What is the address of the first cell represented by Range1? 2. What is the address of the last cell represented by Range1? 3. Write the cell range represented by Range1. 4. Write the cell range represented by Range 2. 5. What is the name of the cell range along a row? 6. What is the name of the cell range along a column? 7. Write the cell range represented by Range 3. 8. Give the number of cells in the cell range represented by Range 3. Entering data To enter any data in a worksheet, practically in the cell, it is required to select the cell. Cell can be selected by positioning the pointer in a cell. The data to be entered can be the label, values or formula. (a) Label: Label is the any text entered by using a keyboard. It may combine with letters, numbers, and special symbols. By 106 Domestic Data Entry Operator – Class IX Unit 4.indd 106 9/5/2018 4:58:42 PM  default the labels are left aligned as shown in Figure 4.8. Fig. 4.8: Labels are left aligned and values are right aligned by default (b) Values: The numerical data consisting of only numbers are called values. By default values are right aligned. There are various forms of values, such as integer, decimal and so on as shown in Figure 4.9. (c) Formulae: Any expressions that begins with an equals ‘=’ is treated as formula. In the expression, the ‘=’ followed by values, cell address and functions are called as formula. When a formula is entered in a cell in a worksheet the value of the Fig. 4.9: Values are right aligned equation is displayed in the cell and the formula is shown in the formula bar. Note: The values do not display the preceding zero. If any value (e.g., telephone numbers), preceded by 0, then the first letter ‘0’ is not displayed, when the data is value. To show the preceding ‘0’, the data type has to be specified as ‘Text’. Fig.4.10 Expressions as formula Example: Getting the total value of cells A1 and B1 into cell C1 see Figure 4.10. Electronic Spreadsheet 107 Unit 4.indd 107 9/5/2018 4:58:43 PM  Notes Mathematical operators used in formulae Spreadsheet Software has the most powerful features to calculate numerical data using formulae. As we use a calculator for calculation, Calc can add, subtract, divide, multiply and much more. LibreOffice Calc uses standard operators for formulae, such as a plus(+), minus(-), multiplication (*), a division (/) for arithmetic operation. Note: The order of evaluation can be changed by using brackets. (The expressions within the brackets are evaluated first). Mathematical Operators Operator precedence + addition First () - subtraction Second ^ * multiplication Third /, * / division Fourth +,- ^ exponentiation (power) Simple calculations using values and operators Table 4.1 below shows a few formula written using values and operators. More than one mathematical operator can be included in the formula. Let us learn how to evaluate the operators in order. See the Tables 4.1 and 4.2. Table 4.1: Writing simple formula Operator Formula Result Addition (+) = 23+ 6 29 Subtraction 0–6 -6 Multiplication (*) 9*6 54 Division (/) 88/8 11 Exponentiation (^) 2^5 32 Table 4.2: Use of brackets in simple equations Formula Evaluation Result = 4+5*3 = 4+15 19 = (4+5)*3 = 9*3 27 = 5*4^2 = 5*16 80 = (5*4)^2 = 20^2 400 = (4/4)^2 = 2^2 4 = 16/(4^2) = 16/16 1 108 Domestic Data Entry Operator – Class IX Unit 4.indd 108 9/5/2018 4:58:43 PM  Let's Practice 3 Evaluate the following equations using operator precedence and then test the result in the spreadsheet 1. 8-4/2 2. 5*5+8 3. 3+5*4 4. 2^5+8 5. 3+2^2 6. 5+6*2^2 7. 8/4*4 8. -4/2+2 9. 1+2^2-2 10. 4*3/2 Formulae with cell addresses and operators The main advantage of entering formula with cell addresses and operators, works just like a variable. When the values of the cells concerned change, the results obtained by the formula also get updated accordingly. Suppose to add the values in two cells A1 and A2 and get the addition in cell A3. If we position the cursor in cell A3 and simply add the values as =5+8, then we will get the correct addition in cell A3. But if we change the values in cell A1 to 6 and A2 to 7, then again we have to get the addition in the cell A3 by writing =6+7. But if we write the general formula in cell A3 as =A1+A2. Then we need not to do the addition in cell A3. We will get the respective addition with the change in values in cell A1 and A2. Example: For calculating the volume of a box with given length, width and height in cells A2, B2 and C2, respectively, see the formula to derive the volume entered in cell D2 (see Figure 4.12). Figure 4.12: Volume of Box Let's Practice 4 Create a worksheet based on the Figure 4.12. Observe the changes in values obtained from the formula in column D when you enter different values in cells A2, B2 and C2. Note: Formula starts with ‘=’ sign and nothing should be written on the left side of the equal sign (‘=’). If you forgot to put the ‘=’ before the formula, it will be treated Electronic Spreadsheet 109 Unit 4.indd 109 9/5/2018 4:58:43 PM  as a label. If you write the formula B1=A2+A3 in cell B2, then it will be taken as a label and calculations will not be performed. When you enter formula with the equal sign (‘=’), a formula bar gets activated automatically. This helps in knowing whether the entered text is a formula or not (Figure 4.14). Fig. 4.13: Incorrect way of formula Fig. 4.14: Correct way of formula Example: Let us create a simple spreadsheet to prepare a shopping bill of stationary of the following items. Item Quantity Unit Price Register 3 40 Single Rule Copy 12 25 Notebook 6 30 Pencil Box 1 50 Color Box 1 120 Notebook Cover 20 3 The steps to create the spreadsheet are as mentioned below: Step 1: Open the LibreOffice Calc by using the standard process. Step 2: Observe that the Calc has created the worksheets automatically. Give the specific name to the worksheet say ‘Stationary Bill’. To do this Select the menu Sheet → Rename Sheet. Give appropriate name, say ‘Stationery Bill’ to the worksheet and click OK. Notice the change in the worksheet name. 110 Domestic Data Entry Operator – Class IX Unit 4.indd 110 9/5/2018 4:58:43 PM  Step 3: Enter the data given in the above table in the worksheet. To enter the above data in the worksheet ‘Stationery Bill’ do the following: Click (click means left click) on the cell A1. Enter the word “Item” and press the enter key. Click on the cell B1. Enter the word “Quantity” and press the enter key. Click on the cell C1. Enter the word “Unit Price” and press the enter key. You may drag the column to resize it. It may be required to Fig. 4.15: Data entry of text, number and formula resize a column while entering name of items. To resize, put the mouse at the edge of the column, and drag it to the required length. Select the heading cells and make them bold using formatting toolbar. Now enter the data so that the worksheet appears as shown in Figure 4.15. Step 4: Save the worksheet with appropriate name so that the data may not be lost. To save the worksheet, click on File → Save. A Save dialog box will appear as shown in Figure 4.16. Select the location where you want to save the file. Enter the name of the file say, ‘Stationery_ Bill’, and Click on the Save button. The spreadsheet file will get saved by default in ODF Fig. 4.16: Saving worksheet using Save dialog box format with the name Stationery_Bill.ods. Remember the name and location of file, to retrieve it in future. Now calculate the total cost of each item in this worksheet. Enter the heading as we will insert a new Electronic Spreadsheet 111 Unit 4.indd 111 9/5/2018 4:58:43 PM  column right to the ‘Unit Price’ and give the heading as ‘Total Cost’ in the cell D1. Here we do not need to insert the new column. Since we are editing the spreadsheet in the sequential manner, the next column is available for data entry. However, if you want to enter the data in the previous column then you have to insert the column before the column C. So that data in column C shifts to column D and an empty column C is created. To insert the column before any column, position the cursor on any cell of the column before which you want to insert the column and select Sheet → Insert Columns → Columns → Columns left (Figure 4.17) Similarly, to insert the column to the right select Sheet → Insert Columns → Columns → Columns right Fig. 4.17: Inserting column in the spreadsheet Now to calculate the total cost in column D for each item, move the cursor on the cell D2, and enter the formula ‘=B2*C2’ to calculate the total cost for ‘Register’. We need to apply the same formula for the next items. So instead of Fig. 4.18: Inserting formula in the spreadsheet writing the formula again and again in the next cell D3 to D7, click on the cell D2, hold and drag it down to D7. The formulas to calculate the total cost will be get copied. Observe that the address of the cell gets changed automatically as shown in Figure 4.18. Now enter the 112 Domestic Data Entry Operator – Class IX Unit 4.indd 112 9/5/2018 4:58:43 PM  formula in Cell D8 as ‘=D2+D3+D4+D5+D6+D7’ to calculate the Total Cost of all items. Let's Practice 5 Prepare the worksheet as shown in Figure 4.18. Change the values in cells B1 and B2. Observe the results obtained from the formulae in cells D2, D3, D4, D5, D7 and D8. Example: Calculating the area and volume of a cube when the length of one side is given (Figure 4.19). Let's Practice 6 Create a worksheet as shown in Figure 4.17. Change the values in cell B1 and observe the results obtained from the formulae in the cells B2, B3 and B4. Fig. 4.19: Finding area and volume of a cube Note: The results obtained from a formula (based on cell addresses) always get updated automatically when the values of these cells mentioned in the formula change. Formula Result =A1*B1-D2 =D1-C2/A2 =B3^C3-A3 =A1*C1/C3 =D2*C4*E1 =A3/B3+A2*D1 =A1+(B2*C2)-B3+C2/B2 =B2-B3+B1*(D1+D2^B1) =A2^B3-C2+A3*(B3+C1) =A3/C3*(D2*D1) =D3/C3/B3*A3+B2 =(D3/(A1+B2))^C2 Use of functions to do calculations In the example of Stationery Bill, to calculate the total cost of all the items to obtain the bill amount, we need to add the amount of each item. We may add each cell by using the formula. Position the cursor on the cell D8, and enter the formula ‘=D2+D3+D4+D5+D6+D6+D7’ and get the total in cell D8. But this may not be practically possible if Electronic Spreadsheet 113 Unit 4.indd 113 9/5/2018 4:58:43 PM  there are hundreds of items and also there are more chances of error. When there is a large quantity of data it becomes difficult and complex task to write formula using only the cell addresses. In such situations, LibreOffice Calc provides built in functions. SUM is the function to get the sum of range of cells. So we can use the function, ‘Sum(D2:D7)’ in D8 cell (Figure 4.20) to obtain the total cost of all the items. In the function we need to include only the cell range (starting and last cell address). Fig 4.20: Using functions in the spreadsheet Think on, which of the following method will be more appropriate to add cell data. = D2+D3+D4+D5+D6+D7 – Method 1 = SUM (D2:D7) – Method 2 The spreadsheet applications contain different functions to meet the requirements of different fields. The basic commonly used functions are given in the Table 4.4. Table 4.4: Commonly used basic functions in Calc Function Syntax Use SUM =SUM(Number1,Number2,.....) Adds the values contained in a range of cells. AVERAGE =AVERAGE(Number1,Number2,....) Finds out the average of the values contained in a range of cell MAX =MAX(Number1,Number2,......) Finds out the largest value contained in a range of cells. MIN =MIN(Number1,Number2,......) Finds out the smallest value contained in a range of cells. COUNT =COUNT(Number1,Number2,.....) Counts the number of cells within a range of cells. 114 Domestic Data Entry Operator – Class IX Unit 4.indd 114 9/5/2018 4:58:43 PM  Fig. 4.21: Sample data to work on functions Let us identify the various ways in which a function can be used. Based on the sample data in Figure 4.21, the results of the functions are evaluated from Table 4.5 to Table 4.9. Verify the result. Table 4.5: SUM function Formula Meaning Result =SUM (A1,B1,C1) The sum of cells A1,B1 and C1 17 =SUM(A1:C1) The sum of cells in the range of 17 cells from A1 to C1 =SUM(A1:C1,B2) The sum of cells in the range of 24 cells from A1 to C1 and B2 =SUM(B1:C2) The sum of cells in the range of 23 cells from B1 to C2 =SUM(A1:A3,C1:C3) The sum of cells in the range of 37 cells from A1 to A3 and C1 to C3 Table 4.6 : AVERAGE function Formula Meaning Result =AVERAGE (A1,B1,C1) The average of cells A1, B1 and C1 5.66 =AVERAGE (A1:C1) The average of cells in the range of cells from A1 to C1 5.66 =AVERAGE (A1:C1,B2) The average of cells in the range of cells from A1 to C1 6.33 and B2 =AVERAGE (B1:C2) The average of cells in the range of cells from B1 to C2 5.75 =AVERAGE (A1:A3,C1:C3) The average of cells in the range of cells from A1 to A3 6.16 and C1to C3 Electronic Spreadsheet 115 Unit 4.indd 115 9/5/2018 4:58:43 PM  Note: When the average is calculated a decimal number with several decimal places may be used. You need the required decimal places in the relevant cell range. Table 4.7: MAX function Formula Details Result =MAX(A1,B2,C1) Finds out the largest value among cells A1,B2 and C1 7 =MAX(A2:C2,B3) Finds out the largest value among the range of cells from A2 to 8 C2 and the cell B3 =MAX(A1:C1) Finds out the largest value among the range of cells from A1 7 to C1 =MAX(A1,B1:C2) Finds out the largest value among the range of cells from A1 to 7 B1 and the cell C2 Table 4.8: MIN function Formula Details Result =MIN(A1,B2,C1) Finds out the smallest value among cells A1, B2 and C1 5 =MIN(A2:C2,B3) Finds out the smallest value among the range of cells from A2 to 4 C2 and the cell B3 =MIN(A1:C1) Finds out the smallest value among the range of cells from A1 to 5 C1 =MIN(A1,B1:C2) Finds out the smallest value among the range of cells from B1 to 4 C2 and the cell A1 Table 4.9: Count Function Formula Details Result =COUNT(A1,B1) Counts the number of cells that contain numbers among cells 2 A1, B1 =COUNT(A1:C1) Counts the number of cells that contain numbers in the range 3 of cells from A1 to C1 =COUNT(A1:A4) Counts the number of cells that contain numbers in the range 3 of cells from A1 to A4 =COUNT(A1:C1,B2) Counts the number of cells that contain numbers in the range 4 of cells from A1 to C1 and B2 =COUNT(B1:C3) Counts the number of cells that contain numbers in the range 6 of cells from B1 to C3 =COUNT(A1:A3,C1:C3) Counts the number of cells that contain numbers in the range 6 of cells from A1 to A3 and the range of cells from C1 to C3 116 Domestic Data Entry Operator – Class IX Unit 4.indd 116 9/5/2018 4:58:43 PM  Let's Practice 7 Notes Using functions The Figure 4.22 shows marks scored by students in three different subjects. Fig. 4.22: Marks scored by students in different subjects Provide answers for the following queries using functions. 1. Write the formula in E2 to find the total marks scored by HARMAN. 2. Copy the formula entered in E2 for other students. 3. Write the formula in F2 to find the average marks scored by HARMAN? 4. Copy the formula entered in F2 for other students. 5. Write the formula in cell B7 to find the highest score in Hindi. 6. How will you find the highest score in English and Maths? 7. Write the formula in cell B8 to find the total number of students who appeared in Hindi? 8. Write the formula in cell B9 to find the lowest score in Hindi. 9. How will you find the lowest score in English and Maths? 10. How will you find the highest score in Hindi, English and Maths? Electronic Spreadsheet 117 Unit 4.indd 117 9/5/2018 4:58:43 PM  Formatting the worksheet The cell holds any type of data in the spreadsheet. The cell data can be formatted using formatting toolbar or cell formatting window. It is also possible to format the cell using Format cells dialog box as shown in the Figure 4.23. The Format cells dialog box can be opened using Format→cells using the Format menu, or from context menu opened through right clicking the cell. The various options in the Format→cells dialog is briefly explained in the Table 4.10. Table 4.10: Formatting tool S.no. Tool Details 1. Font Apply different font types on a worksheet 2. Font Size Apply different font sizes on a worksheet 3. Bold Make the selected text bold 4. Italic Italicize the selected text 5. Underline Underline the selected text 6. Left Alignment Align text in a cell to the left 7. Center Alignment Align text in a cell to the center 8. Right Alignment Align text in a cell to the right 9. Increase decimal places Show more precise value by showing more decimal places 10. Decreased decimal places Show less precise decimal places Use of dialog boxes to format values The format cells dialog box in the toolbar can be used to find more about formatting a cell or range of cell. You can open this Format Cell dialog box by pressing the shortcut key Ctrl+1 as shown in Figure (4.23). Formatting a range of cells with decimal places The division of different numbers Fig. 4.23: Format cells dialog box may result in varied decimal places in the quotient. In such cases it is necessary to format the number to a fixed number of decimal places. 118 Domestic Data Entry Operator – Class IX Unit 4.indd 118 9/5/2018 4:58:43 PM  Following are the steps to format a cell to the required number of decimal places: Select the range of cells. Open the ‘format cells dialog’ box Click the ‘Number’ tab Select the ‘Number’ Change the decimal places as required Click ‘OK’ Fig. 4.24: Formatted to two decimal places Formatting a range of cells to be seen as labels In a telephone number, the STD code starts with 0. But while entering the telephone number with the STD code the first digit zero (‘0’), disappears from the telephone number. This is because the telephone number is stored as a numeric value, and the numeric value does not have a preceding zero. If you make these numeric values as text, then the complete telephone number will appear with a preceding zero. So format the whole range of cells consisting of telephone number as ‘text’. Follow the steps below in order to format a range of cells as text as shown in Figure 4.25: Formatting a range of cells as labels Figure 4.25. Select the range of cells Open the ‘format cells dialog’ box Click the Number tab Select Text Click ‘OK’ Enter numbers Formatting of a cell range as scientific In a spreadsheet, by default the date format is in American Format; (mm/dd/yyyy) (mm-month, dd‑date, yyyy-year). The date 12/09/2018 means that it is the Electronic Spreadsheet 119 Unit 4.indd 119 9/5/2018 4:58:43 PM  09th day of December 2018. In a spreadsheet application, the user can change this Date in many different formats (Figure 4.26). To do these follow the below steps. Select the range of cells. Open the ‘Format cells dialog’ box Click the ‘Number’ tab Select the ‘Date’ category Figure 4.26: Various date formats Select the date format Click ‘OK’ Formatting a range of cells to display times Time is indicated in a computer as 10:35:53 AM. The common format of this is hh:mm:ss AM/PM (Figure 4.27). Here, hh means hours, mm means minutes and ss means second. Follow the steps below to format a range of cells to display the time. Select the cell range Open the ‘format cells dialog’ box Click the ‘Number’ tab Select the ‘Time’ category Select category Time should be Fig. 4.27: Various time formats cell range displayed Click ‘Ok’ Formatting alignment of a cell range The labels and values can be aligned to the left, center or right of a cell range by using the alignment icons (Left, Right, Center) on the standard toolbar. Select the range of cells Open the ‘format cells dialog’ box Click the ‘Alignment’ tab Select left, right or center Click ‘OK’ 120 Domestic Data Entry Operator – Class IX Unit 4.indd 120 9/5/2018 4:58:43 PM  Create the worksheet as shown in Figure 4.28 using formatting tools and formulae. Center align row 1. Make row 1 and row 2 Bold. Italicise cells A3, A4, A5. Use function AVERAGE in cell E3 to calculate the average of Hindi (B3) English (C3) and Maths (D3). Copy this formula by dragging it from E3 to E6. Use function AVERAGE and write a formula in B7 Fig. 4.28: Worksheet with to calculate the average of Class 9 (B3), Class 10 various formats (B4), Class 11 (B5), Class 12 (B6) for the subject Hindi. Copy this formula by dragging it from B7 to D7 and use them to calculate the average for English and Maths. Speeding up data entry The most important ability of a spreadsheet is to drag and drop the contents of one cell to another by using a mouse. Calc includes several other tools for automating input, especially of a repetitive task. They include the fill tool, selection lists, and the ability to input information into multiple sheets of the same document. Using the fill handle The Calc Fill Handle tool is used to fill the next cells till you drag it with the next predefined value. For example, if you want to fill the numbers in sequence of 1,2,3...., or days of the week as Monday, Tuesday,...., or month name as Jan, Feb,...., enter the first two values, select them and drag to the next cells till you wish to continue the series in sequence. Figure 4.29: Fill handle for number (a) For number series: Type the numbers series 1, 2 in two consecutive cells and select them using a mouse. Click on the right down corner of the selected cells, hold down the first button of mouse and drag downward till you want to continue as shown in Figure (4.29). Electronic Spreadsheet 121 Unit 4.indd 121 9/5/2018 4:58:43 PM  Here you can get a series on different interval also by giving the difference between two consecutive cell values and drag them. For example, to draw odd number series type 1 in the first cell and 3 in the next cell and then drag them to fill odd number series. (b) Copying a formula: If you wish to apply the same formula to the number of cells in the rows or columns, you need not enter the formula again and again in each cell. A formula can be copied. It saves the time and efforts in the case of long and complex formulae and reduces chances of errors. (i) Use of copy and paste commands for copying formulae Following are the steps to copy a formula using the copy-paste command Select the cell Copy the contents of the selected cell (Ctrl+C) Select the cell to which the formula is to be pasted Paste the contents in the selected cell (Ctrl+V) (c) Fill handle of a cell: The small black square in the bottom-right corner of Fig. 4.30: Fill handle the selected cell or range is called a fill handle (see Figure 4.30). (d) Uses of fill handle for copying formulae: Follow the steps below to copy formula using the fill handle. Select the cell which contains the formula Click the small black square in the bottom-right corner of the selected cell Drag the fill handle up to the required cell Let's Practice 7 A. Copy the formula using Fill Handle 1. Create the worksheet as shown in Figure 4.30. 122 Domestic Data Entry Operator – Class IX Unit 4.indd 122 9/5/2018 4:58:43 PM  Click on Cell A2 and using Fill Handle, Drag it till Cell A15. You will get the list of all the Days’ name. Click on Cell B2 and using Fill Handle, Drag it till Cell B15. You will get the list of all the Months’ name. Select Cell C2 and C3 using a mouse. Now using Fill Handle in cell C3 drag it till Cell C15, you will get a list of Natural numbers. Select Cell D2 and D3 using a mouse. Now using Fill Handle in cell D3 drag it till Cell D15, you will get a list of Even numbers. Select Cell E2 and E3 using mouse. Now using Fill Handle in cell D3 drag it till Cell E15, you will get a list of Odd numbers. After these five activities, you will get the sheet as shown in Figure 4.31. Fig. 4.30 Activity sheet for fill handle Fig. 4.31 Result of fill Handle activity 2. Create a worksheet as shown in Figure 4.32 with all student names and marks. Type the formula in cell H2 as =SUM(C2:G2). You will get the total marks of Amit. Now drag the formula using Fill Handle in cell H2 to H11 to get the total of all marks. Electronic Spreadsheet 123 Unit 4.indd 123 9/5/2018 4:58:44 PM  Type the formula in cell I2 as =AVERAGE(C2:G2). You will get the average marks of Amit. Now drag the formula using Fill Handle in cell I2 to I11 to get the total of all marks. After that you will your sheet as shown in Figure 4.33. Fig. 4.32: Activity sheet for formula dragging Fig. 4.33: Result of Activity sheet for formula dragging Referencing Referencing is the way to refer the formula or function from one cell to the next cell along the row or column. There are three types of referencing. Relative referencing Mixed referencing Absolute referencing Till now we have used Relative referencing in the earlier examples. (a) Relative Referencing When you drag any formula in any row or column in any direction, the formula gets copied in the new cell with the relative reference. Almost all spreadsheet applications use relative referencing by default. Fig. 4.34: Activity for using Relative reference Fig. 4.35: Use of Relative referencing 124 Domestic Data Entry Operator – Class IX Unit 4.indd 124 9/5/2018 4:58:44 PM  When we drag the formula downwards in columns, the row number of the cell address gets one added. In the same way, when you drag the formula horizontally from the left to the right column, the column name of the cell address is added on to the next column letter. Refer Figure 4.34, in cell E2, the formula = C2*D2 has been used to calculate the total price of the pencil. In cell D7, the formula =SUM(D2:D6) has been used to calculate total quantity of all items. Now drag formula from cell E2 to E6 vertically and from Cell D7 to D8 horizontally. In Figure 4.34, you can observe that the formula in cell E3 to E6 is C3*D3, C4*D4, C5*D5, and C6*D6. The row numbers have got added by one while column names are constant. As you drag formula from cell D7 to E7, the formula gets changed to SUM (E2:E6) from SUM (D2:D6). The column names get changed, while row numbers will remain same. Note: To see the formula after dragging as shown in Figure 4.34, Select from Menu View>Show Formula. Let's Practice 8 A. Show the formula in cell used in Figure 4.35 for column Total and Average. 1. Prepare a worksheet as shown in Figure 4.36 to calculate the Gross Salary of employees. Fig. 4.36: Displaying formula in Relative reference 2. Type the formula in Cell D2, E2, F2, G2 and H2 as shown in the figure. Now drag each formula Electronic Spreadsheet 125 Unit 4.indd 125 9/5/2018 4:58:44 PM  you have typed till row 11 to calculate the gross salary of each person. Finally calculate the total salary of all persons in Cell H12 yourself. (b) Mixed referencing: As we have seen, when we drag the formula, row number or column name get change in relative reference. Again consider the same sheet prepared in Figure 4.36 with the following changes. 1. Make a new column GR Total in Cell J1 as shown in Figure 4.37. 2. Type Grace Marks 20 in Cell C14. 3. Now calculate the Grand Total of marks in cell J2 by formula =H2+C14. 4. Now drag the formula vertically from Cell J2 to J11 and check if the grace marks are added for all the students or not. 5. The result shows that only in cell J2, 20 marks are added, with no other change. Now to see the formula, from Menu View>Show Formula for what exactly Calc did in this case as shown in Figure 4.37. Fig. 4.37: Display formula in mixed referencing In this case as cell address H2 changes from H3 to H11. In the same way the cell address C14 also changes 126 Domestic Data Entry Operator – Class IX Unit 4.indd 126 9/5/2018 4:58:44 PM  from C15 to C23. Since there is no value in cell C15 to C23, the result in the cell J3 to J11 are not affected. So to calculate grand total of all students, modify the formula in Cell J2, keeping the address cell C14 constant. In Mixed Referencing, the $ sign is used before row number or column name to make it constant. Now modify formula in cell J2 as H2+C$14 and drag it downward from cell J2 to J11. Observe the column J from cell J2 to J11 in Figure 4.38, the cell address C14 is constant. The output of this activity is Fig. 4.38: Uses of mixed referencing shown in Figure 4.39. It is observed that in column J from cell J2 to J11, in the formula, value of cell C14 is constant. The grace marks denoted in C14 cell are being added in each (cell J2 to J11). The cell address H2 is relatively changing from H2 to H11, but cell C14 remains constant. This is mixed referencing in which one cell address name is variable and one cell address is constant. Figure 4.39: Result of mixed referencing after modifying formula In this activity, the row number has been kept constant. The column name can also be kept constant by using $ sign before column name (as $C14) in any formula, if dragging the formula horizontally. (c) Absolute referencing: In Absolute referencing, a $ symbol is used before the column name as well as row number to make it constant in any Electronic Spreadsheet 127 Unit 4.indd 127 9/5/2018 4:58:44 PM  formula. For example, $C$12, $D$5, etc. In this case, even if you drag your formula in any direction, the cell name remains constant. This type of referencing is used in higher classes. Thumb rule for referencing Types of Cell Reference Example Type of reference A1 Relative reference $A1 Mixed reference (Column letter is absolute) A$1 Mixed reference (Row number is absolute) $A$1 Absolute reference (No change) Creation of Charts Using Spreadsheets It is not easy to comprehend, compare, analyse or present data when they are represented as numbers. But when data are presented in the form of charts they become an effective tool to communicate. Let us learn how spreadsheet applications are used for this purpose. The various types of charts are given below. Table 4.10: Types of charts Types Purpose Column Chart Comparing classes of data items in group. Group comparison Bar Chart Comparing classes of data items in group. Group comparison Line Chart Comparing classes of data items in group. Group comparison Pie Chart Comparing classes of data items as percentage. XY Scatter Comparing data in pairs Chart Let us use the worksheet below to create a column chart. Follow the steps given below to create charts. Select the range of data (A1:F7) Insert → Chart Select the type of chart 128 Domestic Data Entry Operator – Class IX Unit 4.indd 128 9/5/2018 4:58:44 PM  Select the chart (Column Chart) Click finish. A chart as shown in Figure 4.40 will be displayed. Example: Quadratic function chart of the equation Y=X2-5X-3 is given in the Figure 4.41. 1. Enter the values 2 to 7 in axis as shown in worksheets. 2. Enter the formula below in cell B2. Fig. 4.40: Worksheet with chart = B1^2-5*B1-3 3. Copy the formula entered in B2 upto K2 4. Mark A1:A2 a data range Figure 4.41: Column chart 5. Insert → Chart → XY Scatter Fig. 4.42: Quadratic function values of x, y Let's Practice 9 A. Provide answers of the following, based on the worksheet in Figure 4.43. Name the cell range consisting of number 12, 10, 19. Name the cell range consisting of number 27, 20, 15, 22. Write the formula in B8 using only cell addresses to calculate the total of the values from B2 to B7. Fig. 4.43: Answers based on the worksheet Electronic Spreadsheet 129 Unit 4.indd 129 9/5/2018 4:58:44 PM  Write the formula in G3 using function to calculate the total of the values from B3 to F3. Write how you would copy the formula in F3 to F4. Write the formula in B8 using function to calculate the average of the values from B3 to B7. B. Create the worksheet below in Calc Use a single function and create the formula. Fig. 4.44: Marks List 1. Enter the formula in G2 to calculate the total marks scored by Harman 2. Enter the formula in H2 to calculate the average scored by Harman. 3. Enter the formula in B9 to find out the highest score obtained for science. 4. Enter the formula in B10 to find out the lowest score obtained by students in each subject. 5. Enter the formula in B11 to find out the number of students present for each subject. 6. Enter the formula in B12 to find out the average score of each subject. Check Your Progress A. Multiple Choice Questions 1. Which of the following technique can be used to allow only date value in cell? (a) Data formatting (b) Data sorting (c) Data filtering (d) Data validation 130 Domestic Data Entry Operator – Class IX Unit 4.indd 130 9/5/2018 4:58:44 PM