Absolute Beginners Guide to Microsoft Office Excel 2003

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

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.

Tip

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

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

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

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

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

Note

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