Spreadsheet Applications PDF

Spreadsheets MODULE – 2 Office Automation 9 SPREADSHEETS...

Spreadsheets MODULE – 2 Office Automation 9 SPREADSHEETS Notes In the previous lesson you have learnt about digital documentation. In this lesson you will be learning OpenOffice Calc (version 4.1.1) which is the spreadsheet application that comes with OpenOffice Suite. Spreadsheet is one of the very useful application software for handling numerical data and short text strings. The data is arranged in a grid of rows and columns which can be manipulated, analysed and displayed in various formats including tables and charts. This helps us to make more informed decisions. We are able to track changes in data, write formulas to perform calculations on that data and even build models for analysing data. Various spreadsheet software available in the market are Lotus 1-2-3, MS Excel, and OpenOffice Calc. OBJECTIVES After reading this lesson, you will be able to: z list applications of spreadsheets; z explain the components of Calc application window; z create, save, open and close a workbook; z enter, select, edit a worksheet; z use AutoFill feature and sort lists; z format cell content; z merge and wrap text in a cell; z use formulas, functions and cell referencing; z perform sorting and filtering of data; z create charts; z print the worksheets. Computer Science 155  MODULE – 2 Spreadsheets Office Automation 9.1 APPLICATIONS OF SPREADSHEETS You know that spreadsheet is a very useful application, especially in offices where numerical data has to be stored, manipulated and analysed at regular intervals. Each page of a spreadsheet is known as a worksheet and a collection of worksheets constitutes a workbook. Some of the common applications of spreadsheets are as follows: Notes (i) These act like a calculator for doing automatic calculations. They are used to calculate, analyse, store and present the information. (ii) In a spreadsheet if one value is changed, all the corresponding calculations will be re-calculated. So these are very useful in What-if analysis. (iii) Spreadsheets are very useful to manage financial data like stock exchange movements, account transactions etc. (iv) The data in a spreadsheet can be pictorially represented through charts. (v) Various in-built functions help in easy processing and manipulation of data. 9.2 STARTING OPENOFFICE CALC To start OpenOffice Calc, follow the steps given below: 1. Select Start → All programs → OpenOffice 4.1.1 2. Select OpenOffice Calc option. The Calc application window is displayed as shown in Fig. 9.1. Fig. 9.1: OpenOffice Calc Application Window 156 Computer Science Spreadsheets MODULE – 2 Office Automation 9.3 COMPONENTS OF CALC APPLICATION WINDOW Fig. 9.1 shows the components of Calc Application Window. Title Bar: It is located on the top of the application window. It displays the name of the workbook and the name of the application. The window control buttons (Minimize, Maximize/Restore and Close) are present on the extreme right of the title bar. Notes Menu Bar: Below the title bar is the menu bar which contains various menu items - File, Edit, View, Insert, Format, Tools, Data, Window and Help. The menu bar gives access to basic commands used in Calc. Standard Toolbar: Below the menu bar is the Standard toolbar which contains buttons for frequently used commands such as New, Open, Save, Print, Cut, Copy, Paste etc. This toolbar is also known as Function toolbar. Formatting Toolbar: This toolbar consists of commands that help in formatting text and number data like font style, size, colour, bold, italic, underline, various number formats etc. Formula Toolbar: This toolbar contains the Name box and the Formula bar. z Name Box: It displays the name of selected cell or range of cells. For example, in Fig. 9.1, the name of active cell A2 is displayed in the name box. z Formula Bar: It contains the content of the selected cell or the formula applied on selected cell. It is also used for editing the cell contents or the formulas in the active cell. Rows and Columns: The entire worksheet is divided into horizontal rows and vertical columns. The rows are numbered on the left side of the worksheet as 1, 2,3…and so on. The columns are named on the top of the application window as A,B,C…and so on. Cells: The rows and columns in a spreadsheet intersect to form the smallest unit of a worksheet i.e., a cell. Each cell has a unique address which is the combination of row number and column name. For example, the cell D10 will be formed by intersection of row number 10 and column name D. Active Cell: It is the cell that is currently active or selected. The active cell has a thick black outline. Data is always entered in the active cell. Sheet Tabs: The sheet tabs are present at the bottom of the worksheet. These are named as Sheet1, Sheet 2, Sheet 3 and so on. The active sheet tab indicates the current worksheet. By default, there are three worksheets in a workbook but we can add more worksheets as per our requirement. Computer Science 157  MODULE – 2 Spreadsheets Office Automation Status Bar: This is located at the When we place the mouse pointer over bottom of the Calc Application window any of the buttons or icons on the and it displays information about the toolbars, the name of the icon appears current worksheet. as a tooltip. Scroll Bars: There are two scroll bars on the Writer Window: z Horizontal Scroll Bar: It helps to navigate left and right of the worksheet. Notes z Vertical Scroll Bar: It helps to navigate the up and down of the worksheet. We can click the arrows on the either side of the scroll bars to navigate through the worksheet. Docking Toolbars: On the extreme right of the Calc window, there are five icons – Properties, Styles and Formatting, Gallery, Navigator and Functions. By clicking on any of these icons, the corresponding docking toolbar opens containing various buttons to format text, apply styles, insert pictures, navigate through the document and uses various built-in functions provided by Calc respectively. 9.4 CREATING A NEW WORKBOOK When you start Calc, the application opens with a new workbook. However, if you want to open a new workbook when the Calc application is already running, select File → New → Spreadsheet (Fig. 9.2). The new workbook will be created Press Ctrl + N to open a new workbook in Calc. and its name will appear on the title bar of the Calc window. Fig. 9.2: Opening a new workbook 158 Computer Science Spreadsheets MODULE – 2 Office Automation 9.5 SAVING A WORKBOOK To save a workbook, follow the steps given below: 1. Click File → Save As. The Save As dialog box appears (Fig. 9.3). Notes Fig. 9.3: Save as dialog box 2. Browse the folder where the file has to be saved. 3. Type the name with which the file has to be saved in the File name text box. 4. Click Save button. By default, the file created in OpenOffice Calc is saved with an extension.ods. After saving, the name of the file appears on the title bar of the Calc window. We can also save the document by clicking on Save button on the Standard toolbar (Fig. 9.4). Please note that the Save As dialog box appears only when we are saving the file for the first time. However, if the same file has to be saved Press Ctrl + Shift + S to save with a different name or at a different document for the first time and location, then select Save As option again Ctrl + S to save otherwise. from the File menu. Computer Science 159  MODULE – 2 Spreadsheets Office Automation Notes Fig. 9.4: Save button 9.6 OPENING AN EXISTING WORKBOOK The existing file can be opened by browsing to the folder which contains the file and then double clicking on it. However, if the application is running, follow the steps given below to open an existing workbook: 1. Click File → Open. The Open dialog box Press Ctrl+O to open an appears (Fig. 9.5). existing workbook. 2. Browse the folder where the file to be opened exists. 3. Select the file and click Open button. Fig. 9.5: Open dialog box 160 Computer Science Spreadsheets MODULE – 2 Office Automation 9.7 CLOSING A WORKBOOK We can close a workbook without closing the Calc Window by selecting File → Close. However, to exit Calc, click Close button ( ) on the top right corner of the Title bar or click File → Exit option. In Press Ctrl + Q to either of the ways, if our document to be closed is not exit Writer saved, then Writer prompts us to save the document before closing. Notes INTEXT QUESTIONS 9.1 1. Give one word for the following: (i) The toolbar containing Save button. ____________ (ii) Smallest unit of a spreadsheet. ____________ (iii) The cell in which data will be entered. ____________ (iv) Shortcut key to exit Writer. ____________ (v) Number of scrollbars in Calc window. ____________ 2. Give any three applications of spreadsheet. 3. How can you open a new workbook if Calc application is already running? 9.8 ENTERING DATA As mentioned before, the data is entered in the cells of a worksheet. The data that we enter may be in the form of text, numbers or formulas. Entries containing an alphabet or a character even if they start with a number are called text entries. Any text entry containing alphabets or non-numeric special characters such as &,*,^,% etc. is called a label. For example, Grade, s@g, Maximum marks etc. are labels. Even numeric data in combination of alphabets and special characters like Flat # 100, Arjun Vihar form a label. These entries cannot be used in any type of calculations. The data on which arithmetic calculations can be performed is known as numerical data or value. In addition to the numbers, characters like +, -,. , %, $(or any currency), ( ), E and can also be entered along with numerical data. The table 9.1 shows the purpose of each of the above characters: Computer Science 161  MODULE – 2 Spreadsheets Office Automation Table 9.1: Purpose of special characters in numerical data Character Purpose + Positive number - Negative number. (period) Decimal number ( ) Enclose data in brackets Notes % Percentage $ (or any currency) Denotes the number as a currency E, e Denotes Exponentiation for scientific notation of numbers To enter labels or values, just click the cell in which the data has to be entered. The cell becomes the active cell in which the data can be typed. In Fig. 9.6, the cell A2 contains the label Marks and the cell A3 contains the value 67.89. Fig. 9.6: Text and numerical data 9.8.1 Entering Formulas Formulas are an essential part of any spreadsheet application and they generally define a relationship between two or more cells. It is a combination of values, operators, cell addresses etc. While entering a formula, we can either refer to individual cell or range of cells. They help in performing calculations and also in What–if analysis. The formula always starts with an equal (=) sign. For example if, we want to add two numbers, say 20 and 30, we will type =20+30 in the active cell. If the data to be added is in the cells, say A2 and B2, we may also type = A2 + B2 say, in cell C2 (Fig. 9.7). In the latter method, if any of the values in either cell A2 or B2 is changed, the result in cell C2 will also change. When we type the 162 Computer Science Spreadsheets MODULE – 2 Office Automation formula or click on the cell containing formula, we will see that the formula also appears in the formula bar. Notes Fig. 9.7: Entering Formula After entering the formula, if we try to edit it by clicking in the formula bar, the Accept Editing and Cancel Editing buttons on the left side of the formula bar become active (Fig. 9.8). Also notice that the colour of the cell reference and the colour Formulas in OpenOffice Calc are not case-sensitive. of the border of the cell is same. Fig. 9.8: Editing a Formula 9.9 SELECTING A CELL/RANGE OF CELLS In OpenOffice Calc, we may require to select a single cell or a range of cells. These cells or range of cells can be selected using either a keyboard or a mouse. The selected cells are shaded in blue colour. z To select a single cell, click on that cell. This currently selected cell is known as the active cell (Fig. 9.9 (a)). z To select the entire row or column, click on the row number or column name. (Fig. 9.9 (c) & (d)). z To select adjacent range of cells, place the mouse pointer in the first cell in the range. Thereafter click and drag the mouse pointer in the direction of selection till the last cell in the range is selected. For example, if cell range A3 to B8 has to be selected, place the mouse pointer on cell A3 (Fig. 9.9 (b)). Click and drag the mouse till cell B8. Using a keyboard, we Computer Science 163  MODULE – 2 Spreadsheets Office Automation can select the first cell. Thereafter Press Shift + Spacebar to select all holding down the Shift key and using columns in current row. the arrow keys in the direction of Press Ctrl + Shift + Spacebar to selection, move to the diagonally select current column completely. opposite corner of cell B8. Once the Press Ctrl + A to select the entire last cell in the range is selected, release worksheet. the Shift key. Notes (a) Active Cell (b) Range of selected cells (c) Selecting the entire row (d) Selecting entire column Fig. 9.9: Selection of Cells 9.10 EDITING A WORKSHEET Editing includes changing data by any means. It may be altering, inserting, deleting, copying, moving, changing appearance of the data and so on. The data once entered in the worksheet can be modified by any of the following methods: 164 Computer Science Spreadsheets MODULE – 2 Office Automation (i) Overwriting This is the default mode of editing cell contents in which the current content of the cell is replaced by the new one. For this, select the cell, type the new content and press the Enter key or click Accept button ( ) near the formula bar. (ii) Partial Modification This method is used if there are minor modifications to be done in the cell Notes contents. Click the Formula bar or double click on the cell where modification is to be done. The cursor will appear. Enter the contents either in the formula bar, if you clicked the formula bar or in the cell, if you double clicked on the cell. Thereafter, press the Enter key or click the Accept ( ) button on the Formula bar. 9.10.1 Deleting Cell Content To delete the cell contents in OpenOffice Calc, follow the steps given below: 1. Select the cell or range of cells whose contents are to be deleted. 2. Right click on the selected cells and click Delete Contents from the shortcut menu The shortcut key to edit the cell (Fig. 9.10). The Delete Contents dialog contents is F2. box will be displayed (Fig. 9.11). Fig. 9.10: Delete Contents option Fig. 9.11: Delete Contents dialog box Computer Science 165  MODULE – 2 Spreadsheets Office Automation 3. The various options available in Delete Contents dialog box are: z Delete All : Deletes all the contents from selected range of cells. z Text: Deletes only text from the selected range of cells, keeping all the other values. z Numbers: Deletes only numbers from the selected range of cells. z Date & time: Deletes only date and time values. Notes z Formulas: Deletes only formulas, keeping all the other values as it is. z Comments: Deletes any notes or comments in the selected range. z Formats: Deletes formatting styles applied to cells. z Objects: Deletes only objects. After clicking the appropriate checkboxes, click OK to delete the selected cells. 9.10.2 Copying Cell Content The data when copied, is duplicated and pasted at a new location. To copy data, follow the steps given below: 1. Select the cell or range of cells whose data needs to be copied. 2. Click Copy button on the standard toolbar (Fig. 9.12). Alternatively, we can right click and select Copy option from the short cut menu. Fig. 9.12: Cut, Copy & Paste buttons 3. Click on the cell where the data has to be pasted or on the starting point where the data is to be pasted. 4. Click Paste button on the standard toolbar. Alternatively we may right click and select Paste option from the shortcut menu. Fig. 9.13 shows that the data is copied from A1: E4 and pasted at A10 : E13. 9.10.3 Moving Cell Content When we move cell contents, they are removed (or cut) from the original location and pasted at a new location. Hence, unlike copying, the cell contents are not duplicated. Follow the steps given below to move data from one location to another: 166 Computer Science Spreadsheets MODULE – 2 Office Automation Notes Fig. 9.13: Copying Cell contents 1. Select the cells whose data needs to be moved. 2. Click Cut button on the standard toolbar. Alternatively you can right click and select Cut from the shortcut menu. 3. Click on the cell that will be the starting point of the data when it is pasted i.e., on the upper left cell of the range where you want to copy the data. 4. Click Paste button on the standard toolbar. Alternatively we may right click and select Paste option from the shortcut menu. Fig. 9.14 below shows that the data is moved from A1: E4 and pasted at A10 : E13. Fig. 9.14: Moving Data Computer Science 167  MODULE – 2 Spreadsheets Office Automation 9.10.4 Inserting Cells/Rows/Columns We may have to insert cells, rows or columns in an already existing worksheet. To do so, follow the steps given below: Select the cells and press Ctrl + C 1. Select the cell or range of cells where to copy, Ctrl + X to Cut and Ctrl you want to insert new cell, row or + V to paste. column. Notes 2. Right click and choose Insert…option from the shortcut menu (Fig. 9.15). 3. The Insert Cells dialog box is displayed (Fig. 9.16). Fig. 9.15: Insert option Fig. 9.16: Insert Cells dialog box It has four options : z Shift Cells down: The existing cells are shifted towards the bottom of the worksheet and a new cell is inserted. z Shift Cells Right: The existing cells are shifted to the right of the worksheet and a blank cell is inserted. z Entire Row: A new row is inserted above the selected range of rows. z Entire Column: A new column is inserted to the left of the selected range of columns. 4. Select the desired option and click OK. 9.10.5 Deleting Cells/Rows/Columns To delete cells, rows or columns from the worksheet, follow the steps given below: 1. Select the cell or range of cells where you want to delete a cell, row or column. 168 Computer Science Spreadsheets MODULE – 2 Office Automation 2. Right click and choose Delete… option from the shortcut menu (Fig. 9.15). 3. The Delete Cells dialog box is displayed (Fig. 9.17) with the following four options: Notes Fig. 9.17: Delete Cells dialog box z Shift Cells Up: The selected cells are deleted and the cells below the deleted cells shift up. z Shift Cells Left: The selected cells are deleted and the cells on the right of the deleted cells shift towards the left. z Delete Entire Row(s):Deletes entire row(s). z Delete Entire Column(s):Deletes entire column(s). 4. Select the appropriate option and click OK. 9.11 AUTOFILL FEATURE AutoFill feature of OpenOffice Calc is used to fill sequential data in the cells to form a series automatically. The series to be formed may be numerical or alphabetical. OpenOffice Calc automatically fills the range of cells with the appropriate data. Let us use this feature to insert month names in the worksheet. 1. Type January in cell A3 (Fig. 9.18). 2. Position the pointer at the lower right corner of the cell. 3. The mouse pointer changes to a plus sign(+) and is known as the fill handle. 4. Hold down the left mouse button and drag the fill handle up to cell A14. 5. Release the mouse pointer. The days February to December will appear, automatically in the subsequent cells in the range. Similar mechanism works for names of days of week as well. Computer Science 169  MODULE – 2 Spreadsheets Office Automation Notes Fig. 9.18: Examples of AutoFill Feature Let us consider some more examples to use the AutoFill feature (Fig. 9.18). If we enter 20 in cell C3 and 19 in cell C2, select both the cells and then click and drag the fill handle, we will see that numbers in decreasing order are displayed in the selected range. In another example, if we enter 51 in cell E3 and then 53 in cell E4, AutoFill feature will display all odd numbers in the selected range starting from 51. Hence, this feature uses the difference between the first two numbers to fill the range of cells automatically. 9.12 SORT LISTS The AutoFill feature of OpenOffice Calc uses two lists that are already stored in the Calc application. These are names of months of the year and names of days of the week. That is why these are automatically filled in the selected range of cells. Such type of lists are called Sort Lists. We can create our own Sort Lists by following the steps given below: 1. Click Tools → Options. The Options dialog box is displayed with OpenOffice option already selected. 2. Click the plus sign (+) before OpenOficeCalc option. 3. From the list of options click Sort Lists option. The right side of the dialog box shows the sort lists already stored in Calc (Fig. 9.19). 170 Computer Science Spreadsheets MODULE – 2 Office Automation Notes Fig. 9.19: Options dialog box 4. Click New button. The cursor appears in the Entries box. 5. Type each entry (in the desired order), pressing Enter key after each list item. In Fig. 9.20, we have typed the names of the subjects. Fig. 9.20: Creating a Sort List 6. Click the Add button. In the Lists box, the new list appears in the same order as we entered in the Entries box. 7. If the elements of the list are typed in the worksheet and we want to create a sort list with them, we may select the entire list and then open the Options dialog box as per the method given above. The selected range will be Computer Science 171  MODULE – 2 Spreadsheets Office Automation displayed in the Copy List from text box. Click Copy button to add to the main Sort Lists text box. 8. Click OK to close the Options dialog box. Now if we type English in any cell on the worksheet and drag the fill handle, we will see that the subjects entered while creating the lists automatically fill up the selected range. Notes Fig. 9.21: AutoFill using the created Sort List Also, if any of the sort lists have to be deleted, simply select the list and click Delete button in the Options dialog box. INTEXT QUESTIONS 9.2 1. Fill in the blanks: (i)...................... is the shortcut key to edit data in cells. (ii) Press...................... key while selecting adjacent cells in a worksheet. (iii) Cancel Editing button is present on the...................... toolbar. (iv) All formulas must start with an...................... sign. (v) The data on which arithmetic calculations cannot be performed is called...................... 2. How can you select the entire row in a worksheet? 3. What is the use of AutoFill feature? 4. Name the two ways of editing cell contents in OpenOffice Calc. 172 Computer Science Spreadsheets MODULE – 2 Office Automation 9.13 FORMATTING CELL CONTENTS IN CALC Formatting is altering the style and appearance of data to enhance the appearance of the worksheet without affecting the data values. As we have done before that data can be either text or numeric, formatting can also be categorized into text formatting and number formatting. All the formatting commands are present on the formatting toolbar (Fig. 9.22). Notes Fig. 9.22: Formatting Toolbar 9.13.1 Text Formatting The desired font style, size and colour can be applied by selecting the text and clicking the appropriate icon on the formatting toolbar. We may even make the text bold, italic or underlined or even choose the background colour of cell(s) from the toolbar. Alignment refers to the position of the data with respect to the boundary of the cell. By default, numbers and Date/Time data is always right aligned while textual data is left aligned. Vertically, all types of data appear at the bottom of the cell. To change the alignment, click on the desired alignment button on the formatting toolbar. Orientation refers to rotating text according to the specified angle. This is often used for labelling narrow columns. The default orientation of the text is horizontal. To alter the orientation of the text, follow the steps given below: 1. Select the cell(s) for which the orientation has to be changed. 2. Select Format → Cells. The Format Cells dialog box is displayed (Fig. 9.23). 3. Click the Alignment tab. 4. In the Text orientation section, set the degrees by which the text has to be rotated. The preview shows the text accordingly. 5. Click OK button to view the rotated text on the worksheet. Computer Science 173  MODULE – 2 Spreadsheets Office Automation Notes Fig. 9.23: Format Cells dialog box – Alignment tab Fig. 9.24 shows the text ‘Activities’ at various degrees of orientation. Fig. 9.24: Text orientation 9.13.2 Merging and Wrapping Text These two features are helpful to display long text in a single cell. Wrapping text means to make the entire cell content visible in a single cell by displaying it in multiple lines of the same cell. On the other hand, merging is the feature that makes the long text to merge with the adjacent cells and form one large cell. Consider the long text ‘Invoice cum Receipt number for Electronic Items’ in cell A1 as shown in Fig. 9.25. Fig. 9.25: Long text 174 Computer Science Spreadsheets MODULE – 2 Office Automation To wrap this text, select the cell A1 and click Format → Cells → Alignment option. In the Format Cells dialog box, select Wrap text automatically checkbox in the Properties section (Fig. 9.26 (a)). The text will be wrapped in multiple lines as shown in the Fig. 9.26 (b). Notes (a) (b) Fig. 9.26: Wrapping text To merge cells, select the range of cells to be merged and click Merge Cells button on the formatting toolbar. In the example given in Fig. 9.25, select cells A1: C1 and then click Merge Cells button. The text will be merged into a single cell as shown in the Fig. 9.27. Fig. 9.27: Merging Cells Computer Science 175  MODULE – 2 Spreadsheets Office Automation 9.13.3 Number and Date Formatting Numerical data is predominantly found in almost all spreadsheet. Calc allows us to format numbers in a variety of ways like displaying currency, placing commas to separate long numbers, adding percentage (%) sign, specifying number of decimal places and so on. Similarly date and time can also be displayed in variety of formats. You can either use the formatting toolbar or perform the following steps to apply Notes number and date formatting to the cell contents: 1. Select the cell(s) whose content has to be formatted. 2. Select Format → Cells. The Format Cells dialog box appears (Fig. 9.28). 3. Select the Numbers tab, if not selected. Fig. 9.28: Format Cells dialog box – Numbers Tab 4. Choose the desired Category (Number, Percent, Date, Time etc.) and then the desired Format from the respective list boxes. The preview of the selected format is displayed in the Preview text box. 5. Click OK button to apply the selected style. 9.14 USING FUNCTIONS Functions are predefined formulas in any spreadsheet application. Various functions are provided by Calc to perform various operations on numerical, date, 176 Computer Science Spreadsheets MODULE – 2 Office Automation time and text data. These functions may perform mathematical operations like addition, subtraction, multiplication, average etc., or may display current date and time or may join two texts and so on. The data values given to these functions for performing the specified operation are known as arguments. These arguments may be given in the form of numbers or in the form of cell addresses. All functions must begin with an equal (=) sign. The basic structure of a function is shown below: Notes = Name of function(argument list) Here argument list may contain any number of arguments. Let us now learn to use some commonly used functions in OpenOffice Calc. Sum () The Sum () adds the numbers given in the argument list. The result is displayed in the active cell containing the function. The syntax for Sum() is =Sum (num1; num2; num3;……) For example, to display the sum of 20, 30 and 40, we have to type = Sum (20;30;40) in the active cell. The range of cells can also be mentioned in the formula. For example, to display the sum of numbers stored in cells C3 to C10 will be written as =sum(C3:C10). Consider the worksheet as shown in Fig. 9.29 containing marks in five subjects. Fig. 9.29: Finding total marks To calculate and display the total marks in cell B8, follow the steps given below: 1. Click the cell where sum has to be displayed. In our example, cell B8 is the active cell. 2. Type = sum (B2:B6) and press Enter key. The sum will be displayed in cell B8. Now, if we change any of the values in cells B2:B6, the sum will automatically change. Computer Science 177  MODULE – 2 Spreadsheets Office Automation Average () This function is used to calculate average of all the arguments being passed. Click on cell where average has to be displayed (B9, in Fig. 9.30) and type = Average (B2:B6). Press Enter key to display the average marks. Notes Fig. 9.30: Finding average marks Len () This text function displays the total number of characters in a cell. In Fig. 9.30, =Len(A2) will display the total number of characters in cell A2 (i.e., 7). Similarly =Len(A5) will display 14. Today () This function displays the system date and does not take any argument. Click the cell where the current date has to be displayed. Type = today() and press Enter key. The current system date will be displayed. 9.14.1 Function Wizard OpenOffice Calc provides numerous functions to perform various operations on data entered in the cells. All these functions can be accessed through the Function Wizard. To use Function Wizard, follow the steps given below: 1. Click the Function Wizard button ( ) near the Name box. The Function wizard starts(Fig. 9.31). 2. Select the category of functions from the Category list box. Let us calculate the minimum marks of all the subjects in the worksheet displayed in Fig. 9.30. So we choose the category as Statistical. 3. The corresponding list of functions is displayed in the Functions list box. Select MIN function. The name of the function appears in the Formula textbox (Fig 9.31). 178 Computer Science Spreadsheets MODULE – 2 Office Automation Notes Fig. 9.31: Function Wizard 4. To specify the argument list, click Select button in front of number 1 textbox. Click Function button ( ) on the 5. The Calc worksheet appears (Fig. extreme right of the Calc window to 9.32). Click and drag to select the display the docking toolbar range of cells from which the containing all the functions and also minimum value has to be found. In the Function Wizard button. our example, this range is B2: B6 (bordered with red colour in the Fig. 9.32). Fig. 9.32: Calc Worksheet to select the argument list 6. The selected range appears in the Argument text box. Click Select button again to go back to the Function Wizard. The function =MIN (B2:B6) is seen in the Function text box also. 7. Click OK to close the wizard and display minimum value in the active cell as shown in Fig. 9.33. Computer Science 179  MODULE – 2 Spreadsheets Office Automation Notes Fig. 9.33: Minimum Value using MIN () 9.15 CELL REFERENCING Cell reference is the address of the cell. By using cell referencing we can refer to a cell or a range of cells in a formula itself. In this way, the formulas will show updated results automatically if the value in a particular cell, being used in the formula changes or if the source cells are copied or moved. Based on how a cell reference in the formula is to be copied and used, cell referencing is of three types – relative referencing, absolute referencing and mixed referencing. 9.15.1 Relative Referencing In this type of cell referencing, when the formula is copied from one cell to another in a worksheet, the cell reference or address of the cells being used in the formula automatically changes according to the relative position of the copied formula. Hence, this type of referencing is based on the relative position of the cell in which the formula is being pasted with respect to the cell address appearing in the formula. Consider the example shown in Fig. 9.34. To calculate the sale price of the item keyboard, the formula to subtract discount from price ( =C3-D3 ) is entered in cell E3. Thereafter, the formula is copied to cells E4. The formula bar will now show the formula as C4-D4, thereby giving the correct sale price of item in row 4 i.e., mouse. Similarly, relative referencing is applied when the formula is copied to cells E5 and E6. This is because the relative position of the cell containing data and the cell containing the formula is same. 180 Computer Science Spreadsheets MODULE – 2 Office Automation Notes (a) (b) Fig. 9.34: Relative referencing 9.15.2 Absolute Referencing In this type of referencing, the address or the reference of the cell in the formula is not changed when the formula is copied from one cell to another. To make absolute reference of a formula, add a dollar ($) sign before the column and the row number. For example, $B$1. Now, when the formula is copied, the address of this cell (i.e., B1) will not change. In the example given in Fig. 9.35, the flat discount percentage is given in cell C9. So when the discount on keyboard is calculated (discount/100 * price of keyboard) , the formula used is = $C$9/100 * $C$3. This is absolute referencing. Now when the formula is copied to any other location, the cell address will not change. It will always be C9 for discount percentage and C3 for price. (a) Computer Science 181  MODULE – 2 Spreadsheets Office Automation Notes (b) Fig. 9.35: Absolute Referencing 9.15.3 Mixed Referencing This type of cell referencing is the combination of absolute and relative referencing. Hence in this type of referencing either the row number is fixed or the column name is fixed. The cell references like $B1 or B$1 are examples of mixed referencing. In Fig. 9.36 below, the formula =E3 * $F3 entered in cell G3 is an example of mixed referencing. Fig. 9.36: Mixed Referencing 9.16 SORTING OF DATA Sorting means arranging data in ascending or descending order. The data to be sorted may be numeric or textual or both. Consider the worksheet given in Fig. 9.37 showing the sales figures of five sales persons. Follow the steps given below to sort data in descending order of sales amount. 1. Select the data range to be sorted (A4: B8). Fig. 9.37: Sorting of data 2. Click Data → Sort. The Sort dialog box appears (Fig. 9.38). 182 Computer Science Spreadsheets MODULE – 2 Office Automation Notes Fig. 9.38: Sort dialog box 3. The Sort Criteria tab is already selected. Specify the column according to which the data has to be sorted. In our example, it is column B. 4. Select the order in which the data has to be sorted – ascending or descending. Since we have to sort in descending order of sales amount, so we select Descending radio button. 5. Calc gives you option to sort data according to multiple columns also. Select the column in Then by list box in that case. 6. Click OK to see the data sorted in descending order of sales amount (Fig. 9.39). Fig. 9.39: Data sorted in descending order of Sales Amount Computer Science 183  MODULE – 2 Spreadsheets Office Automation 9.17 FILTERING DATA Filter feature of OpenOffice Calc blocks or hides the data based on specified criteria for a column. Unlike sorting, the data is not rearranged in case of filtering. Instead, the filtered subset of data is extracted and the rest is temporarily hidden from the user. This filtered data can then be edited, analysed, formatted or even printed. Consider the following worksheet (Fig. 9.40) and follow the steps given below to see filtered data. Notes Fig. 9.40: Data before applying filter 1. Select the column(s) on the basis of which filtering has to be done. Since we want to filter records based on Column B i.e., Hobby and Column C i.e., Marks, so we select the range B3:C9. 2. Select Data → Filter → AutoFilter. You will see that a filter icon ( ) appears on column headers i.e., Hobby and Marks (Fig. 9.41) indicating that the filter is applied on these two columns. Fig. 9.41: Filtering Data 3. If we want to view the data of the students who have hobby as reading, then click the filter arrow in the Hobby column header and select Reading 184 Computer Science Spreadsheets MODULE – 2 Office Automation from the submenu (Fig. 9.42 (a)). The filtered data is displayed on the worksheet (Fig. 9.42(b)). Notes (a) (b) Fig. 9.42: Displaying Filtered data 4. Similarly if we want to filter data of those students whose marks are less than 70, click the filter arrow in Marks column header and select Standard Filter option. The Standard Filter dialog box will be displayed (Fig. 9.43). The field name, Marks is already entered. From the Condition list box, select