An entry that includes only the numerals 0 through 9 and certain special characters, such as + – E e ( ) . , $ % and /, is a numeric value. An entry that includes almost any other character is a text value. Table 6-4 lists some examples of numeric and text values.

A number of characters have special effects in Excel. Here are some guidelines for using special characters:

Although you can type 32,767 characters in a cell, a numeric cell entry can maintain precision to a maximum of only 15 digits. This means you can type numbers longer than 15 digits in a cell, but Excel converts any digits after the 15th to zeros. If you are working with figures greater than 999 trillion or decimals smaller than trillionths, perhaps you need to look into alternative solutions, such as a Cray supercomputer!

If you type a number that is too long to appear in a cell, Excel converts it to scientific notation in the cell if you haven’t applied any other formatting. Excel adjusts the precision of the scientific notation depending on the cell width. If you type a very large or very small number that is longer than the formula bar, Excel displays it in the formula bar using scientific notation. In Figure 6-8, we typed the same number in cell A1 and cell B1; because cell B1 is wider, Excel displays more of the number but still displays it using scientific notation.

Figure 6-8. Because the number 123,456,789,012 is too long to fit in either cell A1 or cell B1, Excel displays it in scientific notation.

The values that appear in formatted cells are called displayed values; the values that are stored in cells and appear in the formula bar are called underlying values. The number of digits that appear in a cell—its displayed value—depends on the width of the column and any formatting you apply to the cell. If you reduce the width of a column that contains a long entry, Excel might display a rounded version of the number, a string of number signs (#), or scientific notation, depending on the display format you’re using.

If you type text that is too long for Excel to display in a single cell, the entry spills into adjacent cells if they are empty, but the text remains stored in the original cell. If you then type text in a cell that is overlapped by another cell, the overlapped text appears truncated, as shown in cell A3 in Figure 6-8. But don’t worry—it’s still all there.

If you have long text entries, text wrapping can make them easier to read. Text wrapping lets you enter long strings of text that wrap onto two or more lines within the same cell rather than overlap adjacent cells. Select the cells where you want to use wrapping, then click the Home tab on the ribbon and click the Wrap Text button, as shown in Figure 6-9. To accommodate the extra lines, Excel increases the height of the row.

Figure 6-9. Click the Wrap Text button to force long text entries to wrap within a single cell.

Sometimes you might want to type special characters that Excel does not normally treat as plain text. For example, you might want +1 to appear in a cell. If you type +1, Excel interprets this as a numeric entry and drops the plus sign (as stated earlier). In addition, Excel normally ignores leading zeros in numbers, such as 01234. You can force Excel to accept special characters as text by using numeric text entries.

To enter a combination of text and numbers, such as G234, just type it. Because this entry includes a nonnumeric character, Excel interprets it as a text value. To create a text entry that consists entirely of numbers, you can precede the entry with a text-alignment prefix character, such as an apostrophe. You can also enter it as a formula by typing an equal sign and enclosing the entry with quotation marks. For example, to enter the number 01234 as text so the leading zero is displayed, type either ‘01234 or =“01234” in a cell. Whereas numeric entries are normally right-aligned, a numeric text entry is left-aligned in the cell, just like regular text, as shown in Figure 6-10.

Figure 6-10. We typed the policy numbers in column A as text.

Text-alignment prefix characters, like formula components, appear in the formula bar but not in the cell. Table 6-5 lists all the text-alignment prefix characters.

Only the apostrophe text-alignment prefix character always works with numeric or text entries. The caret, backslash, and quotation mark characters work only if Transition Navigation Keys are turned on. To do so, click the File tab, click Options, select the Advanced category, and then scroll down to the Lotus Compatibility area and select the Transition Navigation Keys check box.

When you create a numeric entry that starts with an alignment prefix character, a small flag appears in the upper-left corner of the cell, indicating that the cell has a problem you might need to address. When you select the cell, an error button appears to the right. Clicking this button displays a menu of specific commands (refer to Figure 6-10). Because the apostrophe was intentional, you can click Ignore Error.

If you ever want to use characters in Excel that are not on your standard computer keyboard, you’re in luck. Clicking the Insert tab on the ribbon and then clicking the Symbol button gives you access to the complete character set for every installed font on your computer. Figure 6-11 shows the Symbol dialog box.

On the Symbols tab, select the font from the Font drop-down list; the entire character set appears. You can jump to specific areas in the character set by using the Subset drop-down list, which also indicates the area of the character set you are viewing if you are using the scroll bar to browse through the available characters. The Character Code box displays the code for the selected character. You can also highlight a character in the display area by typing the character’s code number. You can select decimal or hexadecimal ASCII character encoding or Unicode by using the From drop-down list. If you choose Unicode, you can select from a number of additional character subsets in the Subset drop-down list. The Special Characters tab in the Symbol dialog box gives you quick access to a number of commonly used characters, such as the em dash, the ellipsis, and the trademark and copyright symbols.

Figure 6-11. You can insert characters from the extended character sets of any installed font.