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

Uploaded by erincis220 on 04.10.2010

Transcript:

Hello, my name is Erin, and today I will be discussing how to effectively use cell address

types in Microsoft Excel.

Before we begin it is important to know what a cell address is. In Excel, each box where

you can put information has cell address, which consists of a column letter and row

number. For example, if you look where my cursor is currently, the cell address is B4.

The first type of cell address I am going to talk about is called a relative cell address.

Recall how Millie’s screen cast on formulas used a spreadsheet about test averages. Millie

was able to use a formula to find the class average of each test. Instead of writing a

new formula, she was able to “fill” the averages of tests as well as the class average

with the click of a mouse. If I look here my formula consists of finding the average

for student Rebecca from B2 to E2. Now, if I place my cursor in the corner of the cell,

my cursor changes and creates a fill handle. I then move down the spreadsheet and new averages

are created for each student. This is called autofill. If I look at the new averages given,

instead of averaging for student Rebecca, and new students average is calculated as

seen here with student Sarah. Because of the relative shift, the averages are filled relative

to the proper row we are trying to average. You can also do this vertically. If you wanted

to find each tests average, once again enter a formula to find the average for the first

test. When you fill across, the average shifts relative to the column we are averaging. In

looking at this example, when I filled across, the new test average that I wanted to find

shifted across as well.

While this is a useful tool, there are times when it is not beneficial for the cell address

in a formula to shift relative to a new row or column. In looking at this new set of data

about candy bars, to find the amount of money grade k would make if 5 candy bars were sold

by each student, the formula should multiply the price of the candy bar, the number of

students in the grade, and the number of candy bars sold. So here we have entered that formula.

When I fill across, you can see there is an error occurring. So what could the problem

be? Well, if we look at the formula for this cell, we want to multiply the number of candy

bars sold per student, the number of students in the grade level as well as price of the

candy bar. However, we see that the column letter has changed not only for the number

of candy bars sold per student but the price of the candy bar and the number of students

in the grade level. How to we fix this? Well, if we go back to the original equation, we

see that for C3, we do not want this cell to be shifting over columns relative to when

we autofill. To fix this, we put in a dollar sign. This dollar sign has nothing to do with

money, but in fact tells our formula that this cell should not be shifting by column.

Now if we look at cell C5, we see that in our formula we do want the cell to be shifting

over, changing the number of candy bars per student to fill in the table. Now, if we look

at B6, we see that we do not want this number to be shifting over because the number sixty

applies to this entire row. To stop that cell from shifting over, we again add a dollar

sign in front of the column letter. Now when I attempt to autofill I see there are now

values inputted. When I double check and check the formula of this cell, I see that we are

multiplying the correct three numbers. Now when I try to do this vertically, there is

another error occurring about value. Why did this happen? Well, if I look at the formula

for this I can see instead of multiplying the price of the candy bar, it has shifted

down to a value that we have already found, which is incorrect. Again when I look at C8,

instead of multiplying the number of candy bars sold per student, it’s multiplying

a random value because these both have shifted down. Now when I look at B9, I see that it

has also shifted down but it is the correct thing to do in this formula. How do I fix

this? Well, if I go back to my original formula, I can see that we do not want the row number

changing for the price of the candy bar. To stop this I again will put in a dollar sign.

Now when I look at C5, the number of candy bars sold per student, I also do not want

the row number to ever change so I place a dollar sign. Finally, when I look at cell

B6, I see that I do want the row number to change, because it will shift for the number

of student in grades. Now when I try to autofill, I see there are values put in correctly. To

double-check this, I click on a cell and I see that the correct numbers are being multiplied.

Now I am able to autofill for the entire table. To review these new types of cell addresses,

I see that in my formula column C and row 3 is never changing. For the number of candy

bars sold per student, the column is changing for different amount of candy bars but the

row is not. Now when I look at B6 I see that the row is changing and the column is not.

When something should not be changing, you put a dollar sign in front of the column letter

or row number.

I hope this has helped increase your knowledge on cell addresses, and if you have any other

questions, please check out our class wiki for more information

types in Microsoft Excel.

Before we begin it is important to know what a cell address is. In Excel, each box where

you can put information has cell address, which consists of a column letter and row

number. For example, if you look where my cursor is currently, the cell address is B4.

The first type of cell address I am going to talk about is called a relative cell address.

Recall how Millie’s screen cast on formulas used a spreadsheet about test averages. Millie

was able to use a formula to find the class average of each test. Instead of writing a

new formula, she was able to “fill” the averages of tests as well as the class average

with the click of a mouse. If I look here my formula consists of finding the average

for student Rebecca from B2 to E2. Now, if I place my cursor in the corner of the cell,

my cursor changes and creates a fill handle. I then move down the spreadsheet and new averages

are created for each student. This is called autofill. If I look at the new averages given,

instead of averaging for student Rebecca, and new students average is calculated as

seen here with student Sarah. Because of the relative shift, the averages are filled relative

to the proper row we are trying to average. You can also do this vertically. If you wanted

to find each tests average, once again enter a formula to find the average for the first

test. When you fill across, the average shifts relative to the column we are averaging. In

looking at this example, when I filled across, the new test average that I wanted to find

shifted across as well.

While this is a useful tool, there are times when it is not beneficial for the cell address

in a formula to shift relative to a new row or column. In looking at this new set of data

about candy bars, to find the amount of money grade k would make if 5 candy bars were sold

by each student, the formula should multiply the price of the candy bar, the number of

students in the grade, and the number of candy bars sold. So here we have entered that formula.

When I fill across, you can see there is an error occurring. So what could the problem

be? Well, if we look at the formula for this cell, we want to multiply the number of candy

bars sold per student, the number of students in the grade level as well as price of the

candy bar. However, we see that the column letter has changed not only for the number

of candy bars sold per student but the price of the candy bar and the number of students

in the grade level. How to we fix this? Well, if we go back to the original equation, we

see that for C3, we do not want this cell to be shifting over columns relative to when

we autofill. To fix this, we put in a dollar sign. This dollar sign has nothing to do with

money, but in fact tells our formula that this cell should not be shifting by column.

Now if we look at cell C5, we see that in our formula we do want the cell to be shifting

over, changing the number of candy bars per student to fill in the table. Now, if we look

at B6, we see that we do not want this number to be shifting over because the number sixty

applies to this entire row. To stop that cell from shifting over, we again add a dollar

sign in front of the column letter. Now when I attempt to autofill I see there are now

values inputted. When I double check and check the formula of this cell, I see that we are

multiplying the correct three numbers. Now when I try to do this vertically, there is

another error occurring about value. Why did this happen? Well, if I look at the formula

for this I can see instead of multiplying the price of the candy bar, it has shifted

down to a value that we have already found, which is incorrect. Again when I look at C8,

instead of multiplying the number of candy bars sold per student, it’s multiplying

a random value because these both have shifted down. Now when I look at B9, I see that it

has also shifted down but it is the correct thing to do in this formula. How do I fix

this? Well, if I go back to my original formula, I can see that we do not want the row number

changing for the price of the candy bar. To stop this I again will put in a dollar sign.

Now when I look at C5, the number of candy bars sold per student, I also do not want

the row number to ever change so I place a dollar sign. Finally, when I look at cell

B6, I see that I do want the row number to change, because it will shift for the number

of student in grades. Now when I try to autofill, I see there are values put in correctly. To

double-check this, I click on a cell and I see that the correct numbers are being multiplied.

Now I am able to autofill for the entire table. To review these new types of cell addresses,

I see that in my formula column C and row 3 is never changing. For the number of candy

bars sold per student, the column is changing for different amount of candy bars but the

row is not. Now when I look at B6 I see that the row is changing and the column is not.

When something should not be changing, you put a dollar sign in front of the column letter

or row number.

I hope this has helped increase your knowledge on cell addresses, and if you have any other

questions, please check out our class wiki for more information