#### Spread Sheets - Computer Studies Form 2

**Background**

For along time bursars and account clerks have been using ledger books to store numerical data for accounting work. Similarly, in schools teachers use mark books for entering students scores in various subjects. In each case, data is entered manually. Incase of changes one has to cancel, rub, use whiteout or remove the paper and redo the work.

**Background**

For along time bursars and accounts clerks have been using ledger books to store numerical data for accounting work. Similarly in schools teachers use mark books for entering students scores in various subjects. In each case, data is entered manually. Incase of changes one has to cancel, rub, use whiteout or remove the paper and redo the work. This leads to untidy, tedious and time wasting task. With the advent of electronic spreadsheets, problems associated with data entry, modification, and manipulation has been solved. The provision of tools in electronic spreadsheet have made it easier to perform tasks previously carried out manually.

**Objectives**

**By the end of the lesson, you should be able to:**

- Define a spread sheet
- Describe the components of a spreadsheet
- State the application areas of a spreadsheet
- Create and edit a worksheet
- Explain different cell data types
- Apply cell referencing
- Apply data management skills
- Apply charting and graphing skills

Print worksheet and graph.

Introduction

**Components of a Spreadsheet **

Nearly every part of the spreadsheet has its own term and it is quite useful to know your spreadsheet vocabulary to make using Excel easier. There are three basic parts of an Excel Spreadsheet: The worksheet, databases and graphs.

1. Columns - The vertical segments that you see on the spreadsheet are called columns. (P). The illustration below shows column 'A'. Excel uses letters to represent columns. (P)

2. Rows -(P)The horizontal segments, that run left-to-right, are referred to as rows. The illustration below shows row 1.(P)Excel uses numbers to represent rows.(P)

3. Cell - The intersection between a row and column is referred to as a cell. (P) The illustration below shows a cell.

4. Worksheet- comprises rows and columns(P)

5. Database- a container for related data contained in a worksheet(P)

6. Graph- A pictorial representation of a worksheet (P)

Excel Cells

VO2

The first cell, where column A and row 1 intersect is referred to as A1.

Here's a picture of Cell A1:

**Formatting a Cell**

**Drag and Drop **

In excel, drag and drop is achieved by:

1. Selecting the cell(s) to be moved.

2. To select an individual cell, click that cell.

3. To select multiple contiguous cells, click and drag across the desired cells.

4. Point to and click the heavy border surrounding the cell(s).

5. The mouse pointer changes to a four-headed arrow.

6. Hold the mouse button; drag the cells to the new location

7. Drop the cells and release the mouse button.

Note: If information already exists at the new location, a dialog box will appear asking if you want to replace the information. Respond accordingly.

Cut and Paste

When using Cut and Paste, double check formulas to ensure that cell references are properly updated.

1. Select the cell(s) to be moved HINTS: To select an individual cell, click that cell.To select multiple contiguous cells, click and drag across the desired cells. 2. From the Edit menu, select Cut OR On the Standard toolbar, click CUT A moving border appears around your selection.

3. Select the cell where you want the cell(s) to be pasted

4. From the Edit menu, select Paste OR On the Standard toolbar, click PASTE

**Cut and Paste **

Cut and Paste

When using Cut and Paste, double check formulas to ensure that cell references are properly updated.

1. Select the cell(s) to be moved HINTS: To select an individual cell, click that cell.To select multiple contiguous cells, click and drag across the desired cells. 2. From the Edit menu, select Cut OR On the Standard toolbar, click CUT A moving border appears around your selection.

3. Select the cell where you want the cell(s) to be pasted

4. From the Edit menu, select Paste OR On the Standard toolbar, click PASTE

Internet Worms

Internet worms will scan through all available network resources using local operating system services and scans the Internet for unprotected machines. They attempt to connect to these machines in order to gain full access to them.

A Story

Here is another story about a Computer error linked to horrific Qantas jet plunge

October 08, 2008 10:18am

A QANTAS aircraft flying from Singapore to Perth shot up 300 feet before pitching earthward after signaling to its pilots "irregularities" in its elevator control system.

The "ghost in the machine'' malfunction which caused a mid-air drama leaving 46 people injured has puzzled air safety investigators who cannot recall a similar incident in aviation history.

Australian Transport Safety Bureau (ATSB) director of aviation safety investigation Julian Walsh said there was no doubt the Airbus A 330-300, traveling at 37,000 feet, had briefly taken control of itself.

There are other numerous reported incidences due to computer errors and accidents.

Errors and accidents in computer systems may be classified as;

Human errors

Procedural errors

Software errors

Electromechanical problems

Dirty data

Backup

Backup refers to making copies of data so that these additional copies may be used to restore the original after a data loss event. These additional copies are typically called "backups." Backups are useful primarily for two purposes. The first is to restore a state following a disaster (called disaster recovery). The second is to restore small numbers of files after they have been accidentally deleted or corrupted.

Functions of a Spreadsheet

**Objectives**

By the end of the lesson, you should be able to; (i) Utilize basic mathematical operators

(ii) Apply basic mathematical formulae

**Multiplication **

- Hold down the Ctrl key while you press "g" (Ctrl+g).(P) The Go To dialog box appears.(P)
- Type C1 in the Reference field.(P)
- Press Enter(P) Excel moves to cell C1(P)
- Type Multiply(P)
- Press Enter.(P) Excel moves down one cell.(P)
- Type 2 in cell C2.(P)
- Press Enter. Excel moves down one cell(P)
- Type 3 in cell C3.(P)
- Press Enter(P). Excel moves down one cell(P)
- Type =C2*C3 in cell C4(P)
- Click the check mark on the Formula bar.(P) Excel multiplies C1 by cell C2(P) and displays the result in cell C3(P). The formula displays on the Formula bar(P)

Introducing Basic Functions and Formulae

Spreadsheets are used to perform basic mathematical functions for example automatically adding, multiplying and dividing. They can also be used to carry out statistical functions like finding the average, the largest and minimum value in a set of values, counting the number of cells that contain values within a range and even determining the rank of a number in a list by comparing its size relative to others.

A mathematical operator is the symbol or sign that represents an arithmetic operation in an Excel spreadsheet formula. The mathematical operators used in Excel formulas are similar to the ones we use in our maths class. These are: Subtraction - minus sign ( - ) Addition - plus sign ( + ) Division - forward slash

Multiplication - asterisk (x ) Exponentiation - caret (^ )

Definition

A mathematical operator is the symbol or sign that represents an arithmetic operation in an Excel spreadsheet formula. The mathematical operators used in Excel formulas are similar to the ones we use in our maths class. These are: Subtraction - minus sign ( - ) Addition - plus sign ( + ) Division - forward slash

Multiplication - asterisk (x ) Exponentiation - caret (^ )

**Addition**

1. Type Add in cell A1.(P)

2. Press Enter. Excel moves down one cell.(P)

3. Type 1 in cell A2.(P)

4. Press Enter. Excel moves down one cell.(P)

5. Type 1 in cell A3.(P)

6. Press Enter. Excel moves down one cell.(P)

7. Type =A2+A3 in cell A4.(P)

8. Click the check mark on the Formula bar(P). Excel adds cell A1 to cell A2 and displays the result in cell A4. (P)The formula displays on the Formula bar(P).

Note: Clicking the check mark on the Formula bar is similar to pressing Enter(P). Excel records your entry but does not move to the next cell.

Subtraction

1. Press F5. (P)The Go To dialog box appears.

2. Type B1 in the Reference field.(P)

3. Press Enter. Excel moves to cell B1.(P)

4. Type Subtract.(P)

5. Press Enter. Excel moves down one cell.(P)

6. Type 6 in cell B2.(P)

7. Press Enter. Excel moves down one cell.(P)

8. Type 3 in cell B3.(P)

9. Press Enter. Excel moves down one cell.(P)

10. Type =B2-B3 in cell B4.(P)

11. Click the check mark on the Formula bar. (P)Excel subtracts cell B3 from cell B2 and the result displays in cell B4.(P) The formula displays on the Formula bar.(P)

- Press F5. The Go To dialog box appears.
- Type B1 in the Reference field.
- Press Enter. Excel moves to cell B1.
- Type Subtract.
- Press Enter. Excel moves down one cell.
- Type 6 in cell B2.
- Press Enter. Excel moves down one cell.
- Type 3 in cell B3.
- Press Enter. Excel moves down one cell.
- Type =B2-B3 in cell B4.
- Click the check mark on the Formula bar. Excel subtracts cell B3 from cell B2 and the result displays in cell B4. The formula displays on the Formula bar.

Multiplication

1. Hold down the Ctrl key while you press (Ctrl+g). The Go To dialog box appears.

2. Type C1 in the Reference field.

3. Press Enter. Excel moves to cell C1

4. Type Multiply.

5. Press Enter. Excel moves down one cell.

6. Type 2 in cell C2.

7. Press Enter. Excel moves down one cell.

8. Type 3 in cell C3.

9. Press Enter. Excel moves down one cell.

10. Type =C2*C3 in cell C4.

11. Click the check mark on the Formula bar. Excel multiplies C1 by cell C2 and displays the result in cell C3. The formula displays on the Formula bar

Multiplication

1. Hold down the Ctrl key while you press (Ctrl+g). The Go To dialog box appears.

2. Type C1 in the Reference field.

3. Press Enter. Excel moves to cell C1

4. Type Multiply.

5. Press Enter. Excel moves down one cell.

6. Type 2 in cell C2.

7. Press Enter. Excel moves down one cell.

8. Type 3 in cell C3.

9. Press Enter. Excel moves down one cell.

10. Type =C2*C3 in cell C4.

11. Click the check mark on the Formula bar. Excel multiplies C1 by cell C2 and displays the result in cell C3. The formula displays on the Formula bar

Division

1. Press F5.

2. Type D1 in the Reference field.

3. Press Enter. Excel moves to cell D1.

4. Type Divide.

5. Press Enter. Excel moves down one cell.

6. Type 6 in cell D2.

7. Press Enter. Excel moves down one cell.

8. Type 3 in cell D3.

9. Press Enter. Excel moves down one cell.

10. Type =D2/D3 in cell D4.

11. Click the check mark on the Formula bar. Excel divides cell D2 by cell D3 and displays the result in cell D4. The formula displays on the Formula bar.

When creating formulas, you can reference cells and include numbers. All of the following formulas are valid:

=A2/B2

=A1+12-B3

=A2*B2+12

=24+53

Piracy

Computer piracy is the reproduction, distribution, and use of software without the permission of the owner of copy right

**Autosum**

You can use the AutoSum button on the Home tab to automatically add a column or row of numbers.(P) 1. Select column D1 and D2 2. Press the AutoSum button. Excel selects the numbers it thinks you want to add. 2. Click the check mark on the Formula bar or press the Enter ke. 3. Excel adds the numbers. If Excel's guess as to which numbers you want to add is wrong, you can select the cells you want.

**Illustrating autosum**

1. Go to cell F1.(P)

2. Type 3. (P)

3. Press Enter.(P) Excel moves down one cell.(P)

4. Type 3.(P)

5. Press Enter.(P) Excel moves down one cell.(P)

6. Type 3. (P)

7. Press Enter(P). Excel moves down one cell to cell F4.(P)

8. Choose the Home tab.(P)

9. Click the AutoSum button in the Editing group.(P) Excel selects cells F1 through F3 and enters a formula in cell F4.(P).

Automatic calculations

By default, Microsoft Excel recalculates the worksheet as you change cell entries. This makes it easy for you to correct mistakes and analyze a variety of scenarios.Make the changes described below and note how Microsoft Excel automatically recalculates. 1. Move to cell A2.(P)

2. Type 2.(P)

3. Press the right arrow key. Excel changes the result in cell A4. Excel adds cell A2 to cell A3 and the new result appears in cell A4.(P)

4. Move to cell B2.(P)

5. Type 8. (P)

6. Press the right arrow key. Excel subtracts cell B3 from cell B2 and the new result appears in cell B4.(P)

7. Move to cell C2.(P)

8. Type 4.(P)

9. Press the right arrow key. Excel multiplies cell C2 by cell C3 and the new result appears in cell C4.(P)

10. Move to cell D2.(P)

11. Type 12. (P)

12. Press the Enter key.(P) Excel divides cell D2 by cell D3 and the new result appears in cell D4.(P)

Product

A better way of using the product function is to type the numbers you are multiplying into cells on the spreadsheet and then enter those cell references (the address of the cells) into the function. For example, if we enter the numbers 235 and 546 into cells C1 and C2, we would write the function as:

=PRODUCT( C1:C2 )

The answer is 128,310. If the numbers ever change, you only need to change the numbers in cells C1 or C2 and the function automatically updates the answer. For example, if you find that the number in C1 wasn't 235 but 230, simply type 230 in cell C1 and the function updates the answer to 128,580.

Statistical functions

Average

Example Using Excel's AVERAGE Function:

The MAX function

##### Other Goodies for KCSE ONLINE Members*!*

Coursework **e-Content CD **covers all the topics for a particular class per year and costs **1200/-** ( Per Subject per Class ).

Purchase Online and have the CD sent to your nearest Parcel Service. Pay the amount to Patrick **0721806317** by **M-PESA** then provide your address for delivery of the Parcel.
Alternatively, you can use **BUY GOODS TILL NUMBER 827208 **Ask for clarification if you get stuck.

###### Install ADOBE Flash Player for Best Results

For Best results **INSTALL Adobe Flash Player** Version 16 to play the interactive content in your computer. Test the link below to find out if you have Adobe Flash in your computer.