There are two main types of cell references in Excel: relative and absolute. Both behave
differently when copied to other cells, either as part of a formula or a function.
Relative references are the default (and probably the type you're going to use for most of your
formulas). In this example, I need to know the total cost of each menu item on the invoice.
To do this, I'll need to multiply the values in these two cells.
I'll just enter my formula here. Start by typing the equal sign… and then the formula
will be B4*C4. When you're done, press Enter on your keyboard.
Now if I use the fill handle to copy this formula to all of these cells… we can double-click
each copy, and see that the cell references have changed automatically. So we have B5…
B6…
B7, and so on.
That's because these are relative references. In other words, when Excel looks at our original
formula, what it actually sees is the location of the cells relative to the location of the
formula. That means in this case, it's looking at the two cells to the left of our current
location, and those just happen to be B4 and C4.
Each copy is also looking at the two cells to the left of the formula. This technique
is useful because it means we can copy the formula (as we did here), and the cell references
will sort of move along with it.
The references don't even have to be in the same row—they can be anywhere in the sheet.
There may be times when you don't want the cell references to change. Take this version
of the invoice.
I've added a column where we can list the sales tax for each item. We should be able
to enter our formula here… then copy it to the rest of the list.
To get started, I'm going to enter B4*C4 in parenthesis. Then I'm going to multiply by
cell E2, which contains our tax rate at 7.5% (which is the same as .075).
Next, I'll use the fill handle to copy the formula to the rest of these cells... but
wait, that doesn't look right at all. It's even affecting the formulas in the rest of
the worksheet.
The first copy of our formula is correct, because it's multiplying by E2, which contains
our tax rate… but as we go down the list, we can see that the reference is moving in
relation to the cells, which isn't what we want. We want it to stay frozen on E2 for
each copy of the formula—and for that, we'll need to use an absolute reference.
Absolute references have a dollar sign placed before the column, row, or both, to keep them
from changing when you copy the formula to another cell.
To keep the column and the row from changing, place a dollar sign in front of both.
To keep only the row from changing, place a dollar sign in front of the row only.
To keep only the column from changing, place a dollar sign in front of the column.
Most of the time, you'll be using the first type of absolute reference—the one where
there's a dollar sign before the column and the row.
Let's go back to our example, and add dollar signs before the column and the row to make
E2 an absolute reference.
Now if we copy the formula to the rest of this list… the errors go away, and we're
left with the correct sales tax for each item. The relative reference is different in each
cell, but the E2 reference (our absolute reference) stays the same.
Finally, I'd like to show you how to create cell references across different worksheets
in the same workbook. This can be useful if you want to create a formula on one worksheet
that references a value from another.
It works the same as a normal reference—you just have to include the name of the worksheet
along with the cell address. So to get started, figure out what cell you want to use… then
take note of its location. In this example, we're going to use cell E14 on the worksheet
Menu Order.
Next, switch to the worksheet where you want to use the reference. As you can see, I already
have a formula that's calculating the total of all these services. I just need to add
the total for the menu order in this cell here.
To reference the cell, type the name of the worksheet first… then an exclamation point…
and finally the cell address. (If the worksheet name includes a space, like in this example,
you'll need to add single quotation marks around the name.)
When you're done, press Enter… and Excel will pull the value from the other worksheet.
That means, if cell E14 ever changes on the Menu Order worksheet, it'll be updated here
too.
Mastering cell references is the key to creating formulas that work for you and your data.
Now you know how to use relative references, absolute references, and references across
multiple worksheets.