Excel 2010 Bible

649

CHAPTER

Making Your

Worksheets Error-Free

IN THIS CHAPTER

How to identify and correct

common formula errors

Using Excel auditing tools

Using formula AutoCorrect

Tracing cell relationships

Checking spelling and related

features

I

t goes without saying that you want your Excel worksheets to produce
accurate results. Unfortunately, it’s not always easy to be certain that the
results are correct — especially if you deal with large, complex work-
sheets. This chapter introduces the tools and techniques available to help
identify, correct, and prevent errors.

Finding and Correcting

Formula Errors

Making a change in a worksheet — even a relatively minor change — may

produce a ripple effect that introduces errors in other cells. For example,

accidentally entering a value into a cell that previously held a formula is

all too easy to do. This simple error can have a major impact on other formu-

las, and you may not discover the problem until long after you make the

change — or you may never discover the problem.

Formula errors tend to fall into one of the following general categories:

l (^) Syntax errors: You have a problem with the syntax of a formula.
For example, a formula may have mismatched parentheses, or a
function may not have the correct number of arguments.
l Logical errors: A formula doesn’t return an error, but it contains a
logical flaw that causes it to return an incorrect result.
l Incorrect reference errors: The logic of the formula is correct, but
the formula uses an incorrect cell reference. As a simple example,
the range reference in a Sum formula may not include all the data
that you want to sum.