Page 43 - Excel Timesaving Techniques for Dummies

06_574272 ch05.qxd 10/1/04 10:25 PM Page 28
28
Technique 5: Tailoring Excel’s Error Checking to Your Needs
When you’re new to Excel, the ToolTips connected Inconsistent Formula in Region: Adds an error
to the error indicators are a godsend because they indicator to any cells containing formulas whose
reinforce the rules and start to make sense of those structures don’t match the prevailing pattern in
largely inexplicable error values. As you become a the rest of those in a particular table.
more experienced user, they lose their luster and
Formula Omits Cells in Region: Adds an error
can become downright annoying (obscuring data
indicator to any cells containing formulas that
you want to see and otherwise getting in your way don’t refer to cells within a particular table when
as you inadvertently pass the mouse pointer over most of the others do.
them).
Unlocked Cells Containing Formulas: Adds an
Fortunately, Excel makes it easy to control when error indicator to any unlocked cells containing
error indicators appear in cells containing error formulas (you can unlock cells by pressing Ctrl+1,
values. You can also temporarily suppress the dis- clicking the Protection tab, and deselecting the
play of all error indicators in the worksheet. As part Locked check box).
of this technique on cleaning up the error indicators Formulas Referring to Empty Cells: Adds an
in your worksheet, I give you tips on temporarily error indicator to any cells containing formulas
suppressing the display of those unsightly error referring to empty cells. (This is the only Error
values both on-screen and in your printouts. Checking option not selected by default.)
List Data Validation Error: Adds an error indica-
Modifying the Error Checking tor to cells that contains entries that don’t follow
the data validation condition applied to them.
Settings

The Error Checking tab of the Options dialog box
(Tools➪Options), shown in Figure 5-2, contains a list
of check-box options that correspond to conditions
in which Excel adds an error indicator to a cell. Note
that in this list, only the first check box option has to
do with actual error values in the cells. All the other
options have to do with conditions that might be
problematic to the spreadsheet even though they
don’t result in blatant error values.
Here is the entire list of Error Checking options:

Evaluates to Error Value: Adds error indicators
to cells whose formulas return error values and
includes ToolTips that try to explain the reason • Figure 5-2: The Error Checking options enable you to
for the particular error value.
modify when error indicators appear.
Text Date with 2 Digit Years: Adds an error indi-
cator to cells that contain two-digit dates entered As you can see from the list of Error Checking
as text, as in ’89 or ’76. options, error indicators often don’t represent
an error per se but simply an anomaly in the
Number Stored as Text: Adds an error indicator
to cells that contain numbers entered as text, as prevailing pattern. If you’re not particularly
in ’1123 or ’45.67.