Microsoft Office Tutorials and References

In Depth Information

**Chapter 4: Fixing Formula Boo-Boos**

Chapter 4

Fixing Formula Boo-Boos

In This Chapter

▶
Preventing errors with Excel

▶
Following the flow of cell and range references to and from formulas

▶
Using Excel tools to uncover and correct formulas

Excel would be nothing if it didn’t allow you to create formulas. Creating

formulas is, after all, the real purpose of a worksheet — to allow you to

build a solution that pertains to your specific needs. Without formulas, Excel

would be no more than a place to store information. Boring!

Excel allows formulas to have up to 8,192 characters in length. This means

you can create some monster formulas! Formulas can reference cells that

have formulas that reference other cells that have formulas that reference . . .

well, you get the idea!

Ah, but this comes with a price — how can you track down errors in long

formulas? How can you avoid them in the first place? In this chapter, I explain

how Excel steers you away from entering problematic formulas, and how to

correct completed formulas that are not working in the way you intended.

Catching Errors as You Enter Them

Excel is keeping an eye on you when you enter formulas. Don’t be worried! This

is a good thing. You aren’t being graded. Excel is helping you, not testing you.

All formulas start with an equal sign. When you complete an entry by

pressing Enter or Tab (or clicking into another cell), Excel scans the entry. If the

entry did indeed start with an equal sign, then Excel immediately looks for

three major problems: