Excel 2007 Tutorial 3.2. Absolute and Mixed Cell References

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.