Mini-Lesson on Microsoft Excel Formulas
A mini-lesson on how to use Microsoft Excel’s formula functions.
Barefoot
Technologist
Train the Trainers Program
Mini-Lesson: Microsoft Excel Formulas
Mini-lesson goal: to begin to learn how to use Excel’s powerful formula and calculation functions.
Examples: Budgets
Best format for this lesson: one on one or small group
Materials needed: at least one computer for each pair of participants, and one copy of this mini-lesson for each participant. An LCD projector for a larger group will be important.
Trainer’s notes are in BOLD ITALIC, and you’ll notice this8 icon before any notes to trainers in the text.
There are basically 3 different kinds of information that will go into a cell in an excel spreadsheet: text, numbers, and formulas. 8 If the learner is new to Excel, a brief review of what a “cell” is would be useful here.
Select some cells with numbers in them. Go to “Format” and choose “Cells…”
This first tab, the “Number” tab, defines how this number will be formatted.
There are quite a number of options available. How would you format the “monthly income” column so that it was clear the number was in dollars?
The other tabs determine other types of formatting – “Font” is fairly straightforward; “Alignment” is very useful; “Border” and “Patterns” are basically the same as “Borders and Shading” in Word; “Protection” allows you to control whether or not others can see the selected cells, or change them.
A formula starts with an ‘=’ in front. Always. That’s how excel knows it’s a formula.
8 A common mistake is forgetting the ‘=’
Try this:
What’s the result?
Now, use the “Clients1.xls” spreadsheet.
In cell E13, type in just “=”. What happens? That small box up at the top goes from:
to:
The formula box comes up, with a number of formulas you can use. Choose “SUM”, by clicking on the bar. What happens?
Select the cells E2 through E10 (how?). This is what you should now see:
This is the sum of the grants given.
(At this point you might want to change the format of the cells in this column so that they look like dollars.)
Try adding together two different cells. What about using “Autofill” to make that same calculation apply for more than one cell?
8 A review of autofill might be useful here, as well as a reminder about how the shape of the cursor can indicate whether an autofill action will happen, or the contents of the cell will be moved. In addition, the little yellow box that pops up to the right of the cells can indicate what will get filled in.
While the formula box is up, choose “More Functions…” Look at the list. Use this dialog box to find 3 functions that you think might be useful to you.
You can use the formula box to choose a function, or simply type the function in yourself. Usually, however, choosing functions from the box means that you will receive more guidance in the use of the functions.
How would you find out what the total number of grants was for individuals who had children? Here’s how:
First – you need a new column – basically which has the grant number, depending on whether or not someone has a child. How to do that? Look at the “IF” function. The format looks like this:
=IF([logical_test],[value_if_true],[value_if_false])
So, if the logical test is true (does this person have children?) then make the value_if_true the amount of the grant, and the value_if_false zero. How do you determine whether they have children? Well, if the number in the children column is not equal to zero.
So here’s the formula: =IF(A4<>0,E4,0) – if A4 does not equal (<>
zero, then the value is E4, or the amount of the grant, otherwise it is zero.
Use autofill to make this formula for all of the cells down the row (from 2 to 10), then use a SUM at, say, E13, to add everything up.
As always, There Is More Than One Way To Do It (TIMTOWTDI) – you can use the “SUMIF” function.
So what’s the difference between a formula and a function? I tend to use them interchangeably, but really a formula looks like: =A3+A5 or =D4*(A12-A13)
Where a function looks like: =SUM(A12:A45) or =IF(A4<>0,E4,0) – these are pre-defined ways of calculating data.
Statistical measurements use these pre-determined functions.
Technologist
Train the Trainers Program
Mini-Lesson: Microsoft Excel Formulas
Mini-lesson goal: to begin to learn how to use Excel’s powerful formula and calculation functions.
Examples: Budgets
Best format for this lesson: one on one or small group
Materials needed: at least one computer for each pair of participants, and one copy of this mini-lesson for each participant. An LCD projector for a larger group will be important.
Trainer’s notes are in BOLD ITALIC, and you’ll notice this8 icon before any notes to trainers in the text.
There are basically 3 different kinds of information that will go into a cell in an excel spreadsheet: text, numbers, and formulas. 8 If the learner is new to Excel, a brief review of what a “cell” is would be useful here.
Select some cells with numbers in them. Go to “Format” and choose “Cells…”
This first tab, the “Number” tab, defines how this number will be formatted.
There are quite a number of options available. How would you format the “monthly income” column so that it was clear the number was in dollars?
The other tabs determine other types of formatting – “Font” is fairly straightforward; “Alignment” is very useful; “Border” and “Patterns” are basically the same as “Borders and Shading” in Word; “Protection” allows you to control whether or not others can see the selected cells, or change them.
A formula starts with an ‘=’ in front. Always. That’s how excel knows it’s a formula.
8 A common mistake is forgetting the ‘=’
Try this:
What’s the result?
Now, use the “Clients1.xls” spreadsheet.
In cell E13, type in just “=”. What happens? That small box up at the top goes from:
to:
The formula box comes up, with a number of formulas you can use. Choose “SUM”, by clicking on the bar. What happens?
Select the cells E2 through E10 (how?). This is what you should now see:
This is the sum of the grants given.
(At this point you might want to change the format of the cells in this column so that they look like dollars.)
Try adding together two different cells. What about using “Autofill” to make that same calculation apply for more than one cell?
8 A review of autofill might be useful here, as well as a reminder about how the shape of the cursor can indicate whether an autofill action will happen, or the contents of the cell will be moved. In addition, the little yellow box that pops up to the right of the cells can indicate what will get filled in.
While the formula box is up, choose “More Functions…” Look at the list. Use this dialog box to find 3 functions that you think might be useful to you.
You can use the formula box to choose a function, or simply type the function in yourself. Usually, however, choosing functions from the box means that you will receive more guidance in the use of the functions.
How would you find out what the total number of grants was for individuals who had children? Here’s how:
First – you need a new column – basically which has the grant number, depending on whether or not someone has a child. How to do that? Look at the “IF” function. The format looks like this:
=IF([logical_test],[value_if_true],[value_if_false])
So, if the logical test is true (does this person have children?) then make the value_if_true the amount of the grant, and the value_if_false zero. How do you determine whether they have children? Well, if the number in the children column is not equal to zero.
So here’s the formula: =IF(A4<>0,E4,0) – if A4 does not equal (<>
zero, then the value is E4, or the amount of the grant, otherwise it is zero. Use autofill to make this formula for all of the cells down the row (from 2 to 10), then use a SUM at, say, E13, to add everything up.
As always, There Is More Than One Way To Do It (TIMTOWTDI) – you can use the “SUMIF” function.
So what’s the difference between a formula and a function? I tend to use them interchangeably, but really a formula looks like: =A3+A5 or =D4*(A12-A13)
Where a function looks like: =SUM(A12:A45) or =IF(A4<>0,E4,0) – these are pre-defined ways of calculating data.
Statistical measurements use these pre-determined functions.