Excel lets you enter the following types of information into a worksheet cell:
Numeric values, like the numbers 15,000, $29.95, and 33%
Text values, like the words Total, 1st Quarter, and 1820 Warren Avenue
Dates and times, like Feb-97, 11/19/63, or 1:00 PM
Comments to yourself or others, like This region leads in sales, or an appropriate recorded sound or voice message
Formulas, like =B5*1.081 or =SUM(B3:B7)
Hyperlinks to Internet sites or other documents
Electronic artwork, like clip art, scanned photographs, maps, and illustrations
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.
Entering Numeric Values
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:
Select the cell in which you want to store the number. (You can click the cell with the mouse or use the keyboard to achieve this.)
Type the numeric value. (Notice how the number appears both in the cell and on the formula bar.)
Press Enter or select a new cell to enter the number.
TIPUse Arrow Keys to Move from Cell to Cell
If 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.
Entering Text Values
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 ALSOFor 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:
Select the cell you want to store the text in. (You can click the cell with the mouse or use the keyboard to achieve this.)
Type the text value. (If you're entering a number, date, or time that should be stored as text, enter a single quotation mark before the value.)
Press Enter, or select a new cell to enter the text value.
TIPSpeed up Your Work with AutoComplete
If 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"
Entering Dates and Times
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.
Entering Comments
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.
Entering Formulas
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.
Using Cell References in Formulas
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.
Creating Cell References by Typing
Follow these steps to create a formula that includes cell references you type by hand:
Select the cell in which you want to place the formula.
Type an equal sign (=), and then type the formula you want to create. When it comes time to enter the cell references in your formula, type each cell name in column-first/row-next format. For example, to add cell B5 to cell C5, type =B5+C5.
Press Enter to store the formula. Excel calculates the result and displays it in the cell.
Creating Cell References by Using the Mouse
Follow these steps to create a formula that includes cell references you highlight with the mouse:
Select the cell in which you want to place the formula.
Type an equal sign (=) to start the formula. Click the first cell you want to place in the formula, and then type a mathematical operator. For example, click cell B5, and then press the plus (+) key to add B5+ to the formula bar. When you click the cell, a flashing border surrounds the cell, and its name appears on the formula bar. The border disappears when you type the operator.
Click the second cell you want to place in the formula. If the cell you want is not currently visible, use the scroll bars to locate it. If you want to include additional mathematical operators and cell names, you can add them now.
Press the Enter key to store the formula. Excel calculates the result and displays it in the cell.
Creating Cell References by Using Arrow Keys
Follow these steps to create a formula that includes cell references you highlight with the arrow keys (Right, Down, Up, Left) on the keyboard:
Select the cell in which you want to place the formula.
Type an equal sign (=) to start the formula. Use the arrow keys to highlight the first cell you want to include in the formula, and then type a mathematical operator. For example, highlight cell B5, and then press the plus (+) key to add B5+ to the formula bar. As you move the pointer around the worksheet, a flashing border surrounds the currently highlighted cell and its name appears in the formula bar.
Highlight the second cell that you want to place in the formula. If the cell you want is not currently visible, use arrow keys to locate it. (If you want to include additional mathematical operators and cell names, you can add them now.)
Press the Enter key to store the formula. Excel calculates the result and displays it in the cell.
Adding Artwork
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:
Select the cell in which you want to place the artwork. (Allow some room in neighboring cells to accommodate the image.)
Point to the Picture command on the Insert menu.
Choose the artwork type you want to use from the Picture submenu. You'll see the following options:
Clip Art A picture gallery containing thousands of pieces of electronic art for presentations, reports, and brochures.
From File An Open dialog box that lets you locate preexisting artwork on your system.
AutoShapes A toolbar that lets you add arrows, lines, and other shapes.
Organization Chart A utility that helps you build corporate organization charts.
WordArt A wizard and toolbar that helps you build creative banners, headlines, and text elements.
From Scanner or Camera A utility that helps you insert scanned images and photographs into worksheet cells.
Move or resize the artwork as desired in the worksheet.
If you want to delete the image later, select the artwork object you inserted and press Delete.
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.
Inserting a Background Graphic
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:
Display the worksheet you want to customize by adding background artwork.
Choose Sheet from the Format menu, and then click Background on the Sheet submenu. The Sheet Background dialog box appears.
Browse the folders on your hard disk or network to locate the electronic artwork you want to display as a background graphic. When you find and select it, click the Insert button to insert the graphic. (You'll find several useful images in the Program Files\Microsoft Office\Office\Bitmaps\Styles folder, in the Windows folder, and in your clip art folders.)
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.
