Microsoft Office Tutorials and References
In Depth Information
Creating an Absolute or Mixed Formula Reference
Creating an Absolute
Normally when you copy a formula, the
cell addresses in the original formula are adjusted
to reflect the new location. For example, if you
copy the formula =B8*2.25 from cell B10 to
cell C10, the formula automatically changes to
=C8*2.25 to reflect the new column to which
the formula has been copied.
Sometimes, this automatic adjustment is not what
you want to happen. For example, in Figure 2-12,
cell C31 contains the total of the monthly college
expenses. To determine the percentage of the
total expenses that September represents, you
might type the formula =C22/C31 in cell D22.
However, if you then copy this formula down
the column, you’ll soon discover a problem. For
example, when you copy the formula to cell
D23, it changes to =C23/C32 (the rows in the
cell addresses are adjusted by one row down).
However, as you can see in Figure 2-12, cell C32
is empty, so not only will the formula result be
wrong, you’ll get an error because Excel doesn’t
like it when you try to divide by zero.
Sometimes you don’t want Excel to automatically
adjust cell references when formulas are copied.
In this example, what you want to do is to have
all the copied formulas refer to cell C31 (which
contains the total college expenses) when you
copy the formula from cell D22 (which computes
the percentage of the total college expenses
incurred in September) to the other monthly cells
(D23:D30). To do that, you must make the cell
address C31 absolute (non-changeable) rather
than relative (changeable).
When you create a formula that results in
certain errors, such as trying to divide some
number by zero, Excel displays an error
message in the cell instead of the formula result.
To learn more about such errors and how to
ﬁx them, see Chapter 4, “Troubleshooting
Creating an Absolute Formula
To make a cell reference in a formula absolute,
add a $ (dollar sign) before the letter and the
number that make up the cell address. For
example, the formula for cell D22 in this
example should read =C22/$C$31 to indicate that