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 ...
Get Excel Annoyances 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.