Excel Annoyances

Chapter 3. Formula Annoyances

Transforming the data in your worksheets using formulas is what Excel does best. What could be annoying about that? Lots! Take one horrible example: consider what happens when you create a formula in one cell and copy it to another cell. If you don’t build the formula just right, the cell references in the formula might change based on where you pasted the copied formula! I’ll show you how to prevent this and other annoyances from happening.

Dates and times can present significant challenges in formulas. The arithmetic isn’t particularly hard—it just doesn’t always work the way you’d expect it to. Excel uses an exotic numerical system to refer to dates that seems totally arbitrary. But don’t worry: you’ll learn how to deal with this and other Excel quirks by the time you finish this chapter.

Finally, you’ll learn how to wrestle array formulas to the ground. This is one of Excel’s most powerful tools and one of the most annoying, because array formulas can be way confusing at first. But once you understand them, they end up saving you a lot of time.

FORMULA ENTRY AND EDITING ANNOYANCES

PREVENT COPIED FORMULAS FROM CHANGING CELL REFERENCES

The Annoyance:

I track sales for a department store using the worksheet shown in Figure 3-1. My boss asked me to highlight sales for the jewelry department, so I copied the formula from cell F15 to cell F18—but the formula changed from =SUM(F3:F14) to =SUM(F6:F17). I’m screwed! How do I keep that formula from changing?

Figure 3-1. When ...