Excel 2007 Overview Guide - Part 4 - Formula Bar, Home Menu, Quick Access Toolbar and the Status Bar

Uploaded by springsteen8591 on 03.10.2010

Transcript:

Hi, this is Millie! Welcome to my screencast about how to use Excel’s abilities for entering,

manipulating, and storing data. Formulas, functions, and their order of operations are

the basic data manipulation tools we will cover. The first step is to enter your data.

This might be class grades on various assignments if you are a teacher. Here, I have a simple

spreadsheet comparing temperatures in Celsius and Fahrenheit. Notice it’s not all filled

in yet! We’ll learn how to do that automatically. Keep the data organized with the columns and

rows; you can create headings, like “Fahrenheit” and “Celsius” (but don’t change the

actual column and row names—make the headings in the cells themselves). Now, let’s learn

about formulas first. Formulas are user-created strings of data connected by operations, resulting

in a final value. For instance, here, if we wanted to add up a certain set of Fahrenheit

values—say 35 to 39 degrees—we could do so. The syntax is as follows: simply type

in an equals sign, followed directly by the cell addresses, which are the alphanumerical

names of the cells (in this case A7 to A11, 35 to 39), and the operations that we want

to use (addition, subtraction, division, multiplication, exponents). Parentheses are also useful.

Check out Erin Cooke’s screencast for more information on cell addresses. Let’s pull

up our formula bar for future reference. Just go to view-->formula bar, and now we can see

the formulas used to get a cell value when we click on that cell. Looks like we already

had it up. Now let’s say we want to find Celsius values using the conversion formula

from Fahrenheit to Celsius. So we’ll type the formula (using the correct syntax) and

include the appropriate cell addresses or values. For example, to calculate the Celsius

equivalent of 32 degrees Fahrenheit, I will type the formula into the first Celsius cell,

B4, right here. You might remember this formula from science class: (F-32)*5/9. Instead of

“F,” we’ll write A4, because that’s the address of the value of 32 degrees Fahrenheit.

Let’s see what we get: zero degrees Celsius. That’s right! If we were to enter the formula

in the Celsius column wrong, we would get the wrong answer. For instance, what if we

leave out the parentheses? That’s not the right answer! We need to use the right syntax

to take advantage of the order of operations. The order of operations for formulas and functions

both in Excel and in the math world is PEMDAS, which is parentheses, exponents, multiplication,

division, addition, and subtraction. Excel will automatically perform formulas and functions

in this order. Now, if we edit the formula to have parentheses, we can use it correctly

again. We know that’s correct because 32F and 0C are both freezing. You might be wondering,

do we have to type this conversion formula to find every single Celsius value? No way!

Let’s use a shortcut, called the fill handle. This will enable us to fill in a whole lot

of data at once. Grab the lower right hand corner of this Celsius

cell (this little corner’s called the fill handle)—notice how your cursor changes shape,

from this…to this. Also notice that since we’ve highlighted this cell, we can see

the formula we used to get it in the formula builder up here. Now pull down to as many

cells as you want values for, which for this is a lot, all the way to boiling, which is

212 Fahrenheit, then let go. This is really neat because the cell addresses change relative

to the row when we fill down, and relative to the column when we fill across. We can

also check this in the formula bar. For instance, to find the Celsius equivalent for 52 degrees

Fahrenheit, which is right here, the conversion formula will use row 24 instead of row 4.

Now that we have covered the basics of formulas and operations, let’s learn how we can greatly

reduce our workload and the involvedness of using Excel’s operational capabilities.

To do this, we will use functions. Functions are built-in formulas that Excel already knows,

so we don’t have to type out a formula for them. We are going to learn four simple functions:

average, minimum, maximum, and sum. All follow the same function syntax: an equals sign directly

followed by the function name in ALL CAPS, directly followed by a summary, in parentheses,

of all the cell addresses being included in the function, usually with a colon. Now, for

the next part of this screencast, we will switch to a different spreadsheet that depicts

students’ grades on several tests. The first function we will examine is the AVERAGE function,

which adds up a set of cell values and divides by the number of cells. We’ll use the same

syntax as for formulas, but remember to include AVERAGE in between the equals sign and the

parenthesis. Let’s take the average of Billy’s four test grades. We’ll type in the “test

average” cell in Billy’s row, which is row 2, and use =AVERAGE(B2:E2). Close the

parentheses, hit enter, and there’s Billy’s test average. Notice that the letters change

this time, not the numbers, in the cell addresses (B2:E2)—we’re going across a row, not

down a column. Don’t want to do this for all 16 students? Use fill handle! Just grab

the corner of Billy’s test average and pull down. Let’s check a few kids and make sure

the fill handle worked. Brandon—the formula uses the tests in Brandon’s row, 8. Good.

We now have each student’s average for all four tests. Let’s find the class average

for each individual test. This time we’re going down the columns—for Test 1, we’ll

type =AVERAGE( in the “class average” row and highlight all 15 students’ grades.

Close parentheses and…there we go! The class average for test 1 is 84.625. Not too bad.

We’ll fill in the averages for the other three tests using fill handle and the major

four-test average at the end. We have a lot of information about students’ average grades

now. What if we want to know the highest and lowest grades, or the range, for each test

and for the test averages? We would use the Minimum and Maximum functions, which find

the lowest and highest values of a data set for us. Let’s find the class maximum for

Test 1. In B20, right here, we’ll enter =MAX(B2:B17). We can also highlight those

cells. Close parentheses, and hit enter. Check to make sure it’s right—yep, it looks

like Michelle had the highest score with a 98. Good job, Michelle. Drag fill handle across

to fill in the other test highs. We can do the same thing one row down for minimum test

grades.

One final function we haven’t discussed is the SUM function. This forms part of the

AVERAGE function. Let’s use a new spreadsheet—this one tells us about school fundraising sales.

If we want to find the total for kindergarten’s sales, we’ll go to B5, in this spreadsheet,

and use the usual syntax: =SUM( and then we’ll highlight the cells we want to total, these

three, and end with a parenthesis. $255 is the total sales for the kindergartners. Nice

job. Let’s make it easy with fill handle to find the totals for the other grades, just

like this…and then for the totals for each fundraiser. Look at H5. $4,138, the total

amount of money raised from sales in all grades, would be the same whether we dragged fill

handle across from the “grade totals” row (here)…or the “fundraiser totals”

column. Graphs can be a helpful way to view fundraising data like this—for more information,

see Paige’s video. Thanks for watching my screencast—hope you’ve learned a lot about

formulas, functions, and order of operations in Excel.

manipulating, and storing data. Formulas, functions, and their order of operations are

the basic data manipulation tools we will cover. The first step is to enter your data.

This might be class grades on various assignments if you are a teacher. Here, I have a simple

spreadsheet comparing temperatures in Celsius and Fahrenheit. Notice it’s not all filled

in yet! We’ll learn how to do that automatically. Keep the data organized with the columns and

rows; you can create headings, like “Fahrenheit” and “Celsius” (but don’t change the

actual column and row names—make the headings in the cells themselves). Now, let’s learn

about formulas first. Formulas are user-created strings of data connected by operations, resulting

in a final value. For instance, here, if we wanted to add up a certain set of Fahrenheit

values—say 35 to 39 degrees—we could do so. The syntax is as follows: simply type

in an equals sign, followed directly by the cell addresses, which are the alphanumerical

names of the cells (in this case A7 to A11, 35 to 39), and the operations that we want

to use (addition, subtraction, division, multiplication, exponents). Parentheses are also useful.

Check out Erin Cooke’s screencast for more information on cell addresses. Let’s pull

up our formula bar for future reference. Just go to view-->formula bar, and now we can see

the formulas used to get a cell value when we click on that cell. Looks like we already

had it up. Now let’s say we want to find Celsius values using the conversion formula

from Fahrenheit to Celsius. So we’ll type the formula (using the correct syntax) and

include the appropriate cell addresses or values. For example, to calculate the Celsius

equivalent of 32 degrees Fahrenheit, I will type the formula into the first Celsius cell,

B4, right here. You might remember this formula from science class: (F-32)*5/9. Instead of

“F,” we’ll write A4, because that’s the address of the value of 32 degrees Fahrenheit.

Let’s see what we get: zero degrees Celsius. That’s right! If we were to enter the formula

in the Celsius column wrong, we would get the wrong answer. For instance, what if we

leave out the parentheses? That’s not the right answer! We need to use the right syntax

to take advantage of the order of operations. The order of operations for formulas and functions

both in Excel and in the math world is PEMDAS, which is parentheses, exponents, multiplication,

division, addition, and subtraction. Excel will automatically perform formulas and functions

in this order. Now, if we edit the formula to have parentheses, we can use it correctly

again. We know that’s correct because 32F and 0C are both freezing. You might be wondering,

do we have to type this conversion formula to find every single Celsius value? No way!

Let’s use a shortcut, called the fill handle. This will enable us to fill in a whole lot

of data at once. Grab the lower right hand corner of this Celsius

cell (this little corner’s called the fill handle)—notice how your cursor changes shape,

from this…to this. Also notice that since we’ve highlighted this cell, we can see

the formula we used to get it in the formula builder up here. Now pull down to as many

cells as you want values for, which for this is a lot, all the way to boiling, which is

212 Fahrenheit, then let go. This is really neat because the cell addresses change relative

to the row when we fill down, and relative to the column when we fill across. We can

also check this in the formula bar. For instance, to find the Celsius equivalent for 52 degrees

Fahrenheit, which is right here, the conversion formula will use row 24 instead of row 4.

Now that we have covered the basics of formulas and operations, let’s learn how we can greatly

reduce our workload and the involvedness of using Excel’s operational capabilities.

To do this, we will use functions. Functions are built-in formulas that Excel already knows,

so we don’t have to type out a formula for them. We are going to learn four simple functions:

average, minimum, maximum, and sum. All follow the same function syntax: an equals sign directly

followed by the function name in ALL CAPS, directly followed by a summary, in parentheses,

of all the cell addresses being included in the function, usually with a colon. Now, for

the next part of this screencast, we will switch to a different spreadsheet that depicts

students’ grades on several tests. The first function we will examine is the AVERAGE function,

which adds up a set of cell values and divides by the number of cells. We’ll use the same

syntax as for formulas, but remember to include AVERAGE in between the equals sign and the

parenthesis. Let’s take the average of Billy’s four test grades. We’ll type in the “test

average” cell in Billy’s row, which is row 2, and use =AVERAGE(B2:E2). Close the

parentheses, hit enter, and there’s Billy’s test average. Notice that the letters change

this time, not the numbers, in the cell addresses (B2:E2)—we’re going across a row, not

down a column. Don’t want to do this for all 16 students? Use fill handle! Just grab

the corner of Billy’s test average and pull down. Let’s check a few kids and make sure

the fill handle worked. Brandon—the formula uses the tests in Brandon’s row, 8. Good.

We now have each student’s average for all four tests. Let’s find the class average

for each individual test. This time we’re going down the columns—for Test 1, we’ll

type =AVERAGE( in the “class average” row and highlight all 15 students’ grades.

Close parentheses and…there we go! The class average for test 1 is 84.625. Not too bad.

We’ll fill in the averages for the other three tests using fill handle and the major

four-test average at the end. We have a lot of information about students’ average grades

now. What if we want to know the highest and lowest grades, or the range, for each test

and for the test averages? We would use the Minimum and Maximum functions, which find

the lowest and highest values of a data set for us. Let’s find the class maximum for

Test 1. In B20, right here, we’ll enter =MAX(B2:B17). We can also highlight those

cells. Close parentheses, and hit enter. Check to make sure it’s right—yep, it looks

like Michelle had the highest score with a 98. Good job, Michelle. Drag fill handle across

to fill in the other test highs. We can do the same thing one row down for minimum test

grades.

One final function we haven’t discussed is the SUM function. This forms part of the

AVERAGE function. Let’s use a new spreadsheet—this one tells us about school fundraising sales.

If we want to find the total for kindergarten’s sales, we’ll go to B5, in this spreadsheet,

and use the usual syntax: =SUM( and then we’ll highlight the cells we want to total, these

three, and end with a parenthesis. $255 is the total sales for the kindergartners. Nice

job. Let’s make it easy with fill handle to find the totals for the other grades, just

like this…and then for the totals for each fundraiser. Look at H5. $4,138, the total

amount of money raised from sales in all grades, would be the same whether we dragged fill

handle across from the “grade totals” row (here)…or the “fundraiser totals”

column. Graphs can be a helpful way to view fundraising data like this—for more information,

see Paige’s video. Thanks for watching my screencast—hope you’ve learned a lot about

formulas, functions, and order of operations in Excel.