Part IV: Using Advanced Excel Features
656
#REF! errors
A #REF! error occurs when a formula uses an invalid cell reference. This error can occur in the
following situations:l (^) You delete the row column of a cell that is referenced by the formula. For example, the
following formula displays a #REF! error if row 1, column A, or column B is deleted:
=A1/B1
l You delete the worksheet of a cell that is reference by the formula. For example, the fol-
lowing formula displays a #REF! error if Sheet2 is deleted:
=Sheet2!A1
l (^) 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
l (^) You cut a cell (choose Home ➪ Clipboard ➪ Cut) and then paste it to a cell that’s refer-
enced by a formula. The formula will display #REF!.
#VALUE! errors
A #VALUE! error is very common and can occur under the following conditions:
l An argument for a function is of an incorrect data type, or the formula attempts to per-
form an operation using incorrect data. For example, a formula that adds a value to a text
string returns the #VALUE! error.
l (^) A function’s argument is a range when it should be a single value.
l A custom worksheet function is not calculated. You can press Ctrl+Alt+F9 to force a
recalculation.
l A custom worksheet function attempts to perform an operation that is not valid. For
example, custom functions can’t modify the Excel environment or make changes to other
cells.
l (^) You forget to press Ctrl+Shift+Enter when entering an Array formula.
When you edit a cell that contains a formula, Excel color-codes the cell and range references in the
formula. Excel 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 refer-
ences used in a formula, drag the outline’s border or fill handle (at the lower right of the outline). This
technique is often easier than editing the formula.
Pay Attention to the Colors