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:
• 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
• 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!.
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-