MS Excel Lesson 7

Lesson plan – Microsoft Excel

Class 7

Objectives:

• Use simple formulas

1)      Begin by explaining the principles of formulas, using a whiteboard or flipchart or whatever you have handy. A formula has 4 basic components:

a)      Equals: all formulas start with =

b)      Constants: simple numbers

c)      Addresses: a letter (column) and a number (line). An address makes a reference to a cell. Show some examples, but it will become clearer in a few minutes

d)      Operators: mathematical symbols to use in calculations. We will use the basic six: + - * / % ^

2)      Now, do some examples of basic formulas in Excel, using simple arithmetic and constant numbers (no addresses yet):

a)      =5+5

b)      =15-10

c)      =2*4

d)      =12/4

i)        Make a very important observation: what is shown in the spreadsheet cell is different than the actual content. For instance, if you highlight the first cell, it shows 10 in the cell, because that is the result of the formula. However, up on the formula bar, it still shows =5+5, because that is the actual content of the cell.

3)      Have the class practice by doing several formulas with only operators and constants, as above.

4)      Now let’s incorporate addresses. Make two columns of simple numbers (use the same constants as above). In a 3^rd column, we’ll put some formulas. Use all 4 basic arithmetical formulas, one for each pair of numbers. Proceed thus:

a)      Start with =, as always

b)      Write the address of the first cell (NOT the number in the cell)

c)      Put in the operator

d)      Write the address of the second cell

e)      Press enter to finish

i)        When you are done, explain what the formula means. If the formula was =A1+B1, it means that Excel goes to cell A1, takes the value there, goes the cell B1, gets that value, adds the two together and shows the result.

5)      Have the class replicate what you just did, with the same numbers and formulas.

6)      Now we can make it a little bit easier by using mouse clicks instead of typing in cell addresses. Erase your previous formulas. Starting with the first one, demonstrate how you can click on a cell to automatically insert its address. In this way, the above steps appear more like this:

a)      Start with =, as always

b)      Click the first cell

c)      Put in the operator

d)      Click the second cell

i)        In this way, you can save some time and make entering formulas a little bit easier, by avoiding looking for the cell reference each time.

7)      So why is this so great? Once the class is done practicing for a moment, briefly demonstrate the versatility of Excel. Change one of the numbers from (4) above, and show how the results of the formula automatically update to reflect the change. For instance, if you change one of the 5s in the first row to a 20, the result of the formula will update to show 25 instead of 10.

Hand out Exercise 7 to practice. They must use formulas to fill in the blank column and rows. Have some patience here. Many students have little to no basic math and will need to have it explained to them exactly what formulas they should use. Addition? Multiplication?