Excel Annoyances

FORMULA ERROR AND AUDITING ANNOYANCES

TELL EXCEL WHAT IS AN ERROR AND WHAT ISN’T

The Annoyance:

I’ve figured out how to convince Excel to treat numbers I type into cells as text so that it doesn’t delete leading zeros. But since I started using Excel 2002, when I copy those cells to another set of text-formatted cells the program marks the cells with an annoying green flag telling me there’s some sort of nonfatal error or inconsistency. This problem never came up in Excel 97 or 2000, so I assume this is some new method Excel’s programmers came up with to torture me. Help!

The Fix:

To prevent Excel 2002 or 2003 from marking cells that store numbers as text with an error flag, select Tools → Options, select the Error Checking tab (shown in Figure 3-11), and uncheck the “Number stored as text” box.

Figure 3-12. Use the Error Checking tab’s controls to avoid distracting cell markers in cells that don’t really contain errors.

The Error Checking tab contains other checkboxes that prevent Excel from marking cells that exhibit other characteristics. The two you should consider unchecking immediately are “Inconsistent formula in region” and “Formula omits cells in region.” These types of error flags are helpful for worksheets that summarize lists of data (e.g., sales by hour), but they aren’t very helpful on summary worksheets that use data from all over a workbook. ...