Chapter 32: Making Your Worksheets Error-Free
651
Tip
Excel can help you out with mismatched parentheses. When you’re editing a formula and you move the cursor
over a parenthesis, Excel displays it (and its matching parenthesis) in bold for about one-half second. In addi-
tion, Excel color codes pairs of nested parentheses while you are editing a formula. n
Cells are filled with hash marks
A cell is filled with a series of hash marks (#) for one of two reasons:l (^) The column is not wide enough to accommodate the formatted numeric value. To correct
it, you can make the column wider or use a different number format (see Chapter 24).
l (^) The cell contains a formula that returns an invalid date or time. For example, Excel doesn’t
support dates prior to 1900 or the use of negative time values. A formula that returns either
of these values results in a cell filled with hash marks. Widening the column won’t fix it.
Blank cells are not blank
Some Excel users have discovered that by pressing the spacebar, the contents of a cell seem to
erase. Actually, pressing the spacebar inserts an invisible space character, which isn’t the same as
erasing the cell.When you enter a formula that has a syntax error, Excel attempts to determine the problem and offers a
suggested correction. The accompanying figure shows an example of a proposed correction.Be careful when accepting corrections for your formulas from Excel because it doesn’t always guess
correctly. For example, I entered the following formula (which has mismatched parentheses):=AVERAGE(SUM(A1:A12,SUM(B1:B12))Excel then proposed the following correction to the formula:
=AVERAGE(SUM(A1:A12,SUM(B1:B12)))You may be tempted to accept the suggestion without even thinking. In this case, the proposed formula
is syntactically correct — but not what I intended. The correct formula is=AVERAGE(SUM(A1:A12),SUM(B1:B12))Using Formula AutoCorrect