MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))

Excel accepts two types of cell entries: constants and formulas. Constants fall into three main categories: numeric values, text values (also called labels or strings), and date/time values. Excel also recognizes two special types of constants called logical values and error values.

For more about date/time values, see Chapter 15, "Formatting and Calculating Date and Time."

Making Entries in Cells and in the Formula Bar

To make an entry in a cell, just select the cell, and start typing. As you type, the entry appears both in the formula bar and in the active cell. The flashing vertical bar in the active cell is called the insertion point.

After you finish typing, you must "lock in" the entry to store it permanently in the cell by pressing Enter. Pressing Enter normally causes the active cell to move down one row. You can change this so that when you press Enter, either the active cell doesn't change or it moves to an adjacent cell in another direction. Click the Microsoft Office Button, click Excel Options, select the Advanced category, and either clear the After Pressing Enter, Move Selection check box or change the selection in the Direction drop-down list. You also lock in an entry when you move the selection to a different cell by pressing Tab, Shift+Tab, Shift+Enter, or an arrow key, among other methods, after you type the entry, as shown in Table 6-3.

When you begin typing an entry, three buttons appear on the formula bar: Cancel, Enter, and Insert Function. When typing a formula where the entry begins with an equal sign (=), a plus sign (+), or a minus sign (-), a drop-down list of frequently used functions becomes available, as shown in Figure 6-7.

Figure 6-7: When you start entering a formula by typing an equal sign, the formula bar offers ways to help you finish it.

For more about editing formulas, see Chapter 12, "Building Formulas."

Entering Simple Numeric and Text Values

An entry that includes only 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.

Using Special Characters

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

• If you begin a numeric entry with a plus sign, Excel drops the plus sign.

• If you begin a numeric entry with a minus sign, Excel interprets the entry as a negative number and retains the sign.

• In a numeric entry, the characters E and e specify an exponent used in scientific notation. For example, Excel interprets 1E6 as 1,000,000 (1 times 10 to the sixth power), which is displayed in Excel as 1.00E+06. To enter a negative exponential number, type a minus sign before the exponent. For example, -1E6 (1 times 10 to the negative sixth power) equals -1,000,000 and is displayed in Excel as -1.00E+06.

• Excel interprets numeric constants enclosed in parentheses as negative numbers, which is a common accounting practice. For example, Excel interprets (100) as -100.

• You can use decimal points and commas as you normally would. When you type numbers that include commas as separators, however, the commas appear in the cell but not in the formula bar; this is the same as if you had applied one of the built-in Excel Number formats. For example, if you type 1,234.56, the value 1234.56 appears in the formula bar.

• If you begin a numeric entry with a dollar sign ($), Excel assigns a Currency format to the cell. For example, if you type $123456, Excel displays $123,456 in the cell and 123456 in the formula bar. In this case, Excel adds the comma to the worksheet display because it's part of the Currency format.

• If you end a numeric entry with a percent sign (%), Excel assigns a Percentage format to the cell. For example, if you type 23%, Excel displays 23% in the formula bar and assigns a Percentage format to the cell, which also displays 23%.

• If you use a slash (/) in a numeric entry and the string cannot be interpreted as a date, Excel interprets the number as a fraction. For example, if you type 11 5/8 (with a space between the number and the fraction), Excel assigns a Fraction format to the entry, meaning the formula bar displays 11.625 and the cell displays 11 5/8.

For more about the built-in Excel Number formats, see "Formatting in Depth" on page 295. For more information about date and time formats, see "How AutoFill Handles Dates and Times" on page 214.

Understanding the Difference Between Displayed Values and Underlying Values

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 both cell A1 and cell B1; however, 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.

For more information about increasing the width of a cell, see "Changing Column Widths" on page 335.

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 have applied to the cell. If you reduce the width of a column that contains a long entry, Excel might display a rounded-off version of the number, a string of number signs (#), or scientific notation, depending on the display format you're using.

Creating Long Text Values

If you type text that is too long for Excel to display in a single cell, Excel overlaps the adjacent cells, but the text remains stored in the original cell. If you then type text in a cell that is overlapped by another cell, the overlapping text appears truncated, as shown in cell B3 in Figure 6-8. But don't worry-it's still all there.

Using Text Wrapping

If you have long text entries, text wrapping can make them easier to read. Text wrapping lets you type long strings of text that wrap onto two or more lines within the same cell rather than overlapping adjacent cells. Select the cells where you want to use wrapping, then click the Home tab on the Ribbon, and finally 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.

For more about wrapping text, see "Wrapping Text in Cells" on page 319.

Understanding Numeric Text Entries

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, before typing them, you click the Microsoft Office Button, click Excel Options, select the Advanced category, and then scroll down to the Lotus Compatibility area and select the Transition Navigation Keys check box.

On the CD You'll find the Humongous08.xlsx file in the Sample Files section of the companion CD.

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-type smart tag appears to the right. Clicking this smart tag displays a menu of specific commands (refer to Figure 6-10). Because the apostrophe was intentional, you can click Ignore Error.

Entering Symbols

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.

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

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 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 of the selected character. You can also highlight a character in the display area by typing a character code number. You can select decimal or hexadecimal ASCII character encoding or Unicode 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.

Making Entries in Ranges

To make a number of entries in a range of adjacent cells, first select those cells. Then press Enter, Shift+Enter, Tab, or Shift+Tab to move the active cell within the range. For example, to fill in a range of selected cells, select the range, and begin typing entries, as shown in Figure 6-12. Each time you press Enter, the active cell moves to the next cell in the range. The active cell never leaves the selected range until you specifically select another cell or range; in other words, when you reach the edge of the range and press Enter, the active cell jumps to the beginning of the next column or row. You can continue making entries this way until you fill the entire range. The advantage of this trick is that you don't need to take your hands off the keyboard to select cells with the mouse when making many entries at once.

Figure 6-12: You can easily make entries in a range of cells by first selecting the entire range.

Editing and Undoing Entries

You can correct simple errors as you type by pressing Backspace before you press Enter to lock in the cell entry, which erases the character to the left of the insertion point. However, to make changes to entries you have already locked in, you first need to enter Edit mode. (The mode indicator at the lower-left corner of the status bar has to change from Ready to Edit.) Use one of the following techniques to enter Edit mode:

• To edit a cell using the mouse, double-click the cell, and position the insertion point at the location of the error.

• To edit a cell using the keyboard, select the cell, and press F2. Use the arrow keys to position the insertion point in the cell.

By selecting several characters before you begin typing, you can replace several characters at once. To select several characters within a cell using the keyboard, enter Edit mode, place the insertion point just before or just after the characters you want to replace, and press Shift+Left Arrow or Shift+Right Arrow to extend your selection.

If you need to erase the entire contents of the active cell, press Delete, or press Backspace and then Enter. Pressing Enter acts as a confirmation of the deletion. If you press Backspace accidentally, click the Cancel button or press Esc to restore the contents of the cell before pressing Enter. You can also erase the entire contents of a cell by selecting the cell and typing the new contents to replace the old. Excel erases the previous entry as soon as you begin typing. To revert to the original entry, press Esc before you press Enter.

To restore an entry after you press Delete or after you have locked in a new entry, click Edit, Undo; alternatively, press Ctrl+Z. The Undo command remembers the last 16 actions you performed. If you press Ctrl+Z repeatedly, each of the last 16 actions is undone, one after the other, in reverse order. You can also click the small arrow next to the Undo button to display a list of remembered actions. Drag the mouse to select one or more actions, as shown in Figure 6-13. After you release the mouse, all the selected actions are undone. The Redo button works the same way; you can quickly redo what you have just undone, if necessary.

Figure 6-13: Click the small arrow next to the Undo button to select any number of the last 16 actions to undo at once.

Smart tags give you instant access to commands and actions that are relevant to the current task. Many editing actions, such as copying and pasting cells, invoke a smart tag that appears adjacent to the last cell edited. If you click the tag, a smart tag action menu offers retroactive editing options:

Smart tags appear in many situations, so you will hear about them in other places in this book. For examples of smart tag use, see "Tracing Errors" on page 249, "Pasting Selectively Using Paste Special" on page 186, and "Entering a Series of Dates" on page 523.