ICDL Module 4

This will load Calc

OR

Menu Bar

The menu bar displays global commands. These commands that are grouped according to categories.

For example, the File menu item consists of a set of commands related to file operations such as open existing files, creating new files, saving files, closing files and so on.

Function Bar

The function bar displays the path and name of the current file as well as a series of icons representing commonly used commands.

Object Bar

The object bar contains commands that are used to format cells.

Formula Bar

The formula bar contains the address of the current cell. It is also used as the entry point for text, numbers and formulas that will be entered into the cells.

Toolbar and Work Area

The toolbar down the left hand side provides a quick method of accessing common spreadsheet functions.

The work area of the spreadsheet consists of a grid formed by the intersection of rows and columns. The columns are named by letters of the alphabet A, B, C, ..... Z, AA, AB, AC, ..... The rows are numbered 1,2,3,4, .....

Each cell is identified by its row and column. For example, the cell C11 is the cell lying at the intersection of column C and row 11.

C11 is also referred to as the address of the cell..

Tabs

Each spreadsheet consists of one or more worksheets. These can be accessed by clicking on the tabs at the bottom of the work area.

Open one, several spreadsheets

A spreadsheet consists of text, values and formulas inserted into the cells of the work area. Spreadsheets are saved on disk as files, just as in the case of Writer documents.

More than one spreadsheet can be opened at a time.

Open a spreadsheet under Linux

1. Click on File.

2. Click on Open.

   This brings up the Open Dialogue window:

   The dialogue lists a series of directories and files. To view the contents of a directory, click on the directory. To move up a level, click on the Up one level icon, .

   Suppose you have located the directory in which the desired file is located.

3. Highlight the file.

4. Click Open.

Open a spreadsheet under Windows

The process of opening files under Windows is essentially the same.

The essential difference between Windows and Linux is that in Linux all computers, disks and directories are all part of a large tree. In the case of Windows, each drive forms the root of a separate root structure.

The current directory is the Open dialogue above is shown as /home/david. The direction of the separators and the absence of a drive indicate that this is a Linux directory.

If you were opening a file in Windows, the Windows Open dialogue would appear:

Open several spreadsheets

If you now repeat this process without closing the first spreadsheet all of them will be loaded and you may switch between them. The process of doing this will be described shortly.

An alternative is to open several spreadsheets at once.

1. Click on File then Open.

2. Locate the directory containing the files you wish to open.

3. Click on the first file.

4. Hold down the Ctrl key and click on each of the additional files you wish to open.

5. Click Open.

   The last of the files loaded will appear in the window. This will be the active spreadsheet. The others are also loaded but are in the background.

You can make one of the other spreadsheets active as follows:

1. Click on Window.

   This will list the spreadsheets that are loaded.

2. Click on the spreadsheet you wish to make active.

Suppose you wish to create a spreadsheet from scratch.

You can do this whether other spreadsheets are loaded or not.

This will display a blank spreadsheet.

You will learn how to enter data later. At this stage we are concerned with the mechanics of opening and saving spreadsheets.

For the time being imagine that the spreadsheet you have opened contains data. At this stage your work is located in the RAM of the computer. Since RAM is volatile, the contents will be lost is the computer is switched off.

In order to preserve your spreadsheet for later use it must be saved on the hard drive of the computer.

To do so:

Saving a file in Windows

If you are working with Calc running under Windows, the Windows Save as ... dialogue would appear when you save the file.

1. Select the drive on which you wish to save the file.

2. Select the directory on the drive and, if appropriate, the relevant sub-directory.

3. Enter the name for the file in the File name window.

4. Click Save.

Sometimes we may want to have the same spreadsheet saved under a number of different names. Alternatively, we may want to save a spreadsheet before making changes. We would then have a copy of the new version as well as the version before changes were made.

Suppose we have a spreadsheet loaded as shown on the next screen. In this case the spreadsheet already has the name demog.sxc.

We can now save what is displayed on the screen in two ways.

A spreadsheet is a tool for doing calculations. Consequently, the format of the spreadsheet is structured around this aspect.

Sometimes it is useful to convert the spreadsheet format into another format such as, for example, a text file which can be manipulated using a word processor.

Alternatively, you may wish to save the spreadsheet in a format use by another spreadsheet program such as Microsoft Excel.

To save a spreadsheet in a different format:

The following table list some of the main file formats and a brief explanation of each.

On the file menu there is an option called Version which allows you to save different versions of the spreadsheet in the same file. You are asked to give the version a name and this is recorded along with the date and time of the changes that were made. If you want to open another version of a file simply click on File->Version and choose the version you want to open.

Switching between open spreadsheets was covered in the section called “Open one, several spreadsheets” above.

To understand the difference between a spreadsheet and a worksheet, think of a spreadsheet as a book. The worksheets correspond to the pages of a book. However, in the case of spreadsheets, many of them comprise a single page or worksheet.

The spreadsheet shown in the following screen is made up of three worksheets. Each of these has a name indicated on the tabs at the bottom of the work area.

Calc provides a range of help features. The most useful of these is Find.

To access help:

Calc also provides a comprehensive search facility.

Note that the two check boxes that appear under the Search item window.

The Bookmarks tab takes you to a section which allows you to create references to specific help items that you might wish to refer to at a later time. These named references are called bookmarks.

There are two menu items that need to be distinguished.

Close closes the current spreadsheet, but leaves Calc open. If version of the spreadsheet on the screen differs from that saved on disk, Calc will ask you if you wish to save before closing the spreadsheet.

Exit closes all open spreadsheets as well as the Calc program itself. Once again, if any spreadsheets are open, Calc will ask you if you wish to save them before closing.

It is important that you adopt good habits and close Calc before you start shutting down the system.

A useful zoom is Page width. This displays the width of the printed page on the screen. The degree of enlargement or reduction depends on whether the page setup is portrait or landscape. To see the difference follow the instructions below.

Calc has a number of standard toolbars. Which of these are displayed depends on how your system was et up. You can decide which toolbars you wish to have displayed.

An operation such as this is called a toggle. A toggle is a switch which reverses the status. If it is on, the toggle switches it off. If it is off, the toggle switches it on.

A related function is Full screen.

Certain rows and columns are often used as headings. If you scroll down or to the right, these headings will scroll off the screen. To keep headings on the screen will data scrolls off the screen, we can freeze the rows or columns containing our headings.

Consider the spreadsheet shown on the next screen.

Notice the column headings in row 3 and the row headings in column A.

If we were to scroll down and across these headings would disappear off the screen.

We can freeze the headings as follows:

If we were to scroll down and across, the headings would remain on the screen as shown below.

In this case both rows and columns are frozen.

If you wish to freeze only rows, position the cursor in column A, then Windows->Freeze.

If you wish to freeze only columns, position the cursor in row 1, then Windows->Freeze.

Data entered into cells can be of three types: numeric, text or date.

If a set of pure digits (0,1,2,3,4,5,6,7,8,9) are typed in a cell, Calc will normally treat the data as numeric. In this case arithmetic operations can be performed on the numbers.

If the ' symbol is typed in front of a set of digits, then Calc will treat the number as if it were ordinary text. In this case you will not be able to do arithmetic. You will however be able to format the numbers in the way you do text.

Text can be entered by clicking on the cell where you want to start at and typing normally. The text will flow over from one cell to the next, but will be contained in the cell where you started typing, eg if you wanted to type in a heading and you clicked on cell A1 and typed, the information may be longer than one cell and would carry on over into the adjacent cells, but if you wanted to edit it later you would have to activate cell A1 to work with it.

If you enter numbers in the format that Calc would recognise as a date, Calc will treat these as a date and reformat the contents of the cell in the default date format. For example, 20 Sept 2003, 20 September 2003 and 20-09-2003 will all be interpreted by Calc as dates. If the default date format is Short date, then Calc will reformat these as 20/09/03 in all cases. If you wish these not to be interpreted as dates, precede the entry with the ' symbol.

If the contents of a cell are interpreted as data, then certain types of date arithmetic can be performed. For example, two dates can be subtracted to yield the number of days between the dates. If a number is added to a date, this is interpreted as a number of days and the result will be another date.

Data can be entered into the current cell. As you type, numbers and text will be entered into a cell. At the same time these appear on the Input line of the Formula bar.

When you have completed your data entry, Press Enter to move to the cell below or Tab to move to the cell to the right.

You may also use the direction arrows to move to an adjacent cell.

Changing the direction of Enter

By default, when you Press Enter, the cursor moves to the next cell down. This can be changed using:

Tools->Options->Spreadsheet->Input

	Note on notation
	To simplify the text in future, where there is no possibility of ambiguity, the notation: Tools->Options->Spreadsheet->Input will be used instead of the longer form\: Click on Tools, then Options. Expand the Spreadsheets option by clicking on the + symbol Click on Input

Suppose we have the file Demog.sxc displayed:

We now wish to insert a row after % Urban with the row heading % High school education.

Suppose you wished to insert three rows after % Urban.

The process of inserting one or more columns is done in a similar way:

Existing columns will be shifted to the right.

	You can only use the right hand border for adjusting the column width.

	You must use the lower border of the cell. If you drag the upper border of a cell, you will change the row height of the cell above. For example, instead of making the lower cell wider, you will make the upper cell narrower.

OR

OR

Suppose we click on a cell, for example A8 in the following screen. The contents of cell A8 are also display in the Input line of the Formula bar. If you wish to add text to the existing contents of the cell:

Alternatively, you could edit the contents of the Input line as you would any text. You can insert text or delete text.

When you do this, the existing content of the cell will automatically be replaced by your new entry.

	The difference between the method for editing existing content and replacing existing content is that to edit the content you click on the input line whereas to replace you do not.

Suppose you delete a line of text, then immediately realise you have made a mistake. The undo command allows you to go back a step to the point before the previous command. To use this command:

OR

In fact the undo command has a history. If you repeat the command it will undo the second last command. If you repeat it once more it will undo the third last command, and so on.

The redo command reverses the effect of the last undo. To use this command:

Suppose you had used the undo command three times. In effect you would have undone your previous three command. If you now use the redo, it will re-instate these actions.

The best way of understanding how undo and redo work is to practice using them.

The process of duplicating data involves a number of simple steps:

	When you paste data, the contents of the target cells will be overwritten.

Suppose you have the following spreadsheet displayed on your screen.

Suppose you now wish to insert another set of headings above Group 11.

When you create a new spreadsheet, Calc creates three worksheets by default. You can think of each worksheet as a page of the spreadsheet. Pages can be added to or removed from the spreadsheet very simply. You can move between the worksheets of an open spreadsheet by clicking on the appropriate tab at the bottom of the work area.

Suppose you wish to copy cells A3:K5 from Sheet 1 to B7 on Sheet 3.

It is possible to have several spreadsheets open at the same time. The difference between spreadsheets and worksheets is that the spreadsheets are stored in different files, whereas the worksheets of a particular spreadsheet are all stored in the same file.

You can switch between spreadsheet using the Window menu item.

Suppose you have two spreadsheets open. The first is called ObservationsByGroupSector and the other is called Obs2. You now wish to copy the cells A3:K10 from the first into the second starting at B5.

You can create your own sort lists as follows:

You can use this method to create your own sort lists.

The procedure for moving the content of cell(s) is exactly the same as that for duplicating the content of cell(s) except that you use the Cut function instead of the copy function.

The effect is as shown on the following screen. As this screen shows the contents of the cell are emptied. No cells are moved in the process.

The operation of Edit->Delete cells is quite different to the action of the Delete key. You are offered a number of options as shown on the previous screen. All of these will involve movement of cells to the right and / or below the highlighted cells.

The Find command simply locates occurrences of an item. It does not change these in any way. You may do so manually, if you wish.

The Replace command on the other hand is useful when we wish to change entries.

The Replace command provides us with two methods of replacement.

Replace all replaces all occurrences of the item with the new entry without further prompting.

Replace gives you the option of changing each occurrence or not.

In this case all occurrences of Andiswa will be replaced by Andisa without any further prompting.

The check boxes allow you to refine the Find and replace procedures.

If, however, the Entire cells box had been checked, the result of this search would have been the following message:

Suppose we have the following spreadsheet displayed on our screen. We wish to sort the data on this in the order of the points with the lowest at the top. Here we wish to sort according to values, hence this is a numeric sort.

In this case, the data is located in the range A4:F36. We call each row between columns A and F a record and each column a field.

If we omitted any rows, the records would not be sorted. If we omitted any columns, the values in the omitted fields would be associated with the wrong records.

In the Sort by window select Column F. This is the column that contains the values that we wish to sort according to.

If you wished to sort the data by points in descending order, that is with the highest number first, you would have checked the Descending radio button.

Suppose we had wished to sort the data in the previous example in alphabetic order of name you would proceed as follows:

If you look at the above screen you will see that there are two names Andiswa. The first has higher points than the second. You can sort on multiple criteria so that, where there are multiple occurrences of the first sort criteria, these can then be sorted according to the second criteria. The following screen shows how to do this in the Sort dialogue.

A spreadsheet consists of one or more worksheets. The worksheets can be thought of as pages of the spreadsheets. By default, a spreadsheet consists of three worksheets. These are labelled Sheet1 to Sheet3.

New worksheets can be added to a spreadsheet.

The default names of worksheets are Sheet1, Sheet2, and so on. You may change the name of a worksheet as follows:

There are two methods of duplicating a copy of a worksheet. One method is to copy the entire contents of a worksheet to the clipboard and then pasting this into another existing worksheet. In this section you will make a duplicate of the worksheet in a new worksheet.

Duplicate a worksheet within a spreadsheet

You may insert a copy of a worksheet in another open spreadsheet or into a new document.

OR

The process of moving a worksheet within a spreadsheet or between spreadsheets is virtually the same as duplicating a worksheet, except that you do NOT tick the Copy checkbox in the Move/Copy Sheet dialogue.

Calculations are done within cells. In order to indicate that we wish to do a calculation in a cell, we preface the cell entry with the = symbol. This indicates to Calc that it must treat the cell entry as a calculation.

Calc records the entry in a cell as you enter it, but normally displays the result of the calculation.

For example, a cell could contain the entry: =7+8

Calc however displays the result 15. You need to make a distinction between the actual contents of a cell and what is displayed.

The four basic operations are as follows:

If we simply performed arithmetic using numbers, we may as well use a calculator. Suppose we have a simple situation in which one cell contains an amount of money and another an interest rate. If a third contains the product of the two (a product is the result of multiplying two numbers), this cell will in fact display the interest that would be earned on the capital. This is illustrated in the following screen:

The contents of cell C2 are: =A2*B2

Notice that the formula bar contains the actual contents of a cell. If the cell contains a calculation, the result of the calculation is displayed in the cell.

The = sign indicates that C2 contains a calculation. This calculation involves multiplying the contents of A2 with those of B2.

Now suppose we change the contents of cell B2, without making any change to C2, and press Enter as shown in the following screen:

The value in C3 automatically changes to reflect the changed value in B2. This tool is called automatic recalculation.

When you save a spreadsheet, the formulas are displayed so that they can be reused at a later stage.

Arithmetic operations using the values of cells are written down in the same way as arithmetic operations between values.

More complex calculations are carried out using the normal rules of arithmetic.

Suppose cell A2 contains the value 12, B2 the value 6 and C2 the value 8. The following table illustrates how these can be combined into arithmetic formulas.

	When you carry out arithmetic involving the different operators, remember that different operations have different priorities. The order is as follows: Work out the contents of brackets Carry out the multiplications and divisions Do the additions and subtractions

If you enter a formula and Calc is not able to interpret it, it will display an error in the cell. The main errors that you will come across are shown below.

Suppose cell D4 contains the formula =D1-D2. Suppose we now copy the contents of D4 to E4:G6.

The result is shown on the following screen:

If you look at this screen, you will see that both the column letters and row numbers can change when the cell D4 is copied.

Cell D4 contains the formula =D1-D2. This means find the difference between the cell three rows up in the same column and the cell two rows up in the same column. When D4 is copied, the formula will be adjusted so that the cell references in the target cells have the same relationship.

For example, F6 contains the formula =F3-F4. This also means the difference between the cell three rows up in the same column and the cell two rows up in the same column. When cell references such as this can be changed when the cell is copied, we say that we are using relative addresses.

If we put the $ symbol in front of a column letter or row number, then the reference will not change. The following screen indicates this type of addressing known as absolute addressing.

As this screen shows the addresses in the target cells are identical to that in the source cell.

You may make either the column or row absolute. When you do this the column or row reference will remain fixed when the cell is copied. This is referred to as mixed addressing.

The following two screens illustrate mixed addressing.

A function is an inbuilt utility that simplifies the creation of arithmetic expressions involving cell ranges.

For example, we could add up the contents of all the cells in the range B4: D7 with the expression:

=B4+B5+B6+B7+C4+C5+C6+C7+D4+D5+D6+D7

Obviously, the bigger the cell range we wish to add the more terms there will be in the expression.

The SUM function simplifies the process considerably. All we have to enter is the function:

=SUM(B4:D7)

	The = symbol in front of the SUM function. This means add the contents of all the cells in the range B4:D7.

The SUM function can be used in an arithmetic expression as shown in the following examples:

=SUM(C4:F8) + B6

=B6 – SUM(B7:B12)

=SUM(A3:B6) + SUM(C3:D9)

Syntax

The syntax of a function is the formal structure of the function. We write the syntax of the SUM function as:

=SUM(range)

Where range is the range of cells that are being averaged as, for example, B4:D7.

Syntax: =AVERAGE(range)

AVERAGE functions in exactly the same way as SUM, except that it calculates the average value of the cells in range.

Syntax: = MAX(range), =MIN(range)

These functions determine the maximum and minimum values in a range of cells.

Syntax: =COUNT(range)

COUNT returns the number of cells that contain values in range.

=AVERAGE(range) is actually the same as =SUM(range) / COUNT(range).

Often functions are used to summarise data in a spreadsheet. When you create a spreadsheet, it is good practice to put your summary information at the top of the spreadsheet and the data on which the summary is based below.

Suppose we wish to summarise the data in the spreadsheet below.

In this we may wish to find the sum, average, maximum and minimum of each column as well as the number of data entries in each column. We start by inserting four rows below row 2.

The next screen illustrates the situation at this stage. The functions have been displayed in the cells rather than the values. How to do this will be explained shortly.

To display the functions rather than values in the cells:

Where an entry is too wide to display in a cell, a series of hash symbols (#####) will be displayed.

In the above screen, the ##### in cells E6 and E7 indicates that the cell is too narrow to display the contents.

The IF function allows you insert different values, text or formulas in a cell depending on the result of a test.

Syntax: =IF(condition; TrueValue ; FalseValue)

Condition is a test which can evaluate to True or False. If Condition is True, then TrueValue is inserted into the cell. If Condition is False, then FalseValue is inserted into the cell.

Condition generally involves a comparison between two expressions. The comparison is created using one of the operators:

Suppose in the previous example, we wish to indicate whether a score is above average or below average by inserting the appropriate word in the adjacent column. We could proceed as follows:

To understand why we need mixed mode addressing rather than absolute addressing in C9, suppose we were to insert a column after column D and copy column C to the new column. We would get the following:

If we had used absolute addressing, you would then have obtained the entry =IF(D9>$B$7;”Above”;”Below”) when you copied cell B9 to E9. However the average for column D is found in D7 not in B7.

You can also instruct Calc to display specific decimal places. In the above screen this option is set to display 0 decimal places. (Look at Decimal Places under Options). You can change the value either by typing in a new value or by clicking on the arrows next to the box to either increase or decrease the value.

If you type data into a cell, Calc attempts to interpret the data according to one of its known data types. These include numeric, text and date.

Calc will interpret this as a date and reformat it according to its default date setting. For example, as soon as you press enter, the display of the above cell could become 3/11/03.

The following screen illustrates this. The input window on the Formula bar shows the actual data entry, while the cell illustrates how it has been reformatted.

If we look at the example in the section called “Number, Date” we see that although some of the figures refer to currency, the currency symbol is not displayed. This is simply remedied by formatting numeric data to currency format.

The next screen shows the previous spreadsheet with the profit calculated as a fraction.

In situations such as this it is more common to express the numbers in E5:E8 as percentages.

The simplest way to add underlining, bold or italic is to:

	You could have also changed the font style and size using the object bar.

Double underline does not appear by default on the object bar. In this case:

Suppose you have a single row of cells or a row of cells which you wish to display in a particular colour.

You can also change the background of the cells which, by default, is white.

Copying the formatting of a cell to another cell or range of cells involves a couple of steps.

The following example illustrates the process.

In the following screen, cell A6 has been formatted to Roman, 12 point, bold, red. We wish to copy this formatting to cells A5 and A7:A9.

This displays the Cell styles dialogue. This dialogue contains five icons:

The first icon is the Cell styles icon while the second is the Page styles icon.

On the right hand side, the first icon (paint can) is the Fill format mode icon. The next is the New style from selection and the last the Update style icon. An icon can be selected from each group independently.

	If you let your cursor hover over the icons, a tip will be displayed telling you what the icon is.

Suppose we have a column of cells that we wish to use to write notes. By default, if we write in a cell, the text will appear to spill over into the next cell. If we enter text in the next cell, part of our old text will cease to be visible.

The answer to this problem is to cause the text to wrap within the cell.

The effect is shown below.

Consider the following screen:

Suppose we wish to centre the heading across C2:H2.

Normally when we type text is a cell, its orientation is from left to right. Calc allows us to change the orientation of text very simply.

Suppose we wish to add a slightly unusual effect to the following screen so that the headings slope from left to right.

The gridlines that you see on your screen indicated the edges of the cells. You can, if you wish, add borders to cells or ranges of cells.

Suppose, in the following screen, we wish to put a border round the range of cells C5:H9 as well as vertical lines between the columns.

The Line arrangement section allows you to select a predefined option in Default or to create your own custom arrangement under User-defined.

	In the Borders tab you could have also added a shadow effect to your borders.

In the previous example, only a single data range (the rainfall) was displayed. We can display more than one data range.

The only change we really need make is to select the data labels and data when we start. In the example above:

The previous example illustrated how to add titles and labels to graphs during the creation process.

Suppose a graph had been created without labels and titles as shown in the following screen.

There are no labels or title on this chart. The processing of adding them is quite simple.

The result is shown on the following screen.

By default, there is no background colour to the chart. To add colour to or change the background colour of a chart:

When we create a line, bar or column chart, Calc assigns default colours to the different data series. It also applies default colours to the different segments of a pie chart. These default colours can be changed as follows:

Once a chart has been created it can be changed to another type of chart quite simply as follows:

Charts can be copied and moved just like any other object.

It may be that in creating a spreadsheet we have not been careful is setting our columns widths so that a spreadsheet can fit on a single page. In such a case, Calc can adjust the column widths so that the spreadsheet will fit on a single page.

To the right of the text attributes icon in the Headers / Footers dialogue are a number of other icons. Pressing any of these will insert information such as page numbering, date and file name.

The meaning of these icons is shown in the following table:

	File name		Number of pages in worksheet
	Sheet name		Date
	Current page number		Time

A spreadsheet is an example of a decision support system. We create spreadsheets so that data can be analysed in various ways and turned into information.

You can think of information as data that has been manipulated in various ways so that we are able to interpret the results. Ways in which we manipulate data include things like sorting, summing, averaging, and so on.

If our data is incorrect or if the logic of our manipulations is flawed, the information will be useless at best and dangerous at worst.

If our spreadsheet provides us with incorrect information, we will make incorrect decisions.

Before we make use of a spreadsheet or before we pass it on to someone else to make use of, here are some checks that should be carried out:

One simple check is to verify that our summaries provide reasonable values. For example, if you have a spreadsheet that determines the average sales of a group of representatives, this value will be somewhere between the largest and smallest values.

Just as numeric work needs to be accurate, the presentation needs to reflect professionalism. This means that the creator of a spreadsheet must check grammar and spelling as well as layout before releasing the spreadsheet. A poorly constructed spreadsheet will be treated with less respect by users, even if the numeric work is correct.

Calc allows us to see what the printed spreadsheet will look like without actually printing it. To use this facility:

The Page view object bar contains a number of icons that are relevant to working with the preview. The functions of these icons is explained in the following table:

	Previous page		Last page
	Next page		Zoom in (enlarge)
	First page		Zoom out (make smaller)

The Page format button will take you directly to the page format dialogue. You can make your changes and see the effect immediately.

The Close preview button takes you back to normal mode.

	You cannot edit a spreadsheet while in page preview mode.

Unless you specify otherwise, gridlines and row and column headings are printed when you print a spreadsheet.

To specify whether you wish these printed or not:

In the case of very large spreadsheets, you may wish to have certain rows or columns printed on each page. This would happen, for example, when certain rows or columns contain titles.

To achieve this:

An alternative method is to click in the window indicating the range or click on the shrink icon at the right of the window and select the range of rows or columns by dragging the mouse over them.