Microsoft Office Tutorials and References
In Depth Information
matching parenthesis) in bold for about one second. In addition, nested parentheses
appear in a different color.
Cells are filled with hash marks
A cell displays a series of hash marks (#) for one of two reasons:
• 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.
• The cell contains a formula that returns an invalid date or time. For example, Excel does not support dates
prior to 1900 or the use of negative time values. Attempting to display either of these will result 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 is not the same as erasing the cell.
For example, the following formula returns the number of nonempty cells in range A1:A10. If you “erase” any
of these cells by using the spacebar, these cells are included in the count, and the formula returns an incorrect
If your formula doesn't ignore blank cells the way that it should, check to make sure that the blank cells are
really blank cells. Here's how to search for cells that contain only a blank character:
1. Press Ctrl+F to display the Find and Replace dialog box.
2. In the Find What box, type a space character.
3. Make sure the Match Entire Cell Contents check box is selected.
4. Click Find All.
If any cells that contain only a space character are found, you'll be able to spot them in the list displayed at the
bottom of the Find and Replace dialog box.
Extra space characters
If you have formulas that rely on comparing text, be careful that your text doesn't contain additional space char-
acters. Adding an extra space character is particularly common when data has been imported from another
Excel automatically removes trailing spaces from values that you enter, but trailing spaces in text entries are not
deleted. It's impossible to tell just by looking at a cell whether text contains one or more trailing space charac-
The TRIM function removes leading spaces, trailing spaces, and multiple spaces within a text string.
Figure 22-1 shows some text in column A. The formula in B1, which was copied down the column, is