Excel lets you enter the following types of information into a worksheet cell:
Each kind of information has its own formatting characteristics, meaning that Excel stores and displays each entry type differently. The following sections show you how to enter these values into a worksheet step by step.
CAUTION
When you use a slash to create a fraction in a numeric entry, be sure to include a leading zero (0) and a space if the fractional value is less than 1. If you don't, Excel interprets your fraction as a date. For example, Excel interprets the fraction 3/4 as the date March 4 unless you enter the fraction as 0 3/4. You'll learn more about date and time formatting in Chapter 17.
To enter a number in a cell, select the cell you want by using the mouse or keyboard, type the number, and press Enter. As you type, the number appears simultaneously in the active cell and on the formula bar above the worksheet. The formula bar serves as an editing scratch pad; if you make a mistake entering a long cell entry, you can click the formula bar and move the insertion point to locate the mistake in the entry and correct it, without having to retype the entry (and in so doing possibly making a new error). You can also double-click the active cell, and then move the insertion point within the cell to edit your entry. To the left of the formula bar is a Cancel button, which you can click to discard an unwanted entry on the formula bar (if you haven't already accepted the entry by pressing Enter), and an Enter button, which you can click to accept or lock in a revised entry. See Figure 15-4.
Figure 15-4. New values appear both on the formula bar and in the cell when you enter them.
A numeric value can be an integer (such as 32), a decimal number (such as 499.95), an integer fraction (such as 10 3/4), or a number in scientific notation (such as 4.09E+13). You can use several mathematical symbols in numbers, including plus (+), minus (-), percent (%), fraction (/), and exponent (E), as well as the dollar sign ($). If you enter a number that is too large to fit into a cell, Excel will automatically widen the cell to accommodate the number or adjust its display of the number by using scientific notation or by showing fewer decimal places. If Excel displays the number in scientific notation or places a row of number signs (#######) in the cell, you'll need to manually increase the column width to see the number in its entirety. (See "Changing Column Widths and Row Heights") Excel always stores the actual number you typed internally, no matter how it is displayed in the cell, and you can view this underlying value on the formula bar whenever the cell is active. By default (the preset arrangement), numeric values are aligned to the right edge of a cell.
Follow these steps to enter numeric values:
TIP
Use Arrow Keys to Move from Cell to CellIf you plan to enter additional numbers, you can use the arrow keys to enter a number and move to a new cell in one step. For instance, if you type a number and press the Down arrow key, the cell pointer moves down one line. The Left, Up, and Right arrow keys move the pointer one cell left, up, or right.
To enter a text value into a cell, select the cell, type your text, and press Enter. A text value or label can be any combination of alphanumeric characters, including uppercase and lowercase letters, numbers, and symbols. Excel recognizes text values and aligns them to the left margin of each cell. If no information appears in adjacent cells, Excel allows longer text entries to overlap the cells on the right. If the adjacent cells do contain information, the display of the text is cut off, or truncated; however, just as with a truncated value, Excel correctly stores the full text internally, and you can see it on the formula bar when the cell is active.
SEE ALSO
For details about changing column widths to make room for more information in cells, see "Changing Column Widths and Row Heights"
If you want Excel to store as a text value a value such as a numeric address, date, or part number, precede the value with a single quotation mark. For example, if you enter '55 in a cell, the number 55 will appear left-aligned in the cell without a quotation mark, and a quotation mark will appear on the formula bar to identify the number as a text value. Figure 15-5 shows an example of a cell that has overlapping text, a few cells that have truncated text, and several numeric text entries (that is, numbers stored internally as text).
Figure 15-5. Text values are left-aligned and can overlap adjacent cells if they don't contain information.
Follow these steps to enter text values:
TIP
Speed up Your Work with AutoCompleteIf Excel recognizes the pattern you're typing when you enter a sequence of characters, it will attempt to complete the pattern using a feature called AutoComplete. AutoComplete can be a major time-saver for you if you manage lists in Excel or find that you are entering the same values or functions over and over again. If you activate the AutoComplete feature while entering data, review the characters Excel inserts and, if they make sense to you, press Enter and move on. For information about turning off AutoComplete, see "Customizing Editing Options"
If you want to store a date or a time in a worksheet cell, you should use one of Excel's predefined date and time formats to enter the value so that Excel will recognize the number as a chronological entity and you won't need to format it again by using the Cells command on the Format menu. Excel stores identifiable dates and times internally as serial numbers, which makes them easier to use in functions and formulas and allows you to change the way they appear in worksheet cells. (If you're interested, a serial number is determined by counting the number of days, starting with 1, between the date you enter in a cell and January 1, 1900. However, these numbers don't appear on your worksheet—they're just internal markers that allow you to perform calculations with dates and times and quickly change how they look.) As you'll learn in Chapter 17, Excel lets you change the format of times and dates with a few simple commands.
SEE ALSO
For information about changing the format of date and time values, see "Changing Number Formats"
TIP
To enter the current date in the active cell, press Ctrl+; (the semicolon key). Excel will use the format m/d/yy for the date.
Table 15-2 shows you some of the time and date formats Excel supports. Experienced Excel users will notice two new date formats in the list, each having a four-digit placeholder for the current year (the patterns m/d/yyyy and d-mmm-yyyy). These date formats have been included to help manage the year 2000 problem, which arises in spreadsheets and accounting ledgers when the century portion of a numeric entry is recorded ambiguously. (For example, the date 3/14/02 could be read as March 14, 1902 or March 14, 2002.) To clarify which century you mean, use one of the new four-digit-year date formats.
Table 15-2. Popular Date and Time Formats Supported by Excel
Format | Pattern | Example |
---|---|---|
Date | m/d/yy | 10/1/99 |
Date | d-mmm-yy | 1-Oct-99 |
Date | d-mmm | 1-Oct |
Date | mmm-yy | Oct-99 |
Date (four-digit year) | m/d/yyyy | 10/1/1999 |
Date (four-digit year) | d-mmm-yyyy | 1-Oct-2002 |
Time | h:mm AM/PM | 10:15 PM |
Time | h:mm:ss AM/PM | 10:15:30 PM |
Time | h:mm | 22:15 |
Time | h:mm:ss | 22:15:30 |
Time | mm:ss.0 | 15:30.3 |
Combined | m/d/yy h:mm | 10/1/99 22:15 |
Figure 15-6 shows working examples of the most popular date and time formats and the procedure to enter them. You can change the format of a date or time by choosing Cells from the Format menu, clicking the Number tab, and then modifying the pattern used in the Date or Time categories.
Figure 15-6. Enter time and date values in one of these popular formats.
If you plan to share your Excel worksheets with other users, you might want to annotate a few important cells by using comments to provide instructions or highlight critical information. You can add a pop-up comment to a cell by highlighting the cell and choosing Comment from the Insert menu. Choosing the Comment command displays a pop-up window that has a blinking pointer and your name in it, so you can type a short note in the cell. (See Figure 15-7.) When you're finished typing the comment, click another cell to lock in the note.
NOTE
To change the name that appears when you enter a comment, choose Options from the Tools menu, click the General tab, and change the name in the User Name text box.
Active comments are identified by tiny red dots in the upper right corner of a cell. To display a comment in a worksheet, hold the mouse pointer over the annotated cell until a pop-up comment box appears. Remember that because comments are cell annotations, they exist in addition to the other entries in cells—they don't replace them. To delete an existing comment, select the cell containing the comment in the worksheet, choose Clear from the Edit menu, and click Comments on the Clear submenu.
Figure 15-7. The Comment command allows you to add a descriptive note to a cell.
Managing Your Comments
You can view all the comments in your workbook by enabling the Comments command on the View menu. The Comments command is a toggle that is either off or on; when it is enabled, all the comments in your workbook appear in pop-up windows; when it is disabled, comments only appear when you hold the mouse over the cells in which they reside. The Comments command also activates the Reviewing toolbar, shown in Figure 15-8, which contains a number of useful command buttons. To edit an existing comment, click the Edit Comment button on the toolbar, or right-click the cell containing the comment that needs editing and choose Edit Comment from the shortcut menu.
Figure 15-8. The Reviewing toolbar helps you manage the comments in your workbook.
Remember that because comments are cell annotations, they exist in addition to the other entries in cells—they don't replace them. To delete an existing comment, select the cell containing the comment in the worksheet, choose Clear from the Edit menu, and click Comments on the Clear submenu.
To compute a calculation in a worksheet, you could find your pocket calculator and work the problem. Better yet, enter a formula into an Excel cell and have that formula available instantly, from any cell in your worksheet, for future calculations and modification. A formula is an equation that calculates a new value from existing values. For example, a simple formula could calculate the total cost of an item by adding its price, sales tax, and shipping costs. Formulas can contain numbers, mathematical operators, cell references, and built-in equations called functions. One of Excel's great strengths is its vast collection of powerful and easy-to-use functions. Entering a formula in a cell is the key to unlocking this potent ally.
SEE ALSO
For detailed information about formula syntax, see "Building a Formula". For detailed information about Excel's collection of built-in functions, see "Using Built-In Functions"
All formulas in Excel begin with an equal sign (=). The equal sign signals the beginning of a mathematical operation and tells Excel to store the equation that follows as a formula. For example, the following formula calculates the sum of three numbers:
=10+20+30
Excel stores your formulas internally (you can see them on the formula bar), but it displays the result of each calculation in the cell in which you placed the formula. You can use the standard mathematical operators in a formula—addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^) as well as a few specialty operators described in Chapter 20. Figure 15-9 lists the steps to follow to enter a simple formula in a worksheet cell.
If your formula results in the message Error In Formula, you used an invalid operator or typed the formula incorrectly. Fix the formula in the formula bar, or edit it in the cell by selecting the cell and pressing F2.
NOTE
If you don't begin formulas with an equal sign (=), Excel will interpret the equation as a text value and the formula won't be calculated. If you make this common mistake, press F2 to edit the cell, press the Home key to move the insertion point to the beginning of the formula, type an equal sign (=), and then press Enter.
Figure 15-9. Entering a formula in a cell.
Formulas can also contain worksheet cell references—cell names such as A1 or B5—so that you can include the contents of cells in formulas and combine them in any way you choose. You can use cell references along with numbers, mathematical operators, and built-in functions. To specify cell references in formulas, you can type in their names, highlight them individually using the mouse, or highlight them individually using the keyboard. (The procedures follow.) For example, to add the contents of cell B5 to the contents of cell C5, you would create the following formula:
=B5+C5
Figure 15-10 shows the results of such a calculation.
Figure 15-10. To use the contents of cells in a formula, include cell references in the equation.
Follow these steps to create a formula that includes cell references you type by hand:
Follow these steps to create a formula that includes cell references you highlight with the mouse:
Follow these steps to create a formula that includes cell references you highlight with the arrow keys (Right, Down, Up, Left) on the keyboard:
After you enter your worksheet's basic facts and figures into Excel, you might want to spruce things up a bit by adding some electronic artwork such as clip art, scanned photographs, background images, organization charts, or hand-drawn illustrations. The basic mechanism for adding these items to worksheet cells is the same in all Office applications: click a command on the Picture submenu of the Insert menu.
Follow these steps to add a piece of electronic artwork to a worksheet:
TIP
If you're interested in geographic maps, you can also add a variety of map objects to your worksheet by using the Object command on the Insert menu and selecting the Microsoft Map object type on the Create New tab. You can customize these maps by adding regional information, labels, and data from your worksheet, to create reports that present the facts and figures in visually appealing ways.
If the worksheet you're creating will take center stage in a report or presentation, you might want to embellish it further by adding a subtle piece of artwork to the background. When you add background artwork, Excel places the image you specify behind (below) the current worksheet or chart. (The data in your worksheet cells will appear on top of the image.) If you specify a small pattern rather than a complete image, Excel automatically repeats, or tiles, the pattern to fill the entire worksheet or chart.
To add a bitmap, metafile, or other electronic image to the background of your worksheet, follow these steps:
TIP
Try to use simple, light-colored background images in your worksheets and charts so that the artwork doesn't overpower the text you're using for labels and numbers. Subtle, light gray images often work best.
Figure 15-11 shows what a piece of art named acsndstn.gif looks like as a background graphic in a worksheet. If you decide you don't want this image at some point, choose Delete Background from the Sheet submenu of the Format menu to remove it.
Figure 15-11. The Background command lets you add a background graphic to your worksheet or chart.