Calc

CALC The spreadsheet component of OpenOffice.org

Adapted from the training manual on http://www.documentation.openoffice.org/manuals (October 11, 2005 version, based on OpenOffice.org 2.0). The Original Documentation is Calc: the spreadsheet component by Dave Le Huray, Jim Taylor, Ken Jones, Peter Kupfer, Jean Hollis Weber, Linda Worthington. Extra source: Computing for Life Book A by Tan. FNB Publicaitons. 2005.

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

TABLE OF CONTENTS GETTING STARTED................................................... ..................5 Workbooks, Worksheets and Cells.....................................................................................5 Parts of the Calc Screen.......................................................................................................6 WORKBOOK OPERATIONS..........................................................9 Creating a New Workbook..................................................................................................9 Opening an existing workbook............................................................................................9 Saving workbooks ...............................................................................................................9 NAVIGATING WITHIN WORKSHEETS................................... ........11 Going to a Particular Cell to Make it the Active Cell.....................................................12 Moving from Cell to Cell...................................................................................................12 Moving from Sheet to Sheet..............................................................................................12 Renaming worksheets........................................................................................................13 SELECTING ITEMS IN A WORKSHEET.........................................14 To Select a Cell....................................................................................................................14 To Select a Range of Contiguous Cells ............................................................................14 To Select Cells which are Non-Contiguous......................................................................15 To Select an Entire Column, Row, or Sheet.....................................................................15 To Select More Than One Worksheet...............................................................................15 ENTERING DATA INTO A WORKSHEET........................................17 Entering Text......................................................................................................................17 Entering Numbers..............................................................................................................17 Entering Numbers as Text.................................................................................................17 Entering Dates and Times..................................................................................................17 Entering Data into a Column or Row...............................................................................19 Auto-Complete....................................................................................................................20 EDITING COLUMNS AND ROWS.................................................22 Inserting and Deleting Columns and Rows......................................................................22 INSERTING AND DELETING WORKSHEETS..................................23 To insert new worksheets...................................................................................................23 To delete worksheets...........................................................................................................23 To move/re-position worksheets........................................................................................23 EDITING DATA IN CALC........................................ .....................23 Removing text.....................................................................................................................23 Typing over text (Overwrite).............................................................................................24 Commission on Information and Communications Technology – Human Capital Development Group Page 2 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Editing part of a cell...........................................................................................................24 Sorting Data........................................................................................................................24 FORMATTING DATA IN CALC................................................. .....27 Numbers..............................................................................................................................27 Font......................................................................................................................................27 Alignment............................................................................................................................29 Borders................................................................................................................................29 Background.........................................................................................................................29 Cell Protection....................................................................................................................30 Merge Cells ........................................................................................................................30 FORMATTING ROWS & COLUMNS..............................................30 Columns...............................................................................................................................30 Rows.....................................................................................................................................31 OTHER FORMATTING TECHNIQUES...................... ......................31 Inserting Objects................................................................................................................31 Auto-Format.......................................................................................................................32 Conditional Formatting.....................................................................................................32 USING FORMULAS IN CALC................................... ....................34 Writing a Formula..............................................................................................................35 The Pointing Method.........................................................................................................36 PEMDAS and the Hierarchy of Operations....................................................................37 Copying Formulas..............................................................................................................38 FUNCTIONS............................................................ .................42 VIEWING LARGE WORKSHEETS.................................................48 Freezing and Splitting........................................................................................................48 Splitting the window...........................................................................................................48 CHARTS AND GRAPHS........................................... ...................49 Inserting a Chart................................................................................................................50 Editing a chart....................................................................................................................53 Moving and resizing a chart..............................................................................................54 PRINTING.................................................................... ............54 Row/Column Breaks..........................................................................................................54 Scale.....................................................................................................................................54 Repeating Rows/Columns..................................................................................................55 Defining a print range........................................................................................................55 Adding to the print range..................................................................................................56 Commission on Information and Communications Technology – Human Capital Development Group Page 3 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Removing a print range.....................................................................................................56 Selecting sheets to print.....................................................................................................56

Commission on Information and Communications Technology – Human Capital Development Group Page 4 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

CALC is the spreadsheet component of OpenOffice.org (OOo). You can enter data in a spreadsheet and then manipulate this data to produce certain results. Alternatively you can enter data and then use Calc in a ‘What If...’ manner by changing some of the data and observing the results without having to retype the entire workbook or sheet. You may use it to record, compute, and graph grades for all your classes; tabulate financial records; log experiment results and get statistical findings; graph mathematical formulas; and many many more. A major advantage of electronic spreadsheets is that the data is easier to alter. If the correct functions and formulas have been used, the program will apply these changes automatically. GETTING STARTED

Exercise 1 : 1. Load Calc. 2. Keep it open as you navigate your way around the application in the next section. 3. Familiarize yourself with the different parts of the Calc

Workbooks, Worksheets and Cells Calc works with elements called workbooks. A workbook is composed of a number of individual worksheets, each containing a block of cells arranged in rows and columns. These cells hold the individual elements; text, numbers, formulas etc., which make up the data to be displayed and manipulated. Each workbook can have several worksheets and each worksheet can have many individual cells. In version 2.x of OOo, each worksheet can have a maximum of 65,536 rows (1 through 65536) and a maximum of 245 columns (A through IV). This gives 16,056,320 individual cells per worksheet.

Commission on Information and Communications Technology – Human Capital Development Group Page 5 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Parts of the Calc Screen Title

Menu

Active

Toolbar

Column

Minimize, Maximize/Restore,

Formula

Row Headers Sheet Tabs

Figure 1. Parts of the Calc window

Commission on Information and Communications Technology – Human Capital Development Group Page 6 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

•

• •

•

Title bar: shows the name of the current workbook and the version of OOo in use. If the workbook is new, then its name is Untitled X, with X being a number Menu bar: shows a list of commands to choose from (Alt + underlined letter) Toolbars: shows icons that provide a wide range of common commands and functions  three default toolbars under the Menu Bar: the Object bar, the Function bar, and the Formula bar; the Main Toolbar runs vertically down the left hand side of the screen  tool tip: a small yellow box displayed when mouse is placed over a tool/ an icon; gives a brief explanation of the button’s function. Turning on Extended Tips under the Help menu, Help > Extended Tips, will provide a more detailed explanation of the buttons. Formula bar: When you enter new  Name Box: contains the cell reference data into a cell, the Sum of the active cell: designates a cell using and Function buttons the column header and row header (e.g. change into the Cancel A1, Z300), called the cell reference  Function Wizard: shows a list of functions available in Calc (more on this later)  Sum button: adds the numbers in the specified range of cells  Function button: inserts an equal sign into the Active Cell and the Input Line thereby preparing the cell to receive a formula  Input Line: alternative place where data/formula is displayed and can be edited (press F2) besides the active cell itself

Figure 2. Formula Bar •

Cells: each cell is at the intersection of a particular column and row, forming a grid  Column Headers: A to IV  Row Headers: 1 to 65536

Commission on Information and Communications Technology – Human Capital Development Group Page 7 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet •

Sheet tabs: enable access to each individual worksheet, with the visible, or active, sheet having a white tab (multiple sheets can be selected by holding down the Ctrl button while clicking the respective

Figure 3. Sheet Tabs

sheet tabs)

Commission on Information and Communications Technology – Human Capital Development Group Page 8 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

WORKBOOK OPERATIONS Creating a New Workbook A new workbook can be created from any OOo application. For example, a new workbook can be created from Writer or Draw. • •

•

Click on the File menu and then select New > Spreadsheet. OR Click the arrow beside the New Document button on the Function bar and select Spreadsheet Figure 4. Open File dialog box from the submenu. OR If you already have a workbook open, you can press CTRL+N to open a new Calc workbook. Tip: You can also open a workbook that has been recently worked on using the Recently Opened Files list, located in the File menu – Recent Documents. This list displays the last eight files that were

Opening an existing workbook To display the Open dialog box: • Click on the File menu and then select Open. OR • Click the Open/Import button on the Standard Toolbar. OR • Use the key combination CTRL+O. Saving workbooks To save a workbook : (default file extension is *.ods) • Click on the File menu and then select Save. •

•

Click on the Save button on the Standard Toolbar. This button will be greyed-out and unselectable if the file has been saved and no subsequent changes have been made. Use the key combination CTRL+S.

Figure 5. Save As dialog box

If the workbook has not been saved previously, then each of these actions will open the Save As dialog box where you can specify the workbook name and the location in which to save the workbook.

Commission on Information and Communications Technology – Human Capital Development Group Page 9 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Tip: If the workbook has been previously saved, then these options will overwrite the existing copy without opening the Save As dialog box. If you want to save the workbook in a different location or with a different name, then go to the File menu and select Save As.

Commission on Information and Communications Technology – Human Capital Development Group Page 10 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 2: 1. Calc automatically create a new workbook (Untitled1) when loaded. Try your hand at creating new workbooks. Notice the following:  simply clicking on the New Document button will create a new Writer document, not a Calc spreadsheet – you have to access the submenu to select spreadsheet to create one  each workbook opens in a separate window  the given filenames as you go along proceeds from Untitled1 to X 2. Go to the Windows menu to view a list of the files you have opened/created. The list shows which file you are currently in, designated by a bullet on the corresponding filename. 3. Close the workbooks, except for one, by going to File > Close, Window > Close Window, or by clicking the Close button on the Title Bar. 4. Save the remaining workbook to your designated Calc folder as exercises, make sure that you select .ods from the available file types.  Notice that you can save your file as an older version of OOo Calc (1.0) file, as a template, as a database, as html, or even as NAVIGATING WITHIN WORKSHEETS Worksheet Views The zoom function is used to change the view in order to see more, or fewer, cells on the window. This can be done by going to View menu > Zoom, or by double-clicking on the percentage figure in the status bar at the bottom of the window. Both methods will open the Zoom dialog box. This dialog Figure 6. Zoom Dialog Box box has the following options listed on the left-hand side: • Entire Page – an entire page fits within the height and width of the window; defined by the page format that has been applied to the sheet. • Page Width – the width of the page fits within the width of the screen, not necessarily showing the entire height of the page • Optimal – zooms the selected range to fit the screen and is normally greyed out to show that it is not available. To use this option, you must first highlight a range of cells. • Percentages – zoom the screen to a particular size, 100% being full size. • Variable – to set a zoom percentage of your choice. Either use the up and down arrows to the right of the entry field or enter the amount desired.

Commission on Information and Communications Technology – Human Capital Development Group Figure 7. Calc Page 11 of 57 Navigator

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Going to a Particular Cell to Make it the Active Cell • Place the mouse pointer over the cell and left-click. OR • Type the cell reference in the Name Box and press Enter. OR •

Click on the Navigator button in the Standard Toolbar (or press F5) to display the Navigator. Type the cell reference into the top two fields, labeled Column and Row, and press Enter.

Moving from Cell to Cell Keystroke movement →

Right one cell

←

Left one cell

↑

Up one cell

↓

Down one cell

Home End

To the start of the row To the column farthest to the right that contains data

Page Down/Up Down/Up one complete screen CTRL+ →

To last column containing data in that row or to Column IV

CTRL+ ←

To first column containing data in that row or to Column A

CTRL+ ↑

To first row containing data in that column or to Row 1

CTRL+ ↓

To last row containing data in that column or to Row 65536

CTRL+Home CTRL+End

To Cell A1 To lower right hand corner of the square area containing data

Alt+PageDown One screen to the right (if possible) Alt+PageUp

One screen to the left (if possible)

CTRL+PageDo One sheet to the right (in Sheet Tabs) wn CTRL+PageUp One sheet to the left (in Sheet Tabs) Tab Shift+Tab Enter Shift+Enter

To the cell on the right To the cell on the left Down one cells Up one cell

Moving from Sheet to Sheet Clicking one of the Sheet Tabs at the Commission on Information and Communications Technology – Human Capital Development Group Page 12 of 57

Figure 8. Sheet Tabs Shortcut menu

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

bottom of the spreadsheet selects that sheet. Each sheet is independent from the others though they can be linked with references from one sheet to another. To add sheets, click into the little empty space at the right of the last sheet tab or select Insert > Sheet from the Menu bar, or right-click on one of the sheet tabs and select Insert Sheet. • If you have a lot of sheets, then some of the sheet tabs may be hidden behind the horizontal scroll bar at the bottom of the screen. If this is the case, then the four buttons at the left of the sheet tabs can move the tabs into view. • Sheet numbering is arbitrary – you can name a sheet as you wish (use the Shortcut menu to rename a sheet). • Finally, you can move between sheets by using CTRL+PageUp (moves left one sheet) or CTRL+PageDown (moves right one sheet). Tip: Sheet names must Renaming worksheets start with either a The default name for the sheets is “SheetX”, letter or a number; where X is a number. While this works for a other characters small workbook with only a few worksheets, including spaces are it becomes awkward when there are many not allowed, although sheets. To give a sheet a more meaningful spaces can be used name, enter the name in the name box when between words. you create the sheet or right-click on a sheet Attempting to rename tab and select Rename Sheet from the popup menu and replace the existing name with a better one. •

Commission on Information and Communications Technology – Human Capital Development Group Page 13 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 3:

1.Make sure that the file named exercises.ods is open. Use Sheet1 for this exercise.

2.Use the arrow keys to navigate around the worksheet. 3. 4. 5. 6.

The cell where you are currently located has a black border around it – it’s what is called the active cell, meaning any data you enter at this point will be entered into that cell. Check how the Name Box changes the cell reference as you make different cells active. Enter the cell reference AB630 in the Name Box. Press Enter. See what happens. Use the Calc Navigator to go to another cell. Use the various keystroke & mouse combinations above to

SELECTING ITEMS IN A WORKSHEET To Select a Cell Click on the cell. To Select a Range of Contiguous Cells • by dragging the mouse: click on a cell, press and hold down the left mouse button and then move the mouse around the screen; once the desired block of cells is highlighted, release the left mouse button. Figure 9. (Left) One selected cell and (right) a group of selected cells •

without dragging the mouse  Using the mouse: 1. Click on the cell to serve as the first corner for the range of cells. 2. Move the mouse pointer down to the cell which is to be the opposite corner of the range of cells. 3. Hold down the Shift key and click. The range of cells will be highlighted as above.  Using the keyboard: 1. Click on the cell to serve as the first corner for the range of cells. 2. While holding down the Shift key, use the cursor arrows to select the rest of the range. Commission on Information and Communications Technology – Human Capital Development Group Page 14 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

To Select Cells which are Non-Contiguous Click at least two contiguous cells then Ctrl + click on another, and so on To Select an Entire Column, Row, or Sheet • Row or Column: Click the column/row header to select the entire column/row • An entire sheet: click the Select All button (small square located at the corner of Column Header A and Row Header 1) To Select More Than One Worksheet • Contiguous Sheets: click on the sheet tab for the first sheet, move the mouse pointer over the last sheet tab, hold down the Shift key and click. All the tabs between these two sheets will turn white. Any actions that you perform will now affect all highlighted sheets. • Non-Contiguous Sheets: Click on the sheet tab for the first sheet, move the mouse pointer over the second sheet tab, hold down the Ctrl key and click. Repeat as necessary. The selected tabs will turn white. Any actions that you perform will now affect all highlighted sheets. • All Worksheets: Right-click over any one of the sheet tabs and select Select All Sheets from the popup menu.

Commission on Information and Communications Technology – Human Capital Development Group Page 15 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 4:

1.Make sure that the file named exercises.ods is still open. 2. Select a cell and make it active – review past lesson. Select A1, then B1, then C1 one by one. 3. To select adjacent/contiguous cells A1, B1, and C1 at the same time: click on A1 then click and drag to C1 to cover the entire range. Notice that the Name Box shows A1:C1 – the colon is used to denote a range. As such, if you select the range A1 to C10, the Name Box will show A1:C10. Try it.  Select A1 or any other cell to unselect the range.  Try doing it differently: click on A1 to select it and make it the anchor cell. Then, holding down the Shift key, click on C10. This should select the range A1:C10. Another way to do this is by selecting A1 as the anchor cell then hold down the Shift key as you use the arrow keys to select adjacent cells towards C10.  Notice that you can choose to start with any corner of the range – so you can start with C10 then Shift+click on A1 OR start with C1 then Shift+click on A10…the range will still be A1:C10 in the end. 4. To select non-adjacent/non-contiguous cells A1, B2, and C10 at the same time: click on A1 then B2 and lastly, C10. Try it.  Notice that the Name Box just shows the cell reference of the last cell selected.  Note that the semi-colon is used to denote individually selected cells – as such, such a selection can be designated as A1;B2;C10 in the same way as A1:C1 can be designated as A1;B1;C1 5. Select a row by clicking on its Row Header – i.e. to select the entire Row 10, click on the Row Header 10; click and drag the corresponding Row Headers to select Rows 10 to 15,.  You can also hold down the Shift & Ctrl keys to select contiguous (e.g. Rows 10 to 15) and non-contiguous rows (e.g. Rows 10 and 15 only), respectively.  Try the same thing with Column G. Then with Columns G to D, then

Commission on Information and Communications Technology – Human Capital Development Group Page 16 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

ENTERING DATA INTO A WORKSHEET Most data entry in OpenOffice.org (OOo) Calc can be accomplished using the keyboard. You can enter both text and numbers using either the main keyboard area or the number pad. Each cell can contain an independent piece of data.

Figure 10: Independent cells

Entering Text Select the cell and type the text. Text is left-aligned by default. Entering Numbers Select the cell and type in the number. To enter a negative number, either type a minus (–) sign in front of it or enclose it in brackets ( ). By default, numbers are right-aligned and negative numbers have a leading minus symbol. Entering Numbers as Text If a number is entered in the format 0919, Calc will drop the leading 0. To preserve this, in the case of telephone area codes for example, precede the number with an apostrophe – like so: '0919. However, the data is now regarded as text by Calc. Arithmetic operations will not work on it. It will either be ignored or will produce an error of some kind. Entering Dates and Times Select the cell and type the date or time. You can separate the date elements with a slant (/) or – or type with text such as 10 Oct 03. Calc recognizes a variety of date formats. You can separate time elements with colons such as 10:43:45.

Commission on Information and Communications Technology – Human Capital Development Group Page 17 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 5:

1.Make sure that the file named exercises.ods is still open. 2.Rename Sheet1 as Personal Data. 3. Go to A2, type your full name.  Notice that as you type, the blinking cursor or insertion point moves along, pressing Enter will confirm your entry in A1 and move the active cell to the next cell below.  Notice, too, that the data appears both in the active cell as well as in the Formula Bar’s Input Line – you have the option to enter the data in either. 4. Type the following in the corresponding cells:  A3: your nickname  A4: the school you teach in  A5: years of teaching

Commission on Information and Communications Technology – Human Capital Development Group Page 18 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Entering Data into a Column or Row called “filling” to either fill with the same data or with data which changes in each cell. • To fill a range with the same data 1. In the first cell in the range, enter the data you want to fill the other cells. It can be text, a number or a formula. 2. Click on this cell, hold down the left mouse button and drag to select all the cells that you want this data to fill into. 3. Go to the Edit menu, select the Fill option and then choose the direction in which to fill. • Using the mouse: 1. Enter the data in the first cell that you want to fill into each of the other cells. 2. Click in this cell to select it. You will see a border appear around the cell and this border will have a small black square in the lower right corner (Auto-Fill Handle). Move the mouse pointer over this square and it will turn into a black Figure 11: Fill Series dialog cross. box 3. When it does so, hold down Ctrl key then click and drag down the column or across the row. 4. To create a Data Series (e.g. months of the year, sequential #s, Tip: You´ll notehold thatdown Fill works either rows butthe notautoboth at etc.), do not the Ctrl keyon as columns you clickor and drag the time. However, when to>fillFill an>area, you can fill fillsame handle. (same as using theyou Editwant menu Series option) multiple columns or rows by first filling one column or row, then selecting an area with that column or row at one edge, and then filling the area as if you were filling a single row or column.

Commission on Information and Communications Technology – Human Capital Development Group Page 19 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 6: Make sure that the file named exercises.ods is still open. Use the sheet Personal Data. Go to A1, type the number 1. Go to B1, type the number 2. Go to C1, type the number 3….on to E1. An easier way to do this would be to type 1 in A1 then just click and drag the Auto-Fill Handle to E1 so that Calc will automatically fill in the required data. If you want another increment instead of 1, enter 1 in A1 then enter 5 in B1, highlight the two cells A1 and B1 then click and drag the Auto-Fill Handle sideways – the values will now be 1, 5, 9, 13, 17. Try doing the same thing with months (work on A10:A21), days (work on B10:Z10), descending decimal numbers (work on B15 onwards), etc. For some reason, it cannot do the same fill action for the ordinary alphabet. Erase all other data except the personal data in Column A and the numbers 1 to 5 in Row 1. You can do this by selecting the cells concerned and pressing the Delete button. Press Ok when prompted. Go around the room and get data from four other colleagues. Enter their data in Columns B to E. Do not worry if some of the data seems shortened (symbolized by an arrow) – you’ll fix that soon enough.

Auto-Complete Calc tries to guess the rest of a text entry you are typing. When you are typing several identical text entries, Auto-complete can speed up data entry quite a bit. Here is how it works: Calc is aware of your previous text entries in a particular spreadsheet. When you enter some text in a column that starts in the same way as previous text in the same column, Calc will suggest the completion of the entry with the text previously entered – but with highlighted characters. To accept the suggested new characters, just press Enter or an arrow key. Otherwise, just keep typing or press Backspace if you have reached the end of your entry. If you keep typing, your Exercise 7: 1. Use Sheet2 in exercises.ods. 2. Create some text in some cells in a column. You will want several different entries. 3. Click in an empty cell in the column in which you have entered the text. Then right-click to activate a popup context menu. 4. Move the mouse pointer down the menu and left-click on Selection List. 5. A list will drop down below the active cell showing all the unique entries used in the column so far. 6. Click on the entry you want and it will be entered into the active cell. Commission on Information and Communications Technology – Human Capital 7. Alternatively, after you click in an empty cell in the column, press Development Group Page 20 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

characters will replace Calc’s suggested characters. If you press Backspace, the suggested highlighted characters will disappear.

Commission on Information and Communications Technology – Human Capital Development Group Page 21 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

EDITING COLUMNS AND ROWS Inserting and Deleting Columns and Rows • To insert a single column or row 1. Click on the column/row header to select the entire column/row 2. Do one of the following:  Go to the Insert menu and select Columns or Rows OR  hold down the left mouse button on the Insert Cells icon in the main bar and select Insert Columns or Insert Rows from within the extra toolbar that appears OR  Right-click on the column or row Figure 14. Insert menu identifier and select Insert Column or Insert Row from the popup menu. • To delete a column or row: Right-click on the column or row identifier and select Delete Column or Delete Row Tip: When you insert a from the popup menu. new column it is • To insert multiple columns or rows: inserted to the left of Highlight the required number of columns the highlighted column or rows by holding down the left mouse and when you insert a button on the first one and then dragging new row it is inserted across the required number of identifiers. above the highlighted Proceed as for inserting a single column or row. row above. • To delete multiple columns or rows: Highlight the required number of columns or rows by holding down the left mouse button on the first one and then dragging across the required number of identifiers. Proceed as for deleting a single column or row above. Exercise 8: Use the sheet Personal Data in exercises.ods. Select Column Header A. Insert a column to the left. Notice that the table that you’ve created will automatically move from A1:E6 to B1:F6. Type the following in the corresponding cells: A1: teacher # A2: full name A3: nickname A4: school A5: years of teaching A6: birthday Save your file. Experiment with inserting multiple columns/rows as well as deleting columns/rows. Just make sure the table goes back –toHuman its original Commission on Information and that Communications Technology Capital look. Development Group Page 22 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

INSERTING AND DELETING WORKSHEETS To insert new worksheets Click on the tab of the existing sheet that you want to place the new sheet next to, and then either: • • •

Click on the Insert menu and select Sheet, OR Right-click on its tab and select Insert Sheet, OR Click into an empty space at the end of the line of sheet tabs.

Figure 15. Insert Sheet Dialog Box

Each method will open the Insert Sheet dialog box. Here you can select whether the new sheet is to go before or after the selected sheet and how many sheets you want to insert. To delete worksheets 1. Right-click on the tab of the sheet you want to delete and select Delete from the popup menu. 2. To delete multiple sheets select them as described earlier, right-click over one of the tabs and select Delete from the popup menu.

Exercise 9: 1. Experiment with inserting, renaming, moving, and deleting sheets in exercises.ods. Just make sure that the sheet Personal Data is preserved. 2. Save your file. To move/re-position worksheets 1. Select the corresponding Sheet Tab. 2. Drag it to its new position in the Sheet Tab area. EDITING DATA IN CALC Editing text is done is almost the same way it is entered. The first step to editing text is selecting the cell with the text that needs to be edited. Activate a cell for editing by double-clicking on it or selecting the appropriate cell then pressing F2. Removing text • Removing text only: without removing Figure 16: Delete Contents dialog box Commission on Information and Communications Technology – Human Capital Development Group Page 23 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

any of the formatting of the cell: press Backspace •

Removing text and formatting simultaneously: press Delete to bring up the Delete Contents dialog box (see Figure 16) that allows you to customize the action. To delete everything in a cell (contents and format), check Delete all.

Typing over text (Overwrite) To remove text and insert new text, simply type over the old text. The new text will retain the original formatting. Editing part of a cell Sometimes it is necessary to edit the contents of cell without removing all of the contents, for instance if the phrase “See Dick run” is in a cell and it needs to be modified to “See Dick run fast.” It is often useful to do this without deleting the old cell contents first. The process is the similar to the one described above, but you need to place the cursor inside the cell by doing any of two ways: •

Keyboard shortcut: After selecting the appropriate cell, press the F2 key and the cursor is placed at the end of the cell. Then use the keyboard arrows to move the cursor through the text in the cell.

•

Mouse: select the appropriate cell to edit. Once the cell is Exercise 10:

1.Using the sheet Personal Data in exercises.ods, experiment with deleting, overwriting/editing part of the cell, and finding and replacing. Remember, you have the option to edit the cell data either Figure in the cell itself or in the Formula Bar.17: Find and Replace dialog box 2. Save your file. selected, move the mouse pointer up to the input line and click into it to place the cursor for editing. You can either edit the text from the input line or, once the cursor is in the input line, click the mouse in the original cell for editing. •

Edit > Find & Replace (Ctrl + F): an easy way to replace specified data into something else

Sorting Data There are instances, such as when

Figure 18: Sort dialog box Commission on Information and Communications Technology – Human Capital Development Group Page 24 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

reporting student information or grades, when you would need to arrange your data in a worksheet either in ascending order, i.e. lowest to highest for numbers, alphabetical for text, or descending order, based on the values in one or more columns. This can be done by using the Data menu > Sort. Sort Ascending button: sorts data based on the values of the first column in the selected range Sort Descending button: sorts data based on the values of the first column in the selected range Note that you need to select or highlight the cells of corresponding data in a table. Now, what if you would like to sort your data but do not want the data from the first column to be your basis of reference? Calc allows you to sort using other columns through the Data menu, Sort option. The option shows a dialog box that allows you to pick the column letter which you would like the sorting process to base on. It likewise allows you to choose between ascending or descending. It allows you to pick a second and even a third basis of reference for sorting.

Commission on Information and Communications Technology – Human Capital Development Group Page 25 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 11: Use Sheet2 in exercises.ods. Rename it Sort Scores. Copy the table on the right. Highlight B2:B5. Click the Sort Ascending button – this will sort the ages from youngest to oldest. Notice though that when you did this, Zarah’s age became 18, Daphne’s became 22, and so on. This would cause a serious problem. What if the scores were the ones sorted erroneously? Then, Zarah would have the lowest score – she wouldn’t want that, would she? In order to sort corresponding data (i.e. the correct age and score should correspond with the name), highlight A2:C5 then use the Data menu > Sort feature to set the sorting criteria (i.e. which column to serve as the sorting reference). In this case, try setting Column C as the primary sorting criterion, ascending. Then press OK. [Note: Using the sort buttons on the Standard Toolbar would limit the sorting criteria to the first

 

Commission on Information and Communications Technology – Human Capital Development Group Page 26 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

FORMATTING DATA IN CALC The data in Calc can be formatted in several different ways (review discussion in Writer). It can either be edited as part of a cell style so that it is automatically applied, or it can be applied manually to the cell. To access the options to format a cell, select the appropriate cell or cells, right-click on it, and select Format Cells. All of the format options are discussed below.

Figure 20: Formatting Toolbar

Note: All the settings that will be discussed below can also be set as a part of the style using the Stylist. Numbers On the Numbers tab, the behavior of the data in the cell can be controlled: •

• •

Any of the data types in the Category list can be applied to the data. The number of decimal places and leading zeros can be controlled. Using the Language setting in this menu controls the local settings for the different formats such as the date order and the currency marker.

Figure 19: Format Cells > Numbers

Font The font for the cell can be chosen on the Font tab. The display on the bottom shows a preview of the cell. You can also set the language of the cell. The language setting is useful, because it allows different languages to exist in the same document and be spell checked correctly. The Font Effects tab offers more font options.

Figure 21: Format Cells > Font Commission on Information and Communications Technology – Human Capital Development Group Page 27 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Figure22: Format Cells > Font Effects

Commission on Information and Communications Technology – Human Capital Development Group Page 28 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Alignment The Alignment tab allows you can set the horizontal and vertical alignment, rotate the text, and set the border spacing. Note that you may enter multiple lines of text into a single cell. This may be done in any of two ways. Each method is useful for different situations. •

Wrapping text Text in cells can be set to wrap at Figure 23: Format Cells > Alignment the end of the cell. This option can be set by right-clicking on a cell and selecting Format Cells > Alignment tab. Near the bottom of the dialog is a checkbox labeled Automatic Line Breaks. Selecting this checkbox will cause a line break to appear when the text gets to the end of the cell. A sample output Figure 24: Cell with line wrap can be seen in Figure 24.

•

Manual Line breaks Multiple lines of text may also be entered by using manual line breaks. Line breaks put extra spaces in between lines of text. When a manual line break is Figure 25: Cell with manual line breaks entered, the cell width does not change. To insert a manual line break, press Ctrl+Enter while typing.

Borders As with Writer, the Borders tab allows you to set the borders for the cell, along with a shadow. Background The Background tab, on the other hand, allows you to choose the background color for the selected cell/s.

Commission on Information and Communications Technology – Human Capital Development Group Page 29 of 57 Figure 26: Format Cells > Borders

Figure 27: Format Cells > Background

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Cell Protection The Cell Protection options can be chosen here – advanced topic.

Figure 28: Format Cells > Cell Protection

Merge Cells To combine or merge cells, highlight the cells then go to the Format menu > Merge Cells > Define/Remove FORMATTING ROWS & COLUMNS Columns To format a column or set of columns, select that specific column or set of columns by clicking its/their Column Header/s. 1. Select the Format menu > Columns. You will now have a selection composed of Width, Optimal Width, Hide, Show. If you choose Width, you will be presented with a box that asks you to manually enter a number for the column width. Figure 29: Format > Column Estimate the number of characters you want in a column then type that number. The Optimal Width option automatically sets the column to the longest item in the column.

Commission on Information and Communications Technology – Human Capital Development Group Page 30 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

2. You may likewise use the separators of the column headers and the width changer mouse pointer to do the following:  Position your mouse pointer on the right boundary of Column Header. You will know that you are in the right position when the cursor turns from the normal pointing arrow into the width changer pointer.  To manually adjust the column width, click and drag the column boundary to the position you want. As you drag the column, you will see the column width of that specific column change. Release the mouse button once you have achieved the column width you de- TIP: There are some sire. cases when your  To auto-fit, double click the mouse numeric data will appear number signs on the column separator, where the as width changer is located. The (#######) in their column width will be adjusted auto- cells. This is Calc’s way matically to fit the length of your of telling you that the data. This option adjusts the data do not fit the cells. column width to the longest item in All you have to do to fix this problem is to adjust Exercise 12:

1.Using the formatting techniques discussed above, format the Personal Data sheet in exercises.ods. Experiment with the various features. It’s the only way to get comfortable with your preferred style. TIP: Holding down 2. Save your file. Alt+ the the column. (same as the Optimal respective arrow Width option). Most of the time this key resizes a cell is the fastest and easiest way to adjust your column width. Rows The row normally changes its height according to the font size of its contents. But if you wish to adjust the row height further, the procedures are very similar to changing the column width except that you will be dealing with the Row options instead. OTHER FORMATTING TECHNIQUES Inserting Objects You may choose to insert graphics, objects, special characters, drawings by following the procedures discussed in Writer. • Applying a background to a spreadsheet: 1. Select the cells. 2. Choose Format > Cells (or right-click on the chart and choose Format Cells from the context menu). 3. On the Background tab page, select the background color. Commission on Information and Communications Technology – Human Capital Development Group Page 31 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

•

Graphics in the background of cells

1. Choose Insert > Picture > From File. 2. Select the graphic and click Open. Note that the graphic is inserted and anchored to the current cell. To make the inserted graphic part of the background: Select it then right click to select Arrange > To Background. To remove a graphic from the background, use the Navigator. Press F5, select the graphic from the navigator. Double-click on the appropriate graphic, and then right-click on it and change the arrangement. Auto-Format Calc has available auto-formats to choose from; just highlight the cells to format then go to Format > Auto Format and select from the ready-made options Conditional Formatting Format > Conditional Formatting This feature allows you to format certain cells depending on specific parameters/conditions, without the need to manually format individual cells. Just set the conditions (i.e. below, above, equal to a certain value), then choose from the available cell formats listed in your Styles and Formatting Catalog (accessed via Format > Styles and Formatting, or by pressing F11). For example, if you would like to have all failing scores (i.e. cell value is “less than” 75) in a certain column (or less than 85 average score for the Sort Score contestants in exercises.ods), to appear as Style Fail as soon as they are entered. 1. You need to create or set this particular format in the Styles and Formatting Catalog. • Right-click on the Catalog to add a new Style. • Give a name to the new style and set its format by going through each of the tabs (e.g. setting the font to red, bold, and italics) in the Cell Style dialog box. Commission on Information and Communications Technology – Human Capital Development Group Page 32 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Press OK to create the new Cell Style in the Style Catalog. This new style can then be accessed in the Conditional Formatting dialog box. 2. Highlight the range of cells that you wish to conditionally format then click the Format menu, then click the Conditional Formatting option. • A dialog box will appear as shown below. • Set the parameters for the 1st condition then by choosing among the options in the dropdown menus shown. Set the specific value that will serve as the basis of comparison. Then select from the cell styles available, referenced against those in the Style Catalog. • You may set a maximum of three formatting conditions for the selected range. • Click OK. •

Commission on Information and Communications Technology – Human Capital Development Group Page 33 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Figure 30: Conditional Formatting dialog box

USING FORMULAS IN CALC By using FORMULAS in an electronic worksheet that use cell addresses (called “referencing”) instead of actual values, cells automatically accommodate changes in referenced cells to give you instant results. That is, you don’t have to compute the weighted scores of your students one by one anymore. All you need to do is set up the Calc table, enter and copy the cell-referenced formulas in their proper columns/rows, then just input the raw data to see student scores automatically reflected on your worksheet. You may set up one worksheet for the first quarter, another worksheet for the 2nd, another for the 3rd, and another for the 4th by copying the first worksheet and pasting it to new worksheets in the same workbook – then you could just create a summary worksheet on the same file for the cumulative marks and annual scores. Furthermore, you can reuse your original worksheet for several classes and just make minor revisions on it as you go along. A task that normally takes your entire Commission on Information and Communications Technology – Human Capital Development Group Page 34 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

weekend with the use of a calculator is now cut down to around an hour – doesn’t that make your life a lot easier? Writing a Formula A formula in Calc is an arithmetic expression that combines cell references, numeric constants, and operators to compute for a desired value in another cell. It is entered either in the cell where the value will be stored or in the Formula Bar. A Calc formula MUST begin with the equal sign (=) or else the formula will be treated as text. In addition, avoid using actual data values or constants in formulas to make them more flexible. As much as possible, put constants in their own cells and then just “call” these values through cell references.

Commission on Information and Communications Technology – Human Capital Development Group Page 35 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 13: Enter the number 20 in A1. In A2, enter the number 10. Go to cell A4. Enter the sum of 20 and 10 which is 30. It’s as if you are using a calculator. Go back to A4, simulate manual computation by entering the following formula: = 25+10. Do not forget to start with the equal sign. (Try typing it without the equal sign and see what happens.) In A5, enter the following formula: =20-10. Your worksheet should look like this: Calc automatically showed you the result of your formulas. But what if you change your raw data (in A1 and A2)? Go back to A1. Type 15 over 20. The value in A4 stayed as 30 while the one in A6 stayed as 10. Why is this so? This is because you used the actual numeric values in your formulas instead of the cell addresses of the raw data. The computing power of Calc actually lies in the use of cell addresses rather than raw values in formulas. In A4, overwrite the existing formula with the following: =A1+A2. In A5, overwrite the existing formula with the following: =A1-A2. Your worksheet should look like this:

The Pointing Method The pointing method allows you to enter formulas with the help of the arrow keys on the keyboard to minimize errors in entering cell references.

Commission on Information and Communications Technology – Human Capital Development Group Page 36 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 14: For example, to enter the formula = A1/A2 into cell A3: 1. Move the cell pointer to cell A3 to make it the active cell. 2. Type the equal sign (=) to begin the formula. 3. Press the Up arrow key twice or click the mouse on cell A1. Notice that Calc displays a red border around the cell and that the cell reference A1 appears in cell A3 as well as in the formula bar. 4. Type a division sign (/). 5. Press the Up arrow once or click the mouse on cell A2. Notice that A2 is added to the formula. 6. For longer formulas with more cell addresses and operators, just continue doing the same thing over and over until you finish the formula. 7. Press Enter to end the formula.

PEMDAS and the Hierarchy of Operations Mathematical operations available in Calc: Operato Function Sample Formula r ( ) Grouping/Parenth Note: do not use [ ] nor eses {} ^ Exponentiation =B4^2 squares the value in B4 =B5^B2 Raises the value in B4 to the power in B2 * Multiplication =B4*B2 multiplies the value in B4 by the value in B2 / Division =B4/B2 divides the value in B4 by the value in B2 + Addition =B4+B2 adds the values in B4 and B2 Subtraction =B4-B2 subtracts the value in B2 from the value in B4 Combination =B4*((B2+B3) averages the values in /2) B2 and B3, then multiplies the resulting value to the value in B4 • First order Parentheses • Second order Exponentiation Commission on Information and Communications Technology – Human Capital Development Group Page 37 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

• •

Third order Multiplication, Division – If a formula has both operators in consecutive places, work from left to right. Fourth order Addition, Subtraction – If a formula has both operators in consecutive places, work from left to right.

You have to follow this order when creating and evaluating formulas. Start with the first order operations followed by the second order operations and so on. So, those operations that are grouped must be evaluated first no matter where they are in the equation, followed by operations using exponentiation and so on. For example, =(B5+C1)*(A2^(1+B1)-C3) is not the same as =B5+C1*A2^1+B1-C3.

Exercise 15: Practice your PEMDAS skills on the following: 1. =A3+B 2 2. =C4-C3 3. =A1*C3

4. =A1/C3 5. =A1+A4+B2/B 4 6. =(A1+A4+B2) /B4

7. =(A1+A4+B2)/B4 +C3

10.=A4*B1+A2

8. =B4-B2-C1

11.=A4*(B1+A2)

9. =A4-C3+A1

12.=C4*C1/B2A1*A2

Copying Formulas To make your life much easier, Calc allows you to copy a formula downwards or sideways to create similar formulas for the other cells. This can be done by highlighting the cell and by using either the Edit menu (Copy-Paste), the shortcut keys (Ctrl+C then Ctrl+V), or the Auto-Fill Handle (click and drag). This would avoid unnecessary re-editing of cells if you need to change a value or a set of values – because everything else is linked to cells. • relative referencing: Calc adjusts the cell references as it copies formulas downwards or sideways • absolute referencing: Calc secures or locks a part of the formula to a certain cell reference by using two dollar signs in the cell address: one before the column letter and another before the row number, for example, $A$5. You may either type the dollar signs or press Shift+F4 while typing or editing that part of the formula. NOTE: If a formula that contains both relative references and absolute references is copied, Calc will adjust all relative references but will keep “as is” all absolute references. NOTE: Cells from other worksheets may likewise be referenced (relative Commission on Information and Communications Technology – Human Capital Development Group Page 38 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

or absolute), – a cell could have the formula =’Qtr1’.E6. For example, if you want to create summary tables on student performance, you would need to reference the cells from the 1st to the 4th quarters worksheets to compute for their respective annual averages. Refer to the sample gradebook in your CDs (gradebook.ods).

Commission on Information and Communications Technology – Human Capital Development Group Page 39 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 16: Let us work on your first gradesheet. Load exercises.ods. Insert a new sheet. Name it gradesheet. Determine what sort of data are relevant to your needs. This will help you determine what fields to put in your worksheet. Set up your worksheet – which ones should be in columns, which ones should be in rows. As normally done with columnar record books, columns contain fields (e.g. column A could contain all the names of the students, column E could contain the scores of all the students in the Final Exam) while a student’s set of data can be seen in one particular row. For this exercise, let us suppose that you only have 5 students that took 5 quizzes only. You’ll be given time to work on the other fields, including tests, projects, exam, and finally, a subject quarter grade for each student. Do not devote too much time on formatting the worksheet except for the column width perhaps – you’ll have enough time to do that later.

Enter hypothetical values into the cells that need constants (namely: names, dates, HPS, and individual Quiz Scores, show one decimal place). We will work on the cells that would need formulas together (namely: Average Score, Passing Rate, Total Qzs, %Quiz, and Weighted Quiz Score).

Commission on Information and Communications Technology – Human Capital Development Group Page 40 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

For the cells requiring formulas: Go to J11 (Acuña’s Total Qzs), enter the formula =E11+F11+G11+H11+I11 to add up Acuña’s quiz scores. Since the formulas for J12 to J15 will be similar to the formula in J11 but with changing row numbers (i.e. for J12 or Alberto’s Total Qzs, instead of using the values in row 11, the values in row 12 will be used instead), use the concept of relative referencing to copy the formula downwards. Go to J11 and drag the Auto-Fill Handle downwards to J15. Check the formulas from J12 to J15, they should be correspond to their respective quiz scores. You could also copy the formula in J11 to J9 to get the Total HPS for the 5 quizzes – this time, use the copy-paste icons or Ctrl+C & Ctrl+V instead. For E7, enter the formula =(E11+E12+E13+E14+E15)/5 to get the average score for Q1. Copy it sideways to I7. Check your formulas to see if relative referencing is in effect – column letters changed when the formula was copied sideways. For E8, enter the formula =75% * E9 to get the passing score for each quiz. Copy it sideways to I8. Relative referencing should change the column letters as you copy sideways. For K11, you would need to get the %Quiz of Acuña to see if his overall Quiz Score went beyond 75%, i.e. passed (you cannot use Average Quiz in this case since the HPS for each quiz is different). Enter the formula =J11/J9 then format as %, with two decimal places. Copy the formula downwards. This produced errors. Why? Because the denominator also changed its row number as you copied downwards – it should be anchored to J9. How do you remedy this? Go back to K11, activate the formula by pressing F2. Go to the denominator J9, press Shift+F4 or manually insert a $ before the column and a $ before the

Commission on Information and Communications Technology – Human Capital Development Group Page 41 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

For the weighted Quiz Score – this depends on how much weight your department puts on quizzes. Assuming that the final grade is computed as 30% Quizzes + 30% Tests + 20% Project + 20% Final Exam, then the Weighted Quiz Score column should show how much each student has accumulated in terms of 30% of his final grade. Enter 30 in L9. For L11, enter the formula =K11 * $L$9. Copy the formula downwards. Acuña should have 23.54 out of 100 so far for his final grade, taken from his quiz component – meaning, he has to get a minimum of 51.46 from the other components to pass the quarter.

FUNCTIONS Functions are built-in formulas in Calc designed to compute specific values. A function can be used as part of a Calc formula. One thing that you need to remember when using functions is that different functions follow their own syntax (something like “grammar rules” in English) and have their own set of arguments or components to work with. A semi-colon (;) is used to separate arguments in a function.

Figure 32: Function Wizard dialog box

Calc has many built-in functions which can be accessed by clicking the Function Wizard button. Commission on Information and Communications Technology – Human Capital Development Group Page 42 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

The most common Calc function is SUM. This function (also accessed by using the summation button on the formula bar) adds the values in the cells specified. To add the values in A1, A2, B1, and B2, use =A1+A2+B1+B2 or =SUM(A1;A2;B1;B2) or =SUM(A1:B2) or =SUM(B1:A2) or =SUM(A2:B1) or =SUM(B2:A1). As you enter arguments in the dialog box, the function will also be shown in the formula bar. When all the arguments have been entered, just press OK to confirm the function for the cell. To evaluate conditions, use the following relational operators: mathemati in Calc mathemati in Calc cal symbol cal symbol > > >= or => ≥ < < <= or =< ≤ = = <> or >< ≠ NOTE: Calc formulas and functions are not case-sensitive. Thus, =SUM(B3:B1) is the same as =sum(b3:b1). Functions, however, are sensitive to the arguments used and the placement of the semi-colon (;). Do not put spaces between function names, parentheses, arguments, colons and semi-colons. Some useful functions: Functio Use n Name AND returns TRUE if all arguments are TRUE. If one of the elements is FALSE, this function returns the FALSE value AVERAGE calculates the average or mean of a range COUNT counts how many numbers are in the list of arguments; text entries are ignored COUNTIF returns the number of elements that meet the specified criteria within a cell range IF returns a value

Syntax and Arguments =AND(condition1; condition2; …)

Example =AND(B3=5;B4*3= A5)

=AVERAGE(range) =AVERAGE(elemen ts) =COUNT(range)

=AVERAGE(B1:B6) =AVERAGE(A1;B2:C 3) =COUNT(A1:A10)

= COUNTIF(range; “condition”)

=COUNTIF(A1:A10; “>=0”)

=IF(condition;Then =IF(A4
Commission on Information and Communications Technology – Human Capital Development Group Page 43 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

MAX MEDIAN

MIN MOD

MODE

OR

PRODUC T

based on a certain logical condition returns the maximum value in a list of arguments returns the median of a set of numbers; in a set containing an uneven number of values, the median will be the number in the middle of the set and in a set containing an even number of values, it will be the mean of the two values in the middle of the set returns the minimum value in a list of arguments returns the remainder after a number is divided by a divisor returns the most common value in a data set; if there are several values with the same frequency, it returns the smallest value; an error occurs when a value doesn't appear twice returns TRUE if at least one argument is TRUE; if all the elements are FALSE, returns the FALSE value multiplies all the numbers given as arguments and

value; Otherwise value) =MAX(range)

than”;A2+5)

=MEDIAN(range)

=MEDIAN(B1:B40)

=MIN(range)

=MIN(D10:F10)

=MOD(dividend; divisor)

=MOD(11;4) returns 3

=MODE(range)

=MODE(B1:B40)

=OR(condition1;co ndition2; …)

=OR(B3=5;B4*3=A 5)

=PRODUCT(range)

=PRODUCT(B3:B1)

=MAX(D10:F10)

Commission on Information and Communications Technology – Human Capital Development Group Page 44 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

ROUND

SUM

returns the product returns a number rounded to a certain number of decimal places according to valid mathematical criteria

adds the values of the cells in the range

combine evaluate inner d groupings first then or nested proceed outwards functions

=ROUND(value or =ROUND(12.567;2) cell address; will give you 12.57 number of digits) =ROUND(12.567;0) number of digits: will give you 13 if positive – number of decimal =ROUND(12.567;places; 1) will give you 10 if negative – number of places =ROUND(A4;2) to the left of the decimal place =SUM(range) =SUM(B3:B1)

=IF(ROUND(SUM(B 2:B4);0) rel="nofollow">3; PRODUCT(A2:A4)*5 ; IF(MAX(A2:A4)/2=5; “equal”, “not equal”))

Commission on Information and Communications Technology – Human Capital Development Group Page 45 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Commission on Information and Communications Technology – Human Capital Development Group Page 46 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Exercise 17: Practice your fx skills on the following: =SUM(A1:A6) =AVERAGE(A1:A6) =MAX(A1:A6) =MIN(A1:A6) =MIN(A1:A6)+MAX(A1:A6)*5 =MEDIAN(A1:A6) =MODE(A1:A6) =COUNT(A1:C6) =COUNTIF(A1:C6;"<0") =MOD(A4;A5) =PRODUCT(A4;B4:B5) =ROUND(PRODUCT(A4;B4:B5)/3;2) =IF(A4>C5;"greater than";"less than or equal to") =IF(A4>C5;"greater than";IF(C5
Exercise 18: Load exercises.ods. Use the sheet named gradesheet. Edit the formulas for the following: Total Qzs column: for J11, use =SUM(E11:I11). Isn’t this much easier than the original way? Clic Average Score (for each quiz): for E7, use =AVERAGE(E11:E15). Copy the formula sideways. Insert a new column to the right of %Quiz, label it as Remark. For L11, enter the formula =IF(K11 rel="nofollow">=75%;”pass”;”fail”) Copy the formula downwards. Use conditional formatting to use the Fail Style for the “fail” rem

Work on the other sections of your gradesheet. Add 5 more students. Put separate sections for Save your file.

Commission on Information and Communications Technology – Human Capital Development Group Page 47 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

VIEWING LARGE WORKSHEETS Freezing and Splitting This is especially useful when working on large worksheets, freezing allows you to lock a number of rows at the top of a spreadsheet or a number of columns on the left of a spreadsheet. Freezing both allows you to keep viewing any frozen columns and rows (usually those with fieldnames at the first few rows and columns) while scrolling around within the sheet. The figure on the right shows some frozen rows and columns. Note the heavier horizontal lines between rows 10 and 16 and the heavier vertical Figure 33: Frozen rows and columns line between columns B and L. Rows 11 through 15 and columns C through K have been scrolled off the page. Because the first ten rows and two columns are frozen into place, they remained. Here’s how do you freeze rows/columns: 1. Click into the cell that is immediately below the rows you want locked and immediately to the right of the columns you want locked. 2. Go to the Window menu > Freeze. 3. You will see two lines appear on the screen, a horizontal line above this cell and a vertical line to the left of this cell. Now as you scroll around the screen everything above and to the left of these lines will remain in view. To unfreeze rows or columns, go to the Window menu, uncheck ‘Freeze.’ Splitting the window Another way to change the view is by splitting the window – otherwise known as splitting the ‘screen.’ The screen can be split either horizontally or vertically or both. This allows you to have up to four portions of the spreadsheet in view at any Commission on Information and Communications Technology – Human Capital Development Group Figure 34: Split screen Page 48 of 57 example

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

one time. Why would you want to do this? Imagine you have a large spreadsheet (e.g. an annual record of student grades) and one of the cells has a number in which is used by three formulas in other cells. Using the split screen technique, you can position the cell with the number in one section and each of the cells with formulas in the other sections. Then you can change the number in the cell and watch how it affects each of the formulas. •

Splitting the screen horizontally 1. Move the mouse pointer into the vertical scroll bar, on the righthand side of the screen, and place it over the small button at the top with the black triangle. 2. Immediately above this button you will see a thick black line. Move the mouse pointer over this line and it will turn into a line with two arrows. 3. Hold down the left mouse button and a grey line will appear, running across the page. Drag the mouse downwards and this line will follow. 4. Release the mouse button and the screen will split into two views, each with its own vertical scroll bar.

•

Splitting the screen vertically 1. Move the mouse pointer into the horizontal scroll bar at the bottom of the screen and place it over the small button on the right with the black triangle. 2. Immediately to the right of this button you will see a thick black line. Move the mouse pointer over this line and it will turn into a line with two arrows. 3. Hold down the left mouse button and a grey line will appear, running up the page. Drag the mouse to the left and this line will follow. 4. Release the mouse button and the screen will be split into two views each with its own horizontal scroll bar. Splitting the screen horizontally and vertically at the same time will give four views, each with its own vertical and horizontal scroll bars.

•

Removing Split Views Double click on each split line OR click on and drag the split lines back to their places at the ends of the scroll bars OR go to the Window menu and deselect Split (removes all split lines at the same time).

CHARTS AND GRAPHS

Exercise 19: 1. Use the sheet named gradesheet in exercises.ods. 2. Highlight the ranges B11:B23 and K11:K23 (click on B11, Shift+click on B23, then Ctrl+click on K11, finally Shift+click on K23). 3. Use these ranges as you follow the instructions in creating a chart for your data.

OpenOffice.org Calc offers a variety of different ways to chart or graph Commission on Information and Communications Technology – Human Capital Development Group Page 49 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

your Calc data. Any time a chart is created, the chart AutoPilot is invoked. Open the spreadsheet and highlight (select) the data to be included in the chart. There are two ways to proceed after you have selected your data. The first method is Insert > Chart: The second method is to click the Insert Chart button on the Standard Toolbar. Inserting a Chart Figure 35. Selecting Insert Chart

1. Auto Format Chart

Figure 36: Auto Format Chart

Once Insert Chart is selected, the Auto Format Chart menu appears (Figure 36). The first dialog box is used to define the data range, the labels, and the target sheet for the chart. •

Data Range: should contain both the labels and the data. If the data range is selected when the Auto Format is started, it will automatically be inserted in the Range input field. If the Auto Format is started without a selected data range, a range can be selected by clicking on the range selection icon next to the Range input field.

•

Row/Column as Label  First Row as Label: Uses the entries in the top row of the selection as labels for the data range. This setting is useful if there are several columns of data that need to be displayed in the same chart.  First Column as Label: Uses the entries in the leftmost column Commission on Information and Communications Technology – Human Capital Development Group Page 50 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

of the selection as labels for the data range.  First Column & First Row as Label: Both checkboxes can be selected as well. This provides a combination of both options. This sets the first row as the labels and the first column as the xaxis values. •

Chart Results Output: specifies which sheet the chart will output to. By default, the current sheet is selected, but any sheet, or a new sheet, can also be selected. However, a chart cannot be set to be its own sheet; it must be an object in a normal sheet. Once the correct options have been input, either click Create to create the chart, or click Next to proceed to the next dialog to choose the chart type.

Commission on Information and Communications Technology – Human Capital Development Group Page 51 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

2. Choosing the chart type On the next page of the Auto Format, the chart type can be chosen and a preview of the chart output can be seen. Click Create from here, or click Next for more options. Figure 37: Choosing a chart type OpenOffice.org Calc offers (format) several different chart types. Most chart types come in both a 2-dimensional and a 3dimensional style. Below is a list of common chart styles: • • •

• • •

Lines (2D & 3D) – Provides a standard line graph that is useful for displaying changing data over a period of time. Areas (2D & 3D) – Creates graphs that are similar to line graphs but with the area under the line shaded in. Columns (2D & 3D) – Creates vertical columns to represent data. The columns can be normal, stacked, or by percent. In 3D mode the data can also be represented by cylinders and cones. Bars (2D & 3D) – Provides the same features as columns, but they are horizontal. Pies (2D & 3D) – Graphs that are useful for showing the parts of a whole. The pie chart can be separated or kept as a solid circle. XY Chart – This graph is similar to a scatter plot. It can be used to show several trials of experiments with two variables.

3. Choosing a Chart Variant There are several variants that can be chosen for each graph type. The chart can be created from this screen, or titles and labels can be added by clicking Next. 4. Adding Text: a title

and labels for the axes can be added.

Figure 38: Adding text Commission on Information and Communications Technology – Human Capital Development Group Page 52 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Editing a chart Figure 39: Chart Shortcut Menu 1. Select the chart (left-click). Right-click and select Edit. In this mode, right-click to access the shortcut menu for charts. 2. Alternatively, the chart can be double-clicked. This will activate a new toolbar on the topmost part or on the left side of the screen used for editing different aspects of the chart.

Figure 39: Edit Chart toolbar

Performance in Quizzes Acuña 78.46% Alberto 73.85% Castro 85.38% De Villa 90.00%

Students

Fabul 74.62% Gom ez 92.31% Lakandula 77.69% Padua 71.54% Santos 80.00% Silvestre 81.54% Sy 80.00% Tabun 73.85% Tengco 83.08%

%Quiz Scores Commission on Information and Communications Technology – Human Capital Development Group Page 53 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Moving and resizing a chart • To move the chart: 1. Click on the chart to select it. 2. Click and hold down the chart border to drag the chart across the window. (use the move icon) • To resize the chart: 1. Click on the chart to select it. 2. Click on one of the green handle bars on the chart border and drag the mouse. PRINTING OpenOffice.org Calc offers a very powerful and highly configurable printing system. Many different details can be selected to print or not to print. The order the sheets will print in can be specified, as well as what size they will be. Particular rows or columns can be specified to print on all sheets and the print range can be specified. In Calc you can specify certain details to print or not to print. Some of those details include: • Row and column • Sheet grid • Header/Footer headers • Objects and • Charts • Drawing objects graphics • Formulas • Notes To select the details to be printed: 1. Choose Format > Page. 2. Select the Sheet tab. 3. In the Print area, mark the details to be printed and click OK. NOTE: Use File-Page Preview to view how your sheet will look like before actually printing it. You may edit the settings by clicking the Page button in the Preview window. Row/Column Breaks Figure 40: Choosing printing options in the This is selected when you want to Page Style dialog box manually set print breaks within your worksheet which appear as blue lines (Insert > Manual Break > Row/Column Break) Scale • Reduce/Enlarge printout (%) – This option will scale all of the data in the printout exactly the same either larger or smaller. For example if a Commission on Information and Communications Technology – Human Capital Development Group Page 54 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

•

sheet would normally print out as 4 pages, a scale of 50% would cause that printout of four pages to print as one page. Fit printout to x pages – This option will define exactly how many pages the printout will take up. However, this option will only reduce a printout, it will not enlarge it. To enlarge a printout, the reduce/enlarge option must be used.

Repeating Rows/Columns Printing rows or columns on every page: If a sheet will be printed on multiple pages, certain rows or columns can be set up to repeat on each

Figure 41: Repeating Rows/Columns

printed page. As an example, if the top two rows of the sheet as well as column A need to be printed on all pages, do the following: 1. Choose Format > Print Ranges > Edit. 2. The Edit Print Ranges dialog appears. Click the icon at the far right of the Rows to repeat area. The dialog shrinks so that you can see more of the sheet and thus be able to highlight the print range. (Figure 41). 3. Select the first two rows of the sheet. To do this, click cell A1 and drag to A2. In the shrunk dialog $1:$2 will appear. Rows 1 and 2 are now rows to repeat. Note: The entire range of the rows to be repeated does not need to be selected. Just selecting one cell in each row will work. 4. Click the icon at the far right of the Rows to repeat dialog area (or press Enter). The dialog is restored again. 5. Column A can also repeat; click the icon at the far right of the Columns to repeat area. 6. Click column A in the sheet (not in the column header). 7. Click the icon again at the far right of the Columns to repeat area. Defining a print range This option allows you to modify or set a defined print range. This could be useful if, in a large spreadsheet, only a specific area of data needs to be Commission on Information and Communications Technology – Human Capital Development Group Page 55 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

printed. 1. Highlight the range of cells that comprise the print range. 2. Choose Format > Print Ranges > Define. 3. The page break lines will display on screen. Note: You can check the print range by using File > Page Preview. OOo will only display the cells in the print range. Adding to the print range 1. After defining a print range, more cells can be added to it. This allows multiple, non-contiguous, areas of the same sheet to printed, while not printing the whole sheet. 2. Once you have defined a print range: Highlight the range of cells that should be added to the print range. 3. Choose Format > Print Ranges > Add. This will add the extra cells to the print range. The page break lines will no longer show up on the screen. Note: The additional print range will print as a separate page, even if both ranges are on the same sheet. Removing a print range 1. Choose Format > Print Ranges > Remove. This will remove all defined print ranges on the sheet. 2. After the print range is removed, the default page break lines will appear on the screen 3. Select:  none to remove a print range definition for the current spreadsheet.  selection to define the selected area of a spreadsheet as the print range.  user-defined to define a print range that you have already defined To use the selection option, follow the same procedure as selecting rows to repeat. Click on the dialog button, and highlight the range you want to print. Selecting sheets to print In addition to defining a range, the sheet to print can also be specified. This can be useful if you have a large workbook with multiple sheets and only want a certain sheet to print. 1. Select the sheets to be printed. (Hold down the Ctrl key as you click on each sheet tab.) 2. Go to File > Print... and select Options... in the lower left corner. Note: The Options... button is different from the Properties... button. Properties deals with the settings of the printer, whereas Options deals with OOo’s settings. 3. Check the Print only selected sheets checkbox. 4. Click OK. Commission on Information and Communications Technology – Human Capital Development Group Page 56 of 57

iSchools Computer & Internet Literacy Course for Teachers Electronic Spreadsheet

Commission on Information and Communications Technology – Human Capital Development Group Page 57 of 57