Microsoft Office Tutorials and References

In Depth Information

**Errors in Formulas**

Figure 2-6:
Use the Format Cells dialog box to change the Hidden and Locked status of a cell or

range.

By default, all cells are locked. Protecting a sheet prevents any locked cells from being changed.

So, you should unlock any cells that require user input before protecting your sheet.

Be aware that it’s very easy to crack the password for a worksheet. So, this technique

of hiding your formulas does not ensure that no one can view your formulas.

Errors in Formulas

It’s not uncommon to enter a formula only to find that the formula returns an error. Table 2-4 lists

the types of error values that may appear in a cell that has a formula.

Formulas may return an error value if a cell that they refer to has an error value. This is known as

the ripple effect: A single error value can make its way to lots of other cells that contain formulas

that depend on that cell.

Table 2-4:
Excel Error Values

Error Value

Explanation

#DIV/0!

The formula attempts to divide by zero (an operation not allowed on this planet). This also

occurs when the formula attempts to divide by an empty cell.

#NAME?

The formula uses a name that Excel doesn’t recognize. This can happen if you delete a

name used in the formula or if you misspell a function.

#N/A

The formula refers (directly or indirectly) to a cell that uses the NA function to signal

unavailable data. This error also occurs if a lookup function does not find a match.