Chapter 32. Making Your Worksheets Error-Free


How to identify and correct common formula errors

Using Excel auditing tools

Using formula AutoCorrect

Tracing cell relationships

Checking spelling and related features

It 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.

32.1. 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 formulas, 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:

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

Get Excel® 2010 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.