Entering Data

The steps you take to enter data are basically the same regardless of the type of data entry: text, value, date, or time. Following are the steps you take to type a data entry into a cell :

1. Select the cell in which you want to type the entry.

2. Type the entry. As you type, the entry appears both in the cell and in the formula bar, as shown in Figure 4.2.

   Figure 4.2. As you type an entry, it appears in the cell and in the formula bar.

   

3. Accept the entry by doing one of the following:

   Press Enter to accept the entry and move to the next cell down.

   Press Tab to accept the entry and move to the next cell to the right.

   Click the Enter button (the green check mark) to the left of the formula bar to accept the entry and keep the current cell selected.

Though the basic steps for entering data in a worksheet are easy to master, the various types of data entries must be typed in a specific format that indicates to Excel the type of data each cell contains. The following sections cover the peculiar formatting requirements for the various data types.

Note

To cancel an entry before you accept it, press the Esc key or click the Cancel button (the red X ) to the left of the formula bar.

Entering Text Labels

Text entries are the easiest to master. A text entry is any entry that contains text only (nonnumeric characters ) or a combination of text, spaces, and numbers (as in a mailing address). Unlike numeric entries, which Excel aligns to the right, text entries are aligned with the left side of the cell.

In some cases, you may want a numerical entry, such as a ZIP code, treated as a text entry rather than as a numeric entry. To format a numeric entry as text, type a single quote before the entry; for example, you might type '60629 to have a ZIP code treated as text.

If you type a text entry that is longer than the cell is wide, and the cells to the right are blank, Excel displays the entire entry. If, however, the neighboring cells to the right contain entries, Excel truncates (displays only part of) the entry. You can drag the right side of the column heading to widen the column, as shown in Figure 4.3, and/or choose to have Excel wrap the text to display it on more than one line. See Chapter 5, "Controlling Rows, Columns, and Cells," for additional options and detailed instructions on how to widen columns , increase row heights, and wrap text in cells.

Figure 4.3. You can quickly widen a column to give your entries more room.

Tip

As you type entries in a column, Excel keeps track of those entries. If you start typing an entry that's similar to an entry you already typed, Excel's AutoComplete feature completes the entry for you. If the AutoComplete entry is correct, press Enter to accept it. If the AutoComplete entry is incorrect, continue typing the entry as you want it to appear. If AutoComplete tacks on additional characters, press the Delete key to remove them and then press Enter to accept your entry. To disable AutoComplete for values, open the Tools menu, click Options , and click the Edit tab. Click Enable AutoComplete for Cell Values to remove the check mark.

Entering Numbers

Numerical entries (values) can contain any of the numerals 0 to 9 and these special characters: + - () , . $ %. No spaces are allowed. All values are right-aligned, unless you specify otherwise when you format the cells (as explained later in this chapter in the section called "Adjusting Number Formats").

When you're typing values, the most important point to keep in mind is to allow Excel to handle the formatting. In other words, don't worry about typing a dollar sign or percentage symbol. Instead of typing $700.00 , simply type 700 . Later (as you will learn in "Adjusting Number Formats"), you can format the cell to display the currency format. Excel then will add the dollar sign and two decimal places to make the value look like a dollar amount.

If you type a number that is longer than the cell is wide, Excel expands the cell a little to accommodate the number. If you later reduce the cell width so that the number no longer fits, Excel displays the entry as a series of number signs, like this: #######. Excel doesn't display just a portion of a value (as it does with text entries), because if it did display a portion of the number, you might glance at the number and think you were looking at the entire number. To have Excel display the number in its entirety, widen the column, as shown in Figure 4.3.

Note

If you type a value that's wider than its cell and wider than Excel is willing to make the cell, Excel displays the value in scientific notation . That is, Excel displays the value as a decimal number from 1 to 10 raised to a specified power of 10. For example, 42,566 appears as 4.E+4 or 4.0 times 10 to the 4th power. The number you typed is still there. Just widen the column and then change the number format as explained later in this chapter in "Adjusting Number Formats."

Entering Dates and Times

Most dates and times are in a numeric format, but unlike values, dates and times require some special characters. Fortunately, Excel accepts times and dates entered in a variety of formats. Table 4.2 illustrates some of the more common valid formats for dates and times. As with numbers, you can enter dates in a generic format, such as 9-3-06 or 9/3/06, and then change the cell format later to have Excel display the date in whichever format you prefer.

Table 4.2. Common Date and Time Formats

Format	Example
MM/DD/YY	4/8/06
MMM-YY	Jan-06
DD-MMM-YY	28-Oct-06
DD-MMM	06-Sep
HH:MM	16:50
HH:MM:SS	8:22:59
HH:MM AM/PM	7:45 PM
HH:MM:SS AM/PM	11:45:16 AM
MM/DD/YY HH:MM	11/8/06 4:20
HH:MM MM/DD/YY	4:20 11/18/06

Dates and times can be a little quirky. The following list alerts you to some of the more unexpected attributes of times and dates:

• When typing dates, separate the day, month, and year with a dash (-) or forward slash (/).

• Avoid starting a date with the name of the month, such as April 8, 2006. Excel treats such dates as text entries. Instead, type 8-Apr-2006 or 8-4-2006.

• To avoid confusion, use all four digits to specify a year. For example, type 2006 instead of 06 . Excel generally interprets two-digit years from 00 to 29 as falling in the twenty-first century (20002029) and years 30 to 99 as falling in the twentieth century (19301999).

• Excel is on military time (a 24-hour clock), so its day consists of 24 hours, not two 12- hour periods. In military time, you don't need to say a.m. or p.m. You know that 8:30 is in the morning and 19:30 is at night. When typing times, either type the military time, or follow every entry with a.m. or p.m. For example, if you want to type 8:30 at night, type either 20:30 (military time) or 8:30 PM .

• To Excel, dates are numbers, and Excel started counting on January 1, 1900. Excel treats that date as the number 1. January 1, 2008, is 39448 because it is the 39,448th day from January 1, 1900. Likewise, Excel treats times as decimal fractions of a day. Why? Because Excel uses dates and times in calculations. For example, Excel can calculate the date on which a savings bond matures given the date it was issued and the number of years that must pass before it matures.

Tip

To enter today's date, press Ctrl+; (the Ctrl key plus the semicolon key). To enter the current time (as recorded on your computer's clock), press Ctrl+Shift+: (Ctrl plus Shift plus the colon key).

Adjusting Number Formats

Every cell in a blank Excel worksheet starts with a General format, which essentially is no format at all. Type a text entry, and Excel moves it to the left. Type a number, and Excel moves it to the right. If you type a date, Excel automatically applies a date format. Type a time, and Excel applies a time format. Other than that, Excel does little to the entries you type because it doesn't need to know whether a number represents dollars, percentage points, or the number of cattle on a ranch.

To you and me, however, values do have significance, and we need to see what those values represent. Fortunately, Excel provides several number formats that clearly show what the values represent. Table 4.3 lists and describes the available number formats.

Table 4.3. Excel Numbering Formats

Number Format	Description
General	Displays numbers just as you type them.
Number	Displays the whole number plus two decimal places. If you type 457, the cell displays 457.00.
Currency	Displays a dollar sign, followed by the whole number and two decimal places. If you type 457, the cell displays $457.00. Excel can display negative numbers in red, enclosed in parentheses, or both, or insert a minus sign before the number.
Accounting	Similar to Currency, except it provides no special display for negative values.
Date	Provides several options for displaying dates, including MM/DD/YYYY and DD-MM.
Time	Provides several options for displaying the time, including HH:MM:SS and HH:MM AM/PM.
Percentage	Displays the number you type, followed by two decimal places and a percentage symbolfor example, 99.99%.
Fraction	Displays a fraction as a fraction, rather than as a date or text entry.
Scientific	Displays a number in scientific notation. That is, Excel displays the base number, followed by E+#, where E+# indicates the number of decimal places you need to move the point to the right. For instance 42,578 shows up as 4.E+4.
Text	Treats numbers as text and left-aligns each number in its cell.
Special	Handles formatting for special entries, including ZIP codes, Social Security numbers, phone numbers, and other numerical entries that are not actually values.
Custom	Enables you to create your own numeric format.

To change the number formatting for a particular cell or group of cells, follow these steps:

1. Click the cell or drag over the cells whose number format you want to change. (To select multiple cells, see "Selecting a Cell Range," later in this chapter.)

2. Open the Format menu and choose Cells . The Format Cells dialog box appears.

3. If necessary, click the Number tab to bring it to the front.

4. In the Category list, click the general category that best represents the type of number format you want. For example, to display values as dollar amounts, click Currency , as shown in Figure 4.4.

   Figure 4.4. The Format Cells dialog box enables you to specify how you want values displayed.

   

5. If you are presented with additional choices, enter your preferences. For example, you may be able to specify the number of decimal places, the symbol (such as $ or %), or the way negative numbers are displayed.

6. Click OK . Excel returns you to the worksheet and displays any values in the selected cells according to the newly specified format.

For information about additional options in the Format Cells dialog box, see "Using the Format Cells Dialog Box," in Chapter 7, "Formatting Your Worksheet."

Tip

To apply common numeric formats quickly, select the cell(s) you want to format, and then click the Currency Style , Percent Style , or Comma Style button in the Formatting toolbar. Click the Increase Decimal button to add decimal places or the Decrease Decimal button to remove decimal places.

Note

You can add a note to a cell to explain its contents or call attention to the cell. These notes can be for yourself, your colleagues, or other people viewing the worksheet. To add a note ( comment ) to a cell, right-click the cell and choose Insert Comment . Type your comment in the resulting text box, and then click anywhere outside the text box. A red triangle appears in the upper-right corner of the cell. Rest the mouse pointer on the red triangle to view the comment. Right-click the cell again to view options for editing or deleting the comment.

Having Excel Read Entries Aloud as You Type

If you watch the screen while you type, you can see what you're typing and automatically correct entries on the fly. However, if you're typing numbers from a page, you don't want to keep looking up at the screen. Fortunately, Excel provides a text-to-speech feature that can read your data back to you as you type your entries, assuming , of course, that your computer is equipped with a sound card and speakers . To enable the text-to-speech feature, follow these steps:

1. Right-click any Excel toolbar or the menu bar, and choose Text to Speech . The Text to Speech toolbar appears, as shown in Figure 4.5. (Excel may display a dialog box indicating that the Text to Speech feature is not installed. Insert the Excel or Office installation CD, click OK , and follow the onscreen instructions to complete the installation.)

   Figure 4.5. Excel's text to speech feature reads entries back to you as you type them.

   

2. Click the Speak on Enter button (the rightmost button on the toolbar).

3. Click in a cell, type an entry, and press Enter . Excel reads the entry back to you.

4. To have Excel read back a block of cell entries, drag over the cells to select them, and then click the Speak Cells button (the leftmost button).

Caution

If Speak on Enter is on and you don't hear anything when you type an entry and press Enter, double-click the speaker icon in the Windows System Tray (on the right end of the taskbar). Make sure all volume controls are cranked up and that all Mute boxes are clear (no check marks). If you still don't hear anything, check the volume control on your speakers, and make sure the speakers are plugged in to the correct jack on your computer.