Microsoft Office Tutorials and References

In Depth Information

**Chapter 21: Tools and Methods for Debugging Formulas**

21

Tools and Methods for

Debugging Formulas

In This Chapter

What is formula debugging?

●

How to identify and correct common formula errors

●

A description of Excel’s auditing tools

●

Errors happen. And when you create Excel formulas, errors happen very frequently. This chapter

describes common formula errors and discusses tools and methods that you can use to help

create formulas that work as they are intended to work.

Formula Debugging?

The term
debugging
refers to the process of identifying and correcting errors in a computer

program. Strictly speaking, an Excel formula is not a computer program. Formulas, however, are

subject to the same types of problems that occur in a computer program. If you create a formula

that does not work as it should, you need to identify and correct the problem.

The ultimate goal in developing a spreadsheet solution is to generate accurate results. For simple

worksheets, this is not difficult, and you can usually tell whether the formulas are producing

correct results. But as your worksheets grow in size or complexity, ensuring accuracy becomes more

difficult.

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 made the change — or you

may
never
discover the problem.