Goodwill Community Foundation creating
opportunities for a better life 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 B 4
times C 4 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 B 4 times C 4 in
parentheses then I'm going to multiply
by cell e2 which contains our tax rate
at 7.5% which is the same as
point zero seven five 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 e to 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 e to 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 will be
updated here to 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
you