Microsoft Office Tutorials and References
In Depth Information
• Apply the Text number format to the cell. Select Text from the Number Format drop-down list, which can be
found in the Home ⇒ Number group. If you haven't applied other horizontal alignment formatting, the value
will appear left-aligned in the cell (like normal text), and functions like SUM will not treat it as a value.
Note, however, that it doesn't work in the opposite direction. If you enter a number and then format it as
text, the number will be left-aligned, but functions will continue to treat the entry as a value.
• Precede the number with an apostrophe. The apostrophe isn't displayed, but the cell entry will be treated as
if it were text. Functions like SUM will not treat the cell as a number.
Even though a cell is formatted as Text (or uses an apostrophe), you can still perform some mathematical opera-
tions on the cell if the entry looks like a number. For example, assume cell A1 contains a numeric value pre-
ceded by an apostrophe. This formula displays the value in A1, incremented by 1:
This formula, however, treats the contents of cell A1 as 0:
To confuse things even more, if you format cell A1 as Text, the preceding SUM formula treats it as 0.
In some cases, treating text as a number can be useful. In other cases, it can cause problems. Bottom line? Just
be aware of Excel's inconsistency in how it treats a number formatted as text.
When a number isn’t treated as a number
If you import data into Excel, you may be aware of a common problem: Sometimes, the imported values are
treated as text. Here's a quick way to convert these nonnumbers to actual values. Activate any empty cell and
choose Home⇒Clipboard⇒Copy. Then, select the range that contains the values you need to fix. Choose
Home⇒Clipboard⇒Paste⇒Paste Special. In the Paste Special dialog box, select the Add option, and then click
OK. By “adding zero” to the text, you force Excel to treat the nonnumbers as actual values.
If background error checking is enabled, Excel will usually identity such nonnumber cells and give you an oppor-
tunity to convert them.
If background error checking is turned on, Excel flags numbers preceded by an apo-
strophe (and numbers formatted as Text) with a small triangle indicator in the cell's
upper-left corner. Activate a cell that displays such an indicator, and Excel displays an
icon. Click the icon, and you have several options on how to handle that potential error.
Figure 5-1 shows an example. Background error checking is controlled from the Excel
Options dialog box. Choose File ⇒ Options and navigate to the Error Checking section of
the Formulas tab.