TEAM WORK SCHOOL - INFOTECH 100



Lesson Plan / With notes

Grade: 10

Subject: Information Technology

Duration: 2 x 35 min session

Topic: Spread Sheet

Sub topic: Microsoft Excel

Specific Objectives

At the end of this lesson students should be able to:

1. Define a spread sheet;

2. State what is meant by row, column, cell, label value, formula and range;

3. Navigate to specified locations within a spreadsheet;

4. Create a simple worksheet from given instructions;

5. Use the copy and paste option

6. Format data to accept currency values and decimal places

7. Adjust column width and row height;

8. Inserting and using formulas;

9. Identify and use relative and absolute cell addressing;

10. Sort a range of cells using different criteria;

11. Creating graphs and charts

12. Filtering the worksheet

Procedures

1. The term spread sheets will be defined

Spread sheet package

This is an application package that enables you to carry out numerical work easily and flexibly.

A spreadsheet enables you to store not only numerical data but also formulae to carry out operations on the numerical data. These formulae are applied to the data in the sheet whenever you make a change to it. So if you change any data values the entire worksheet is automatically re-calculated, updating other numbers as necessary.

2. Students will be engaged in a discussion on the wide usage of spreadsheets in various spheres.

What are spread sheets used for?

a. Loan calculations

b. Financial plans

c. Stock keeping

d. Payrolls

e. Company accounts

f. Keeping accounts in a club

g. Preparing end of term school reports

h. Statistics e.g. Finding averages or calculating standard deviation

3. The teacher will use a multimedia projector to display the contents of the Microsoft Excel window and the various menus, buttons and other characteristic elements in the excel window will be explained.

4. The teacher will write and explain some basic terms that are features of Microsoft Excel.

Key terms

A Cell

This is an intersection of a row and a column.

Cell address or cell reference

Each cell in a spreadsheet is identified by a cell reference. E.g. B3, D1

Active Cell

This is the cell in which any information entered from the keyboard will be stored.

A Label

This can be used as a title or heading to describe an aspect of the worksheet. A label can consist mainly of letters but sometimes numbers. A label cannot be used in calculations.

A Value

This is a piece of data (number) that can be used in calculations.

A formula

This is an instruction to perform certain operations on values. A formula in Microsoft Excel must start with an equal sign (=).

The formula bar

This displays the contents of the active cell, if any.

Workbook window

This is window occupies the majority of the screen and displays the Microsoft Excel work sheets.

Worksheet tab

These identify the current sheets in a workbook and allow you move from one work sheet to another, work sheets are therefore subparts of the workbook.

A Range

This is a cell or a group of adjoining cells that treated as a single unit. E.g. B3:E3

Text wrapping

This is feature that allows which is more than one word and which exceeds the width of a cell to move to another line in the same cell.

5. Students will then be showed various techniques that can be used to navigate through Microsoft excel.

.

|Mouse click |click any cell with your mouse to enter data |

|Cursor keys (arrow keys) on the keyboard |Use these to move in the direction of the arrows one cell at|

| |a time. |

|Enter |Moves one cell down |

|Tab |Moves one cell to the right |

|CTRL + HOME |Moves back to cell A1 |

|HOME | Moves to the first cell in a row |

|CTRL + END |Moves to the last cell in the worksheet |

|CTRL + PgDown |Moves to the next worksheet in the workbook |

|CTRL + PgUP |Moves to the previous worksheet in the workbook |

|F5 |To a designated Cell |

6. Students will then be taught how to use some formatting features of Excel such as

a. Changing the alignment of a cell or range

b. Changing the orientation of a cell or range.

c. Text wrapping

Aligning a cell or range

a. Select the cell or range of cells

b. Pull down the format menu and select Cells.

c. Select the Alignment tab.

d. Select the required alignment (e.g. left, centre, right) from the horizontal combo box.

Changing the orientation of a cell or range

a. Select the cell or range of cells

b. Pull down the format menu and select Cells.

c. Select the Alignment tab.

d. Click on the dots in the orientation pane to get the number of degrees you want the data angled at.

Text wrapping

a. Select the cell or range of cells

b. Pull down the format menu and select Cells.

c. Select the Alignment tab.

d. Click the wrap text box in the text control pane.

e. Click ok

7. A student in the class will be asked to create a small data which includes labels and values. This activity will be visible from the multimedia projector. From this activity students will fulfill objective 5 - 8 of this lesson plan.

Cut, copy or paste commands

a. Select the cell(s) containing the data you wish to copy or move

b. Select the cut or Copy command (from the edit menu or a shortcut from the toolbar)

c. Click in the cell where you wish to copy or move the data to.

d. Either press the paste command or press enter

Formatting numbers

Numbers can be represented in the following formats

• General: if you want no specific number format

• Number: used for general display of numbers (using decimal places and negatives)

• Currency: used to represent monetary values (e.g. $589.54 )

• Accounting: for lining up currency symbols and decimals points.

To format a worksheet for currency data

a. Select the cell or range of cells

b. Pull down the format menu and select Cells.

c. Select the Number tab, and then select Currency from the display list of the categories.

d. Choose the number of decimal places and the currency symbol you want to use.

e. Click ok

8. Students at this point will be introduced to the use of formulae within the spreadsheet.

Formulae

A formula is used to perform calculations using data contained in the cells and to display the results. Some of the features of formulae are:

a. A formulae is entered into the cell in which the result is to be displayed

b. A formula must begin with an equal sign (=)

c. A formulae includes arguments(such as cell references, text or numbers) and operators

i. Addition +

ii. Subtraction -

iii. Multiplication *

iv. Division /

v. Exponential ^ (raising to a power)

Using the diagram illustrated below.

Fig.1

[pic] (=B7^C7 in this case will result in 252)

The results will be displayed in any cell where you place the formulas.

Functions

A function is a predefined formula in EXCEL that can automatically calculate the results, perform worksheet actions or assist with decision making based on the information provided in the work sheet. Examples of functions (using the figure below) are the:

• SUM function Example =sum(B2:B5)

• AVERAGE function Example =average(B2:B5)

• MIN function Example = min(B2:B5)

• MAX function Example = max(B2:B5)

• COUNT function Example = count(B2:B5)

Fig.2

[pic]

a. Sum function

To add the figures that in the Income section there are several ways to do this.

Three of these methods are:

i. Add cells individually =B2+B3+B4+B5 (then press enter)

ii. Use the sum function =Sum(B2:B5) ((then press enter)

iii. Highlight the figures in the range B2:B6 (where B6 will be the cell where the answer will appear), then click on the auto sum button ([pic]) on the toolbar.

To add the items in the Expenses column then one of the three methods illustrated above can be used.

b. Average function

This is used to find the average of a set of values. The general form of this function is =average(first cell:last cell).

Using Fig.2 the formula to find the average expenses would be =average(B2:B5).

c. Max (maximum) function

This function is used to find the largest value in a set of values in a row or column.

Using Fig.2 the formula to find the average expenses would be =max(B2:B5).

d. Min (minimum) function

This function is used to find the smallest value in a set of values in a row or column.

e. Count function

This function states the number of entries that have been made in its argument list.

9. Students will be given activities which include each of these functions after each has been introduced.

10. Students at this point will be introduced to the fill handle and then by extension relative cell references and absolute references.

In fig 3 below if you are asked to add the figures below then display the results to the right you can use the formulas mentioned previously. This however can be a time consuming exercise especially if you are entering the cell names individually in the formula.

To simplify this process we can use the fill handle which is a square dot at the bottom right corner or an active cell. Below it is indicated by the arrow.

How does this work?

Once you have used a formula to obtain a result such as in D3 below click and hold the fill handle then drag it downwards. Excel will then apply the formula to the values below that are in the same relative position as the first values to which the formula was applied.

Fig3 Fig4

a[pic] b [pic]

a. This cannot work like this if you wish to multiply a long list of values by one value located in one cell. For example if you wish to find the local equivalent of a group of values.

Fig5 [pic]

b. To do this you would have to make the cell in the B column an absolute value. This can be done by placing a dollar sign before and after the column name of the cell. E.g. $B$2.

As illustrated in Fig6 below the cell will be applied to the values within the formula whenever you use the fill handle.

Fig6 [pic]

11. The following Errors messages may be seen from time to time to indicate that there needs to be a change in your operation.

[pic]

12. Students will then be introduced to the concept of sorting.

Sorting

This means to arrange in order. Sorting can be done using text as well values. Items can be sorted from highest to lowest or vice versa.

To sort a column using a single criteria

i. Select the cells in the column which you wish to sort.

ii. Pull down the Data menu and select Sort.

iii. In the ‘sort by’ box, select the primary column by which you want your data stored.

iv. Check the ascending or descending button.

v. Click OK.

13. The teacher will then involve the students in the creation of charts from data that is present within the table.

Steps:

i. Select the range (example in figure 2 above), A9 to B12

ii. Go to the Insert menu

[pic]

iii. Select the chart types

iv. Go to the Design tab / Switch Row/Column swaps data between the axes if required. The Select Data icon allows you to change the source of the data and name the legend entries.

v. Chart options: clicking on Chart Tools Layout gives many options to change the features of the chart.

14. Students will afterwards be introduced to filtering.

Filtering Records

Filtering a worksheet displays records that contain a certain value or that meet a set of criteria. The two methods used for filtering are advanced filter and auto filter.

Auto filter – this allows the selection of records based on one criterion.

Advanced filter -- this allows the selection of records based on more than one criterion

Advanced filter

Steps:

1. Select the range to be filtered (inclusive of the heading for the table)

2. Right click the Selection

3. Select filter

4. Click filter by selected cell’s value

5. Choose the criteria on which you wish to filter the table from the headings of the table

15. Students will be given the information in writing.

Teaching and learning Materials

Multimedia projector

Log on IT text book

The computer

Activities

1. Students should complete the following table.

a. In addition students should insert a formula to display

i. Maximum profit after tax

ii. Minimum expense

iii. Average profit

[pic]

2. Students should use the absolute cell reference to do the exercise below. [pic]

3. Students should copy and complete the following.

a. After students are requires to copy and paste the page to another worksheet.

b. Sort worksheet1 according first name

c. Sort worksheet2 according last name

d. Insert a formula to display the number of transactions that have been made. [pic]

Students are required to enter editing features to the illustration below. Such as

a. Centre the numbers

b. Display the numbers as currency

c. Use two decimal places to represent the currency

[pic]

Evaluation

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download