Excel 2019 Bible

423

C H A P T E R

19

Making Your Formulas Error-Free

IN THIS CHAPTER

Identifying and correcting 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 worksheets. 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 previ-

ously 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—if you discover

the problem at all.

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

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.

Logical errors A formula doesn’t return an error, but it contains a logical flaw that causes it to

return an incorrect result.

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 of the

data that you want to sum.

Semantic errors An example is a function name that is spelled incorrectly. Excel will attempt to

interpret it as a name and will display the #NAME? error.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.