Entering Worksheet Data

Often, entering worksheet data requires nothing more than clicking the correct cell to select it and then typing the data. The various kinds of data behave differently when entered, however, so you should understand how Excel accepts assorted data.

Excel can work with the following kinds of data:

• Labels ” Text values such as names and addresses, as well as date and time values.

• Numbers ” Numeric values such as 34, “291, 545.67874, and 0.

• Formulas ” Expressions that compute numeric results. (Some formulas work with text values as well.)

Excel also works well with data from other Office 2003 products as well as integrates into the online Internet world by supporting hyperlinks that you can embed into your worksheets. Additionally, you can import (transfer) worksheet data from other non-Microsoft products, such as Lotus 1-2-3.

Entering Text

If you want to put text (such as a title or a name) in a cell, just click the cell to select it and then type the text. By default, Excel left-justifies the text in the cell. As you type, the text appears both in the cell and in the formula bar (see Figure 6.3). Remember that the name box to the left of the formula bar displays the name of the cell into which you are entering data, such as C7. When you press Enter, Excel moves the cell pointer down one row. In addition to pressing Enter, you can click the Enter button (indicated by a green checkmark) to the right of the name box to keep the current cell selected, or press one of the arrow keys or the Tab key to move the cell pointer to a different cell adjacent to the current one.

Figure 6.3. Excel might or might not display all of a cell's contents.

Press Tab to move the cell pointer to the right or the arrow keys to move the cell pointer in any direction after you enter data.

As you type text into a cell, you can press Backspace to erase what you've typed before anchoring the contents in the cell with Enter or another cursor-movement key. If you press Esc or press the Cancel button at any point during your text entry but before you move to another cell, Excel erases the text you typed in the cell and restores the original cell contents. In addition, you can click the Undo button or press Ctrl+Z to back up to a cell's previous state.

When you create a new worksheet, the cell sizes take on the width and height size specified by the template you specify or by the default template if you don't specify a different one. If the width of your text is greater than a cell's original width, Excel does one of two things, depending on the contents of the next cell to the right:

• If the adjacent cell is empty, Excel displays the entire contents of your entry, with the overflow spilling into the next cell to the right.

• If the adjacent cell contains data, Excel truncates (cuts off) the wide cell to show only as much text as fits in the cell's width. Excel does not remove the unseen data from the cell; however, if the adjacent cell contains data, it always displays instead.

Figure 6.4 shows two long labels in cells C5 and C10. The same label, which is longer than standard cell width, appears in both cells . Because no data resides in D5, Excel displays all the contents of C5. The data in D10, however, overwrites the tail end of C10. C10 still contains the complete label, but only part of it is visible.

Figure 6.4. Excel might or might not display all of a cell's contents.

You can increase and shrink the width and height of columns and rows by dragging the right edge of a column heading or the bottom edge of a row heading. If you drag the right edge of column D to the right, for example, all rows in the entire column D widen.

Excel usually recognizes any entry that begins with an alphabetical character as text. Some textual data, such as price codes, telephone numbers, and ZIP Codes, can fool Excel into thinking you are entering numeric data because of the initial numeric value. As you see in the next section, Excel treats numeric data differently from text data when you type the data into cells. If you want Excel to treat a number (such as a ZIP Code) as a text entry so that it does not perform calculations on the cell, precede the contents with a single apostrophe ( ' ). For example, to type the ZIP Code 74137, type '74137 ; the apostrophe lets Excel know to format the value as text.

When you enter what would otherwise be a valid number but use the apostrophe prefix, Excel places a small green triangle in the cell's upper-left corner. This triangle is a small warning that cautions you about the apostrophe where possible numeric data should appear. If you click the cell, Excel displays an icon you can click to open a pop-up menu with the following options:

• Convert to Number ” Converts the number to a numeric format by removing the apostrophe. (You would choose this if you accidentally typed the apostrophe or changed your mind later.)

• Help on This Error ” Displays online help about entering numeric and text data.

• Ignore Error ” Keeps the apostrophe and removes the green triangle tag.

• Edit in Formula Bar ” Places the value in the formula bar with the insertion-point text cursor at the end of the data so that you can edit the data.

• Error Checking Options ” Displays the Options dialog box, shown in Figure 6.5, from which you can change the way Excel reacts to a possible data-entry error such as numeric values that you enter with an apostrophe.

  Figure 6.5. Specify how you want Excel to handle common errors.

  

• Show Formula Auditing Toolbar ” Displays a toolbar with which you can trace all cell references related to a formula to help you repair formulas that don't produce the results you expect them to.

Entering Numbers and Formulas

Excel accepts numeric values of all kinds. You can type positive numbers, negative numbers, numbers with decimal points, zero-leading numbers, numbers with dollar signs, percent signs, and even scientific notation (a shortcut for writing extremely large and small numbers).

If you type a number but see something similar to 3.04959E+16 appear in the cell, Excel converted your number to scientific notation to let you know that the cell is not wide enough to display the entire number in its regular form. Excel does not extend long numbers into adjacent cells. Excel converts most numbers that contain more than 12 digits to scientific notation.

Excel right-justifies numbers inside cells. You can change the format for a single cell, a group of cells, or the entire worksheet, as you will see in Hour 8, "Using Excel 2003."

Perhaps the most important task in learning Excel is mastering formulas. You never have to do math in Excel because Excel does it for you. The trick is getting your formulas correct. Hour 7, "Reconstructing and Editing Excel 2003 Worksheets," explains the ins and outs of Excel formulas. Later in this lesson, you get a chance to create a simple worksheet. As you will see, if your data changes, Excel automatically recalculates the entire worksheet for you. Therefore, once you create a worksheet and enter all the formulas, you can concentrate on the data and let Excel do the calculating.

Entering Dates and Times

Excel supports almost every national and international date and time format. Excel uses its AutoFormat feature to convert any date or time value that you type to a special internal number that represents the number of days since midnight, January 1, 1900. As with all Office 2003 products, Excel automatically displays all dates the user enters with four-digit year values by showing the full year. Although this strange internal date representation of days since 1-1-1900 might not make sense now, you use these values a lot to compute time between two or more dates. You can easily determine how many days an account is past due, for example, by subtracting the current date from the cell in the worksheet that contains the due date.

Excel uses a 24-hour clock to represent time values unless you specify a.m. or p.m. To convert p.m. times to 24-hour times, add 12 to all time values after 12:59 p.m. Thus, 7:54 p.m. is 19:54 on a 24-hour clock.

You can type any of the following date and time values to represent 6:15 p.m., July 4, 1976, or a combination of both 6:15 p.m. and July 4, 1976:

 July 4, 1976 4-Jul-76 6:15 p.m. 6:15 p.m. 18:15 07/04/76 18:15 07-04-76 18:15 

If you enter any of these date and time values, Excel converts them to a shortened format (such as 7/4/76 18:15). You can enter a date, a time value, or both. The shorter format often helps worksheet columns align better.