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.

Figure 2-12

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).

Error Messages

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

Formula Errors.”

Creating an Absolute Formula

Reference

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