Microsoft Office Tutorials and References

In Depth Information

• You delete the worksheet of a cell that is referenced by the formula. For example, the following formula dis-

plays a #REF! error if Sheet2 is deleted:

=Sheet2!A1

• You copy a formula to a location that invalidates the relative cell references. For example, if you copy the

following formula from cell A2 to cell A1, the formula returns #REF! because it attempts to refer to a

nonexistent cell:

=A1–1

• You cut a cell (by choosing Home
⇒
Clipboard
⇒
Cut) and then paste it to a cell that's referenced by a formula.

The formula will display #REF!.

#VALUE! errors

The #VALUE! error is very common and can occur under the following conditions:

• An argument for a function is of an incorrect data type or the formula attempts to perform an operation using

incorrect data. For example, a formula that adds a value to a text string returns the #VALUE! error.

• A function's argument is a range when it should be a single value.

• A custom worksheet function (created using VBA) is not calculated. With some versions of Excel, inserting

or moving a sheet may cause this error. You can press Ctrl+Alt+F9 to force a recalculation.

• A custom worksheet function attempts to perform an operation that is not valid. For example, custom func-

tions cannot modify the Excel environment or make changes to other cells.

• You forget to press Ctrl+Shift+Enter when entering an array formula.

Pay attention to the colors

When you edit a cell that contains a formula, Excel color-codes the cell and range references in the formula. Ex-

cel also outlines the cells and ranges used in the formula by using corresponding colors. Therefore, you can see

at a glance the cells that are used in the formula.

You also can manipulate the colored outline to change the cell or range reference. To change the references that

are used in the formula, drag the outline's border or
fill handle
(at the lower-right corner of the outline). Using this

technique is often easier than editing the formula.

Absolute/relative reference problems

As I describe in Chapter 2, a cell reference can be relative (for example, A1), absolute (for example, $A$1), or

mixed (for example, $A1 or A$1). The type of cell reference that you use in a formula is relevant only if the

formula will be copied to other cells.

A common problem is to use a relative reference when you should use an absolute reference. As shown in Fig-

ure 22-3, cell C1 contains a tax rate, which is used in the formulas in column C. The formula in cell C4 is as fol-

lows:

=B4+(B4*$C$1)