Goodwill Community Foundation creating
opportunities for a better life
there are two main types of cell
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 0 7 5 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 e 2
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'll 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