Uploaded by djdates on 22.06.2010

Transcript:

In this video, we will discuss relative, absolute, and mixed cell references.

Opening a new blank Excel spreadsheet, we will start by creating some data. We will

create a spreadsheet tracking the efforts of our sales staff over the months of January,

February, and March. We will just make up the data as we go along.

This is a complicated topic that many Excel users are not comfortable with, but as you

will see, knowing how to use absolute and mixed cell references can save you a lot of

time. You will only need to create your calculations once, even though you may be applying that

calculation against many different cells. Now that we have created the data, we will

create a row and column of totals. We could use Autosum or create the formula

manually, and then use the fill handle, or copy and paste, to duplicate the formula in

other cells. Notice that when we paste this formula, the new cells contain the same formula,

but with the cell references updated to reflect adjacent cells of their new location. This

is called relative cell referencing because the cell addresses change relative to the

location of the formula. Let's calculate the sales each person has

sold as a percentage of the whole group. We will divide the total for each person by the

total overall, entering an equal sign, clicking on the sales total, entering a forward leaning

slash for division, and then clicking on the overall total.

Now, let's try using the fill handle to fill this percentage to the next cell.

Notice that we get a division by zero error? If we edit our cell, we can see that first

cell reference moved from Sam to Cam, which is what we want.

But our second reference moved from the overall total cell to an empty cell, which is why

we are getting the division by zero error. Let's hit escape [Esc] to exit editing the

cell and return back to our original calculation. What we want to do is lock in the second cell

reference so that it doesn't move, but we still want the first cell reference to move

when we copy it to the next position, so it will calculate the percentage based on Cam

and not Sam. We lock in the cell references by placing dollar signs in front of the row

or the column we wish to lock in place. If we place a dollar in front of both the row

and the column, the cell will not move at all. We can also use the F4 key to toggle

the dollar signs locking in the row or column or both. Let's click on the second cell reference

and press F4. Notice the dollar signs in front of both the

row and column in the cell reference? Let's press enter and fill this calculation

to the rest of the cells. We can press F2 to edit the cells and see

how the first cell reference is still relative and changing as we will fill our cell across,

while the second reference is absolute and does not move when we copy the calculation

to another cell? This is absolute cell referencing, where we

are locking in a column and row in a cell reference to a specific cell, so it does not

change when the cell is copied. Now we will do a more complicated example.

Let's create a multiplication table, using the fill handle to populate the cells at the

top and side of the table. We will create a calculation in the first

result cell, multiplying one times one by selecting the appropriate cells.

We will then fill this calculation to the rest of the cells to the right, and we see

that our multiplication table is a little broken.

We can edit these cells and see what is happening. It looks like our first cell reference is

moving, when we want the cell reference to stay put. Let's lock this cell in place. For

now, we lock in both the column and the row for the first cell reference.

Filling across, we can see that the first row of our multiplication table now works

the way we expect it to. However, does it work the way we expect if

we fill down? It looks like both cell references are now

incorrect in our second row. We want the first cell reference to stay in the same column,

but move down the rows as we fill in the cells. So let's edit our first cell and lock only

the column on the first cell reference, and leave the row unlocked. We could edit this

manually or continue pressing the F4 key until we get the combination that we want.

We also want to make sure the second cell reference stays in our top row, but can move

freely to different columns as we fill in cells, so let's lock only the row on the second

reference. This is called a mixed cell reference, because one dimension, the row or column,

is absolute, while the other dimension is relative.

When we use fill again, going first horizontally, and then down, we see the calculations we

want to see. Let's try another example.

We are going to calculate the payroll for Sam, Cam, and Pam, and their coworkers Alice

and Bob, assuming for the sake of simplicity that all of these employees are in the same

tax bracket. We will enter some numbers for the gross salaries

of these employees. Then we will enter in the percentages we will use for Federal tax,

Medicare, Social Security, NY state tax, the employee 401k contribution, and the employer

401k contribution. We can multiply our gross pay by the federal

tax withholding percentage, but when we fill this data to the right, we have the same problem

we had before. We want to lock in the column on our first cell reference so the percentages

are calculated across the table.

When we fill across horizontally, our formula now looks good.

However, when we fill down, we can see that we have a serious problem.

Returning to our original cell, we can lock in the row on the second cell reference so

that the column changes, but the row will always stay the same.

Filling out the table again, we can now see our correct calculations.

Opening a new blank Excel spreadsheet, we will start by creating some data. We will

create a spreadsheet tracking the efforts of our sales staff over the months of January,

February, and March. We will just make up the data as we go along.

This is a complicated topic that many Excel users are not comfortable with, but as you

will see, knowing how to use absolute and mixed cell references can save you a lot of

time. You will only need to create your calculations once, even though you may be applying that

calculation against many different cells. Now that we have created the data, we will

create a row and column of totals. We could use Autosum or create the formula

manually, and then use the fill handle, or copy and paste, to duplicate the formula in

other cells. Notice that when we paste this formula, the new cells contain the same formula,

but with the cell references updated to reflect adjacent cells of their new location. This

is called relative cell referencing because the cell addresses change relative to the

location of the formula. Let's calculate the sales each person has

sold as a percentage of the whole group. We will divide the total for each person by the

total overall, entering an equal sign, clicking on the sales total, entering a forward leaning

slash for division, and then clicking on the overall total.

Now, let's try using the fill handle to fill this percentage to the next cell.

Notice that we get a division by zero error? If we edit our cell, we can see that first

cell reference moved from Sam to Cam, which is what we want.

But our second reference moved from the overall total cell to an empty cell, which is why

we are getting the division by zero error. Let's hit escape [Esc] to exit editing the

cell and return back to our original calculation. What we want to do is lock in the second cell

reference so that it doesn't move, but we still want the first cell reference to move

when we copy it to the next position, so it will calculate the percentage based on Cam

and not Sam. We lock in the cell references by placing dollar signs in front of the row

or the column we wish to lock in place. If we place a dollar in front of both the row

and the column, the cell will not move at all. We can also use the F4 key to toggle

the dollar signs locking in the row or column or both. Let's click on the second cell reference

and press F4. Notice the dollar signs in front of both the

row and column in the cell reference? Let's press enter and fill this calculation

to the rest of the cells. We can press F2 to edit the cells and see

how the first cell reference is still relative and changing as we will fill our cell across,

while the second reference is absolute and does not move when we copy the calculation

to another cell? This is absolute cell referencing, where we

are locking in a column and row in a cell reference to a specific cell, so it does not

change when the cell is copied. Now we will do a more complicated example.

Let's create a multiplication table, using the fill handle to populate the cells at the

top and side of the table. We will create a calculation in the first

result cell, multiplying one times one by selecting the appropriate cells.

We will then fill this calculation to the rest of the cells to the right, and we see

that our multiplication table is a little broken.

We can edit these cells and see what is happening. It looks like our first cell reference is

moving, when we want the cell reference to stay put. Let's lock this cell in place. For

now, we lock in both the column and the row for the first cell reference.

Filling across, we can see that the first row of our multiplication table now works

the way we expect it to. However, does it work the way we expect if

we fill down? It looks like both cell references are now

incorrect in our second row. We want the first cell reference to stay in the same column,

but move down the rows as we fill in the cells. So let's edit our first cell and lock only

the column on the first cell reference, and leave the row unlocked. We could edit this

manually or continue pressing the F4 key until we get the combination that we want.

We also want to make sure the second cell reference stays in our top row, but can move

freely to different columns as we fill in cells, so let's lock only the row on the second

reference. This is called a mixed cell reference, because one dimension, the row or column,

is absolute, while the other dimension is relative.

When we use fill again, going first horizontally, and then down, we see the calculations we

want to see. Let's try another example.

We are going to calculate the payroll for Sam, Cam, and Pam, and their coworkers Alice

and Bob, assuming for the sake of simplicity that all of these employees are in the same

tax bracket. We will enter some numbers for the gross salaries

of these employees. Then we will enter in the percentages we will use for Federal tax,

Medicare, Social Security, NY state tax, the employee 401k contribution, and the employer

401k contribution. We can multiply our gross pay by the federal

tax withholding percentage, but when we fill this data to the right, we have the same problem

we had before. We want to lock in the column on our first cell reference so the percentages

are calculated across the table.

When we fill across horizontally, our formula now looks good.

However, when we fill down, we can see that we have a serious problem.

Returning to our original cell, we can lock in the row on the second cell reference so

that the column changes, but the row will always stay the same.

Filling out the table again, we can now see our correct calculations.