Microsoft Office Tutorials and References

In Depth Information

Chapter 22: 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 com-

mon 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?

Debugging
refers to 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 com-

puter program. If you create a formula that does not work as it should, you need to identify and correct the prob-

lem.

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 work-

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

Research on spreadsheet errors

Using a spreadsheet can be hazardous to your company's bottom line. It's tempting to simply assume that your

spreadsheet produces accurate results. If you use the results of a spreadsheet to make a major decision, it's espe-

cially important to make sure that the formulas return accurate and meaningful results.