Electronic spreadsheet- IT.pdf

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 perfor...

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.  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 30-May-19 11:32:23 AM  (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 30-May-19 11:32:23 AM  (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 30-May-19 11:32:23 AM  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 30-May-19 11:32:24 AM  (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 30-May-19 11:32:24 AM  (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 30-May-19 11:32:24 AM  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 30-May-19 11:32:24 AM  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 30-May-19 11:32:24 AM  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 30-May-19 11:32:24 AM  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 30-May-19 11:32:24 AM  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 30-May-19 11:32:24 AM  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 30-May-19 11:32:24 AM  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 30-May-19 11:32:24 AM  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 30-May-19 11:32:25 AM  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 30-May-19 11:32:25 AM  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 30-May-19 11:32:25 AM