COMPUTER O LEVEL(FORM TWO) NOTES – TOPIC:2 SPREAD SHEET

TOPIC: 2 SPREADSHEET

A sheet is a piece of paper with vertical and horizontal lines.

A spreadsheet allows the operator to enter and store data in a computer in a grid format. Calculations are made according to the formulae entered. The program makes changes easily and quickly.

Any entry can be changed, and the effect of the change is calculated automatically. Entries can be numbers for calculation or words to form headings or formulae.

The worksheet of a spreadsheet is divided into several sections.

1. Column: Lines of cells down the worksheet. Each is identified by a letter. The identification letters start with A, B, C… They go through AA, AB, AC…, BA, BB, BC…, and so on up to IV, giving 256 columns.
2. Row: Horizontal lines across the worksheet. These are identified by numbers from 1 onwards up to 65,536.
3. Cell: Where horizontal and vertical lines cross, they form a cell. Each cell is identified by a column letter followed by a row number. For example, the first cell is A1. On the worksheet, information is entered in cells. Moving about on the spreadsheet is shown by a cell pointer. The current cell is indicated by a highlighted rectangle. There are 256 x 65,536 cells in a worksheet.
4. Label: Text entries on the worksheet are called labels. These should be kept to a minimum and are normally limited to column and row headings only.
5. Status area: The area on the screen display which gives information about the current worksheet. The information includes content of the current cell, name of the worksheet, formula, and graphical actions.
6. Cell address: The cell which is currently in use. The combination of row and column coordinates makes up a cell address. Sometimes it is known as the active cell or selected cell.
7. Range: An assortment of cells referring to all cells between and including the references. It consists of two cell addresses separated by a colon. For example, A1:A3 includes cells A1, A2, and A3.
8. Union: A union reference includes two or more references. It includes two or more cell addresses separated by a comma. For example, A7, B8, C9 refers to cells A7, B8, and C9.

Applications of the spreadsheet:

1. Used in decision making because it presents a convenient method of quick investigation of data.
2. Used for record keeping, e.g., student’s records.
3. Preparing budgets.
4. Planning different activities.
5. Creating charts for data presentation.
6. Preparing financial statements.
7. Preparing statistical data.

Starting Microsoft Excel

1. Click Start.
2. Go to All Programs.
3. Go to Microsoft Office.
4. Click Microsoft Excel.

Closing spreadsheet

Go to the right corner of the bar and click Close .

Entering data

Data is entered into the worksheet by moving the cursor to the appropriate position on the screen, clicking the left mouse button to select that cell, and then typing the information required. The characters you type will appear in the active cell and on the formula bar.

When you have finished typing data into a cell, you should signal the end of that data in one of the following ways:

• Press the Enter key (the cell below becomes the active cell).
• Press one of the arrow keys (the active cell moves one place in the direction of the arrow).
• Click on the Enter box (marked by a green tick) on the formula bar (the original cell remains the active cell).

There are two basic types of information that can be entered into a worksheet: Constants and Formulae.

The constants are of three types: numeric values, text values, and date and time values. Two special types of constants, called logical values and error values, are also recognized by Excel but are not discussed in this document.

Numeric values include only the digits 0-9 and some special characters such as + – E e () . , % /.

A numeric cell entry can maintain precision up to 15 digits. If you enter a number that is too long, Excel converts it to scientific notation. For example, if you type 12345678901234567, it will be stored as 12345678901234500 and displayed as 1.23457E16. Sometimes, although the number is stored correctly in the cell, the cell is not wide enough to display it properly. In those cases, Excel might show a rounded number or even a string of # signs — just increase the width of the column.

A text entry can contain up to 32,767 characters (only 1024 will display in the cell but all will show on the formula bar). If the text you enter will not fit in the particular width of your cell, Excel lets it overlap the adjacent cell unless that cell already contains an entry, in which case the extra text can be thought of as being tucked behind the adjacent cell.

Creating a simple worksheet

Instructions are given for creating the worksheet shown below. The first stage is to get the correct values in the cells. Later you can make the worksheet more attractive by using bold text, different alignment, and color.

5.1. Entering the data

1. Click in cell B2 and type Twiga secondary school.
2. Press the Enter key (or one of the arrow keys).
3. Click in cell B4 and type ID NO.
4. Press the down-arrow key to move to cell B5.
5. Type Rachel Maingu.
6. Press the down-arrow key and then complete column B as shown above.
7. Enter the data in columns D, E, F, and G (but not H and I).

5.2. Amending data

If you are in the process of entering data in a cell and you notice that you have made a mistake, it is easy to correct it. Press the Backspace key to delete a character to the left of the cursor or the Delete key to delete a character to the right of the cursor.

If you want to edit the contents of a cell you dealt with earlier, you should double-click in that cell and make the alterations either in the cell itself or on the formula bar.

If you want to clear a cell of its contents (formula and data), formats, comments, or all three, you can select that cell with a single click of the left mouse button, select Clear from the Edit menu and then click on Contents, Formats, Comments, or All.

There are a couple of quick ways of clearing the contents of a cell. The first is to use the right mouse button to click in the cell and then select Clear Contents from the menu that appears. The second is to select the cell by clicking the left mouse button and then press the Delete key on the keyboard.

Do not confuse using the Delete key with selecting Delete from the Edit menu. That command carries out the more drastic action of removing the entire cell from the worksheet and shifting the surrounding cells to fill in the resulting space.

5.3. Ranges

In Excel, any rectangular area of cells is known as a range. The range is defined by the top-left and bottom-right corner cell references separated by a colon (:).

So, B4:D7 represents the range of cells cornered by B4 and D7.

5.4. Copying and pasting

The text in cells E5:E8 (in other words E5, E6, E7, and E8) is the same as in the range B5:B8, so you can copy that.

1. Point to cell B5 and hold down the mouse button.
2. Keeping the button held down, drag the pointer to B8 and then release the mouse button. The range B5:B8 will be highlighted.
3. Click on the copy button on the toolbar (or select Copy from the Edit menu).
4. Click in E5 (where you want the copy to be placed).
5. Click the paste button on the toolbar (or select Paste from the Edit menu).

In addition, there is a smart tag (the Paste Options one) near the bottom right-hand corner of the pasted data.

A smart tag can be:

• Ignored – it will disappear when you carry out some other action.
• Removed – by pressing the Esc key.
• Used – point to it and click on the down arrow to display several options; select one and press the Enter key.

For now, just ignore the Paste Options smart tag and finish the process of entering the data.

1. Click in any cell to remove the highlighting.
2. Press the Esc key to remove the flashing dotted line (marquee) around the cells you copied.

Note: selecting non-contiguous ranges

If you need to select cells that are not contiguous (i.e., ranges that are not necessarily next to each other), proceed as follows:

1. Select the first area of cells.
2. Hold down the Ctrl key while selecting the other areas.

5.5. Adjusting the width of columns

If the “Twiga secondary school” text is too wide for column B, try widening that column.

1. Move the cursor to the division between the areas containing the column names. Note how the shape of the cursor changes to a vertical black line with arrows pointing right and left.
2. Double-click the left mouse button.

The column will be widened (if necessary) automatically.

If you wish to have control over the sizing of the column, it can be done manually.

1. Move the cursor to the division between the areas containing the column names.
2. Hold down the left mouse button and drag that column divider the required distance to the right.
3. Release the mouse button.
4. Repeat the process if you want to make fine adjustments.

5.6. Entering currency values

If you have a column containing amounts of money, you do not have to type the ₤ sign; instead, you can enter the numbers and format the cells as currency later.

Formatting the cells

1. Highlight the cells, e.g., F5 to F7 (point to F5, press the mouse button; while keeping the button held down, drag the pointer to F7; release the button).
2. Point to the highlighted area and click the right mouse button.
3. Choose Format Cells window, click on the Number tab (unless it is already selected).
4. In the Category: box select Currency by clicking on it.
5. Make sure that Decimal places: is set to 2; Symbol: shows ₤ and that the first option in the Negative numbers: box is highlighted. Sample: shows you what the result of the formatting will be (₤0.50).
6. Click on OK.

Alternatively, you could have formatted the cell first and then entered the values.

If you begin a numerical entry with a ₤ sign, Excel assigns currency format to that cell. Similarly, if you end a numerical entry with a % sign, Excel assigns Percentage format to that cell.

Note: Excel 2003 includes the Euro symbol € for currency.

5.7. Formatting a cell before entering a value

Now try formatting a cell before entering its value.

1. Click in F4.
2. Click on the Bold and Align Right buttons. The cell will not look any different until you enter a value.
3. Type Costs.
4. Press the Enter key.

The text should be in a bold font and right-aligned in the cell.

5.8. Formatting text

You have entered all the data you need but you might like to make the worksheet easier to read by formatting some of the text.

First, make the word Allocation bold.

1. Click in B4.
2. Click on the Bold button on the toolbar.

Next, apply a bold font to Adult in C4 and Child in D4 and center the text in the cells as follows:

1. Highlight cells C4:D4.
2. On the toolbar, click on the Bold, Italic, and Center buttons. If you can’t remember which those are, try pointing to each button in turn – a small pointer message will appear showing its name.

5.9. Using different sizes and colors

The charity Barbecue heading of this worksheet can be made more interesting by changing its size and colour.

1. Click in B2.
2. Click on the down-arrow of the Font Size button.
3. Select another size, for example, 12.

Your text in B2 will increase in size.

4. Click on the down-arrow of the Font Color button.
5. Select a suitable colour for your text.

It is possible to colour the background of cells and to centre a heading across a range of cells.

1. Highlight the cells B2:F2.
2. Click on the down-arrow of the Fill Color button.
3. Select a suitable colour for your background. Be sure to make it different from the colour of your text or you will not be able to see the heading!
4. Make sure that the range B2:F2 is still selected and click on the Merge and Center button. Your heading will be centered in that range of cells.
5. Click in any cell away from the heading.

5.10 Naming a sheet

The information on Sheet 1 might refer to Twiga Secondary School to be held in Term II. It would make sense to name the sheet accordingly.

1. Right-click on the tab Sheet 1.
2. Select Rename from the menu that appears.
3. Type Term II.
4. Press the Enter key (or click in a cell).

Adding a border

Sometimes a worksheet can be made more attractive by adding borders around cells.

1. Select the rectangle of cells starting with B2 in the top left-hand corner across to F7 in the bottom right-hand corner.
2. Click on the down-arrow to the right of the Borders button on the toolbar.
3. Click on the second example in the third row (All Borders). This shows borders around all the cells.
4. Click on a cell not currently selected (to remove the highlighting).

Another way of adding borders is to select Cells from the Format menu and click on the Border tab. First select a style for a line, then apply it by clicking the presets, preview diagram, or buttons on that Border tab in the Format Cells window. This approach gives you more options.

If you would prefer to draw the border around cells yourself, click on the down-arrow to the right of the Borders button on the toolbar and select Draw Borders. On the Borders toolbar that appears, select a colour and style for the line, and choose to draw either a grid or just a border. Then, just draw on your worksheet. An eraser is also available.

Inserting and deleting rows and columns

Extra rows and columns can be inserted whenever you wish. As an example, insert a row between rows 4 and 5.

1. Click, with the right mouse button, on the row name 5.
2. Select Insert (and ignore the Insert Options smart tag).

Try inserting an extra column before column A.

1. Click, with the right mouse button, on the column name A.
2. Select Insert.

To delete a row or column, right-click on its name and select Delete from the menu which appears.

Spell-checking

Excel allows you to check the spelling of your work. You have probably already used this technique in Word.

1. Select a cell near the top of your worksheet.
2. Click on the Spelling button on the toolbar (or select Spelling from the Tools menu).

If a spelling mistake is found (or something that Excel thinks is a mistake but isn’t), you will be given the opportunity to correct or ignore it.

Excel will object to Twiga Secondary School. If you wish, change it to Twiga Secondary School by editing the text in the Not in Dictionary box and clicking on the Change All button.

When the end of the sheet is reached, the checking can be continued from the beginning of the sheet down to the cell you selected.

Saving your workbook

Your workbook can now be stored in a file on a floppy disk (the A: drive), flash drive, CD, or on the Networked PC service (the J: drive).

1. From the File menu select Save As (or Save).

A Save As window appears.

Choose a name for your file

1. In the File name: box, type a suitable name (such as Twiga Secondary School) for your file. Later, Excel will add the .xls extension automatically.

Excel file names can have up to 218 characters including alphanumeric characters, spaces, and special characters except for ⁄, >, <, *, ?, “, |, and :. Remember that Excel does not distinguish between upper and lower case letters.

Decide which drive to use

If you are using the Networked PC service, Excel will offer to save your file on your J: drive.

If you want to save the file to another drive (such as A:),

1. Click on the down arrow to the right of the Save in: box.
2. Select the drive of your choice, e.g., Flash disk (F:).

Note: On the Networked PC service, a quicker way of selecting the A: drive is to click the Flash Disk icon at the left-hand edge of the Save As window.

Decide which folder to use

If you want to choose a folder in which to save your file:

1. Double-click on a folder already displayed in the list box or click the Up One Level button (to move up one folder level) and navigate until you can select the folder you want.

On your own PC, you can click on the History button (at the left-hand edge of the Save As window) to see documents and folders you have worked with recently.

Different file format

It is possible to save the file in a different file format by changing the setting in the Save as type: box.

Finally

1. Click on the Save button.

After you save the file, the workbook window remains open and Excel displays the new name in the title bar.

Note: Once a file has been saved for the first time, subsequent changes can be saved by selecting Save from the File menu, or by clicking the Save button on the standard toolbar.

Closing your workbook

You can close your workbook at any time.

1. From the File menu, select Close.

If you have made any changes to the workbook since it was last saved, you will be asked whether you wish to save those changes.

2. Click Yes to keep the changes or No to discard them (and leave the workbook as it was when you last saved it).

Note: To open the workbook later, select Open from the File menu, navigate to the location of the file, select it, and click OK. You can have several workbooks open at the same time.

Calculations

You can now try doing some simple calculations using a worksheet that was prepared earlier.

If you are not using the ITS Networked PC service, you may like to get a copy of this file from the ITS Web pages before working through the rest of the document.

• The file is called Twiga Secondary School.xls and can be found in the Information/Guides section of the ITS website.

http://www.dur.ac.uk./its/info/guides/files/excell/

Opening a worksheet

1. From the File menu select Open.
2. Change the Look in: setting to work file on ‘Dudley’ (T:).
3. In the box of folders, double-click on its, and then on Excel.
4. Click on Twiga Secondary.xls.
5. Click on Open.

Entering formulae

All formulae begin with an equals (=) sign. If you forget to type the =, the rest of the line could be entered into the cell as a piece of text. Formulae can contain values, cell addresses, mathematical operators, and functions.

Some of the mathematical operators that can be used are:

They are listed here in decreasing order of priority starting with Percent, which has the highest priority (done first), and ending with comparison, which has the lowest (done last). If a formula contains operators with the same priority, they are evaluated from left to right.

If you want to alter the order of evaluation, use parentheses (brackets) to group expressions. Any parts of a formula that are in parentheses are done first. For example:

7 + 3/2 will be evaluated as 8.5 (not 5.0).

(7 + 3)/2 will be evaluated as 5.0.

Entering a formula for total per person

In this case, it is the marks of Maths, Geography, Chemistry, and Biology.

1. Click in G5.
2. Type =C5 + D5 + E5 + F5 or =SUM(C5:F5).
3. Press the Enter key.

Note that you have entered the cell references in the formula, not the values in the cells since those particular values might be changed later.

Next, copy the formula to cells G6-G13. This will display the total marks for each student of Form 1 in Twiga Secondary for the four (4) subjects.

Entering a formula for Average

Now calculate the average marks for each student in Form 1 for the four subjects.

1. Click in H5.
2. Type =(C5 + D5 + E5 + F5)/4 or type =G5/4 or =AVERAGE(C5:F5).
3. Press the Enter key. The average marks will be displayed.

Copying a formula to another cell

A formula can be copied to another cell in the same way as text or numbers. In this case, from H5 to H13.

Sorting

If you have a rectangular block of data surrounded by blank cells, then Excel recognizes this as a list. Data in a list can be sorted alphabetically, numerically, or chronologically. Excel rearranges the rows according to the contents of one or more columns.

If you select a single cell in the list, Excel will automatically select the whole list for you. If there are labels in the first row, Excel excludes them from the sort. Alternatively, you can select the area of data that is to be sorted. Be careful though, sorting data selected by you does not move any non-selected data in adjacent columns or rows.

On the summer worksheet of the Charity_Barbecue workbook, you have a list in cells B13:E19 (If you added extra people, the range will be different). It can easily be sorted by surname.

1. Click on the summer tab.
2. Click on any name in the Party column.
3. Click the Sort Ascending button on the toolbar.

The list will be sorted in ascending order by name.

4. Immediately click the Undo button if you want to restore the list to its original order.

The Sort Descending button can be used to sort a list in descending order.

If you wish to sort by more than one column, select Sort from the Data menu.

For further information about lists, including sorting, see Guide 36: Lists and data management in Microsoft Excel 2003.

Printing

There are various stages to getting your information printed.

Page Setup

1. From the File menu, select Page Setup.

By changing the options in the Page Setup window, you can control the appearance of your printed sheets. This includes choice of margins, headers/footers, page orientation (portrait/landscape), scaling, and precisely what will be printed (grid lines, for example).

2. Click on OK in the Page Setup window.

Print preview

1. From the File menu, select Print Preview (or click on the Print Preview button on the toolbar).

In this mode you can, if you wish, view the next/previous page, zoom, use a magnifying glass, adjust margins, and change the page breaks.

2. Click the close button to return to your worksheet.

Dotted lines will now be displayed on your worksheet indicating page boundaries.

Selecting a printer and printing your work

1. From the File menu, select Print.
2. If you are working on the Networked PC service, by default, the printout should be directed to the printer in the room in which you are working. Check that this is the case.

If you want a different printer:

3. Click on the box beside the Printer Name.
4. Select the printer you want to use.

You can specify what is to be printed, which pages are to be printed, and (to print your work, click on OK).

GRAPHS AND CHARTS

Data may be presented in graphs or charts as well.

From the table below:

To change the colour, click on the bar you want to change the colour.

If you want to change the chart type, click on the chart and insert, select the type of chart.

PAGE PROPERTIES AND PRINTING

Includes:

• PAGE SETUP

It may include:

• a) Header footer setup
• b) Selecting a printer
• c) Paper layout (page layout)

Procedures:

From the menu bar, click File then select Page Setup.

i. Page tab

• On the page tab you may choose either portrait or landscape for the page layout.
• Select the page size as A4.

ii. Header/footer

• Click header/footer tab.
• Click custom header.
• There are three sections here; you may type your header in any section depending on what type of paper you want your footer/header to appear. But in most cases, the left section is preferred for header.
• After typing your header click OK.
• Then click on custom footer.

The footer may be either in the center section or right section.

• Click on the center section.
• Click the button shown as #; the word and page will disappear in the centre section also. Date and time may be set in the footer right section. After completion of footer and header setup click OK.

PAGE PREVIEW

From the Menu bar click File.

Then select Print Preview.

Note: Your page may default to two pages while the document is for one page only.

Procedures:

Click Page Break Preview.

Click OK to the window appearing.

If the document is making up two pages, there must be dotted lines separating those pages. Place your cursor on the dotted line and drag it to the right until you meet the solid line.

WORKING WITH MS EXCEL WHILE YOU ARE IN MS WORD

Select MS Excel from the toolbar, work with your data.

THE FOLLOWING IS SUMMARY OF PROCUREMENT

To shift/Return to MS Excel double click the Table.

EXERCISE

From the given data below,

1. Find total, average, and grades.
2. Draw bar graph, pie chart.
3. Save file name with your name.
4. Put the file in folder, save in desktop and My Documents.

Formulating the formula

Note: The number of opening brackets must be the same as closing brackets.

Example: a student whose average is equal or greater than 80 is placed in A grade, average equal or greater than 70 is grade B, average equal or greater than 50 is in C grade, otherwise F grade.

FORMULA

=IF(F2>=80,”A”,IF(F2>=60,”B”,IF(F2>=50,”C”,”F”)))

EXERCISE

Banda Enterprises had the following data.

FORMULA:

Discount = Basic price * Discount

Basic Price

VAT = Sale Price * VAT

Selling price = Sale Price + VAT

Question

1. Present your information in Chart form (column chart).
2. Cut the graph and copy it on a separate worksheet and rename worksheet as chart.
3. Create header and footer.
4. “Excel test” as header (left section).
5. “Your name, Date and time” as footer (left section).
6. Save the work in your name and put it in the folder “on the desktop (My work)”.

1 CHART

Answer 2

Practical work:

Manipulation:

This includes:

• I. Mathematical operation (+, -, ×, and ÷)
• II. Sum
• III. Average
• IV. Count
• V. Minimum
• VI. Maximum
• VII. Logical formulae etc.

When using functions, remember the following:

• Use an equal sign to begin a formula.
• Specify the function name.
• Enclose arguments within parentheses.
• Use a comma to separate the arguments.

SUM

The sum function is used to calculate the sums. For example:

=SUM(2, 13, 10, 65)

In this function:

• The equal sign begins the function.
• SUM is the name of the function.
• 2, 13, 10, and 65 are the arguments.
• Parentheses enclose the arguments.
• Comma separates each of the arguments.

Another way of using the sum function:

=SUM(A1:A5)

AVERAGE

The average function is used to calculate an average from a series of numbers.

Min (Minimum)

You can use the Min function to find the lowest number in a series of numbers.

Max (Maximum)

You can use the Max function to find the highest number in a series of numbers.

Count

The count function is used to get the number of entries in a number field that’s in a range.

If (logical functions)

This is a conditional formula in a logical manner (true or false). You can use the “if” function to group or grade the marks/scores/data in a series of numbers.

Example 1

The following are the Form Three students’ scores in a monthly test.

Represent these marks using:

• a) Column chart
• b) Line chart
• c) Pie chart

Column chart

Line chart

Pie chart