Cell references are one of the most important concepts in Excel, and there are a couple
of
different types that you need to know about: Relative references and absolute references.
Let's start with relative.
Relative references are the default type of reference,
and they're probably the type that you'll use most often.
In this example, I want to know the amount of
money spent on each item,
and to do this I'll need to multiply the price by the quantity.
I'll enter my formula here.
Start by typing the equals sign, and the formula will be D4*E4.
And then just press the Enter key.
If I use the fill handle to copy this formula to
all of these cells, you can see that in each one,
the cell references are actually going to be
different.
So we have D7, D8, D9, and so on.
That's because these are relative references.
In the original formula, when we enter the cell
references, what Excel actually sees is just the
location relative to the cell that contains the
formula.
In this case, it's looking two cells to the left
and also one cell to the left,
and those just happen to be D4 and E4 in this case.
And each copy is also looking two cells to the
left and one cell to the left.
This is really useful because it means we can
easily copy the formula,
and the cell references will sort of move with the formula.
And that doesn't necessarily mean that the references have to be on the same row,
like they are here - they could be anywhere in this sheet -
it just means that when the formula is copied to a
different cell,
the references will move along with the formula.
There are other times where you don't want the
cell reference to change.
In this example, we have different varieties of
roses,
and we want to find the percent of the total sold for each type.
So for Red Delight, we know that we sold 28 units,
but what percentage of the total is that?
We can find the percentage by dividing.
I'll type the equals sign, then C3 divided by C12.
Then, I'll use the fill handle to copy this formula to all of these cells,
but wait a minute, that doesn't look right at all.
The first one's right, because it's dividing by C12, which is our total,
but as we go down the list, you can see that this cell reference
is moving into these empty cells, which is not what we want.
We want it to stay frozen on C12 for all of these
formulas,
and to do that, we'll need to use an absolute reference.
Absolute references will have a dollar sign placed before
the column, row, or both, to keep them from changing.
If you put dollar signs before the column and the
row, then
neither of them will change, no matter where you copy the formula to.
If you just put the dollar sign before the row,
then the row will not change,
but the column will change if you copy the formula to a different column.
And if you just put the dollar sign before the
column,
then the column won't change, but the row can change.
Most of the time, you're just going to be using the first one, where there is
a dollar sign before the column and the row.
We can go back to this example and add dollar signs before the column and the row
to make C12 an absolute reference.
And now if we copy this formula, these numbers look a lot better.
If you double-click on some of them, you can see that the relative reference
is different in each one, but the C12 reference stays constant.
The type of cell references that you use in your
formulas are very important.
So whenever you're creating a formula, just ask
yourself
whether you want to use relative references, absolute references, or both.