Microsoft Excel 2016 BIBLE (2016)

IN THIS CHAPTER

1. Understanding the types of data you can use

2. Entering text and values into your worksheets

3. Entering dates and times into your worksheets

4. Modifying and editing information

5. Using built-in number formats

This chapter describes what you need to know about entering and modifying data in your worksheets. As you see, Excel doesn't treat all data equally. Therefore, you need to learn about the various types of data that you can use in an Excel worksheet.

Exploring Data Types

An Excel workbook file can hold any number of worksheets, and each worksheet is made up of more than 17 billion cells. A cell can hold any of three basic types of data:

· A numeric value

· Text

· A formula

A worksheet can also hold charts, diagrams, pictures, buttons, and other objects. These objects aren't contained in cells. Instead, they reside on the worksheet's draw layer, which is an invisible layer on top of each worksheet.

Chapter 23, “Enhancing Your Work with Pictures and Drawings,” discusses some of the items you can place on the draw layer.

Numeric values

Numeric values represent a quantity of some type: sales amounts, number of employees, atomic weights, test scores, and so on. Values also can be dates (Feb 26, 2015) or times (such as 3:24 a.m.).

Excel can display values in many different formats. In the “Applying Number Formatting” section, later in this chapter, you see how different format options can affect the display of numeric values.

Excel's Numeric Limitations

You may be curious about the types of values that Excel can handle. In other words, how large can a number be? And how accurate are large numbers?

Excel's numbers are precise up to 15 digits. For example, if you enter a large value, such as 123,456,789,123,456,789 (18 digits), Excel actually stores it with only 15 digits of precision. This 18-digit number displays as 123,456,789,123,456,000. This precision may seem quite limiting, but in practice, it rarely causes any problems.

One situation in which the 15-digit accuracy can cause a problem is when entering credit card numbers. Most credit card numbers are 16 digits, but Excel can handle only 15 digits, so it substitutes a zero for the last credit card digit. Even worse, you may not even realize that Excel made the card number invalid. The solution? Enter the credit card numbers as text. The easiest way is to preformat the cell as Text. (Choose Home Number, and choose Text from the Number Format drop-down list.) Or you can precede the credit card number with an apostrophe. Either method prevents Excel from interpreting the entry as a number.

Here are some of Excel's other numeric limits:

· Largest positive number: 9.9E+307

· Smallest negative number: –9.9E+307

· Smallest positive number: 1E–307

· Largest negative number: –1E–307

These numbers are expressed in scientific notation. For example, the largest positive number is “9.9 times 10 to the 307th power” — in other words, 99 followed by 306 zeros. Keep in mind, though, that this number has only 15 digits of precision.

Text entries

Most worksheets also include text in some of the cells. Text can serve as data (for example, a list of employee names), labels for values, headings for columns, or instructions about the worksheet. Text is often used to clarify what the values in a worksheet mean or where the numbers came from.

Text that begins with a number is still considered text. For example, if you type 12 Employees into a cell, Excel considers the entry to be text rather than a numeric value. Consequently, you can't use this cell for numeric calculations. If you need to indicate that the number 12 refers to employees, enter 12 into a cell and then type Employees into the cell to the right.

Formulas

Formulas are what make a spreadsheet a spreadsheet. Excel enables you to enter flexible formulas that use the values (or even text) in cells to calculate a result. When you enter a formula into a cell, the formula's result appears in the cell. If you change any of the cells used by a formula, the formula recalculates and shows the new result.

Formulas can be simple mathematical expressions, or they can use some of the powerful functions that are built into Excel. Figure 2.1 shows an Excel worksheet set up to calculate a monthly loan payment. The worksheet contains values, text, and formulas. The cells in column A contain text. Column B contains four values and two formulas. The formulas are in cells B6 and B10. Column D, for reference, shows the actual contents of the cells in column B.

Figure 2.1 You can use values, text, and formulas to create useful Excel worksheets.

This workbook, named loan payment calculator.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible.

You can find out much more about formulas in Part II, “Working with Formulas and Functions.”

Entering Text and Values into Your Worksheets

To enter a numeric value into a cell, move the cell pointer to the appropriate cell, type the value, and then press Enter or one of the arrow navigation keys. The value is displayed in the cell and appears in the Formula bar when the cell is selected. You can include decimal points and currency symbols when entering values, along with plus signs, minus signs, and commas (to separate thousands). If you precede a value with a minus sign or enclose it in parentheses, Excel considers it to be a negative number.

Entering text into a cell is just as easy as entering a value: activate the cell, type the text, and then press Enter or a navigation key. A cell can contain a maximum of about 32,000 characters — more than enough to store a typical chapter in this book. Even though a cell can hold a huge number of characters, you'll find that it's not possible to actually display all these characters.

Tip

If you type an exceptionally long text entry into a cell, the Formula bar may not show all the text. To display more of the text in the Formula bar, click the bottom of the Formula bar and drag down to increase the height (see Figure 2.2). Also useful is the Ctrl+Shift+U keyboard shortcut. Pressing this key combination toggles the height of the Formula bar to show either one row or the previous size.

Figure 2.2 The Formula bar, expanded in height to show more information in the cell.

What happens when you enter text that's longer than its column's current width? If the cells to the immediate right are blank, Excel displays the text in its entirety, appearing to spill the entry into adjacent cells. If an adjacent cell isn't blank, Excel displays as much of the text as possible. (The full text is contained in the cell; it's just not displayed.) If you need to display a long text string in a cell that's adjacent to a nonblank cell, you have a few choices:

· Edit your text to make it shorter.

· Increase the width of the column (drag the border in the column letter display).

· Use a smaller font.

· Wrap the text within the cell so that it occupies more than one line. Choose Home Alignment Wrap Text to toggle wrapping on and off for the selected cell or range.

Entering Dates and Times into Your Worksheets

Excel treats dates and times as special types of numeric values. Dates and times are values that are formatted so that they appear as dates or times. If you work with dates and times, you need to understand Excel's date and time system.

Entering date values

Excel handles dates by using a serial number system. The earliest date that Excel understands is January 1, 1900. This date has a serial number of 1. January 2, 1900, has a serial number of 2, and so on. This system makes it easy to deal with dates in formulas. For example, you can enter a formula to calculate the number of days between two dates.

Most of the time, you don't have to be concerned with Excel's serial number date system. You can simply enter a date in a common date format, and Excel takes care of the details behind the scenes. For example, if you need to enter June 1, 2016, you can enter the date by typing June 1, 2016 (or use any of several different date formats). Excel interprets your entry and stores the value 42522, which is the serial number for that date.

Note

The date examples in this book use the U.S. English system. Your Windows regional settings will affect the way Excel interprets a date you've entered. For example, depending on your regional date settings, June 1, 2016 may be interpreted as text rather than a date. In such a case, you need to enter the date in a format that corresponds to your regional date settings — for example, 1 June, 2016.

For more information about working with dates, see Chapter 12, “Working with Dates and Times.”

Entering time values

When you work with times, you extend Excel's date serial number system to include decimals. In other words, Excel works with times by using fractional days. For example, the date serial number for June 1, 2016, is 42522. Noon on June 1, 2016 (halfway through the day), is represented internally as 42522.5 because the time fraction is added to the date serial number to get the full date/time serial number.

Again, you normally don't have to be concerned with these serial numbers or fractional serial numbers for times. Just enter the time into a cell in a recognized format. In this case, type June 1, 2016 12:00.

See Chapter 12 for more information about working with time values.

Modifying Cell Contents

After you enter a value or text into a cell, you can modify it in several ways:

· Delete the cell's contents.

· Replace the cell's contents with something else.

· Edit the cell's contents.

Note

You can also modify a cell by changing its formatting. However, formatting a cell affects only a cell's appearance. Formatting doesn't affect the cell's contents. Later sections in this chapter cover formatting.

Deleting the contents of a cell

To delete the contents of a cell, just click the cell and press the Delete key. To delete more than one cell, select all the cells that you want to delete and then press Delete. Pressing Delete removes the cell's contents but doesn't remove any formatting (such as bold, italic, or a different number format) that you may have applied to the cell.

For more control over what gets deleted, you can choose Home Editing Clear. This command's drop-down list has five choices:

· Clear All: Clears everything from the cell — its contents, its formatting, and its cell comment (if it has one).

· Clear Formats: Clears only the formatting and leaves the value, text, or formula.

· Clear Contents: Clears only the cell's contents and leaves the formatting. This has the same effect as pressing Delete.

· Clear Comments: Clears the comment (if one exists) attached to the cell.

· Clear Hyperlinks: Removes hyperlinks contained in the selected cells. The text and formatting remain, so the cell still looks like it has a hyperlink, but it no longer functions as a hyperlink.

· Remove Hyperlinks: Removes hyperlinks in the selected cells, including the cell formatting.

Note

Clearing formats doesn't clear the background colors in a range that has been designated as a table unless you've replaced the table style background colors manually. See Chapter 5, “Introducing Tables,” for more about tables.

Replacing the contents of a cell

To replace the contents of a cell with something else, just activate the cell and type your new entry, which replaces the previous contents. Any formatting applied to the cell remains in place and is applied to the new content.

You can also replace cell contents by dragging and dropping or by pasting data from the Clipboard. In both cases, the cell formatting will be replaced by the format of the new data. To avoid pasting formatting, choose Home Clipboard Paste Values (V), or Home Clipboard Paste Formulas (F).

Editing the contents of a cell

If the cell contains only a few characters, replacing its contents by typing new data usually is easiest. However, if the cell contains lengthy text or a complex formula and you need to make only a slight modification, you probably want to edit the cell rather than re-enter information.

When you want to edit the contents of a cell, you can use one of the following ways to enter cell-edit mode:

· Double-click the cell to edit the cell contents directly in the cell.

· Select the cell and press F2 to edit the cell contents directly in the cell.

· Select the cell that you want to edit and then click inside the Formula bar to edit the cell contents in the Formula bar.

You can use whichever method you prefer. Some people find editing directly in the cell easier; others prefer to use the Formula bar to edit a cell.

Note

The Advanced tab of the Excel Options dialog box contains a section called Editing Options. These settings affect how editing works. (To access this dialog box, choose File Options.) If the Allow Editing Directly in Cells option isn't enabled, you can't edit a cell by double-clicking. In addition, pressing F2 allows you to edit the cell in the Formula bar (not directly in the cell).

All these methods cause Excel to go into edit mode. (The word Edit appears at the left side of the status bar at the bottom of the window.) When Excel is in edit mode, the Formula bar enables two icons: Cancel (the X) and Enter (the check mark). Figure 2.3 shows these two icons. Clicking the Cancel icon cancels editing without changing the cell's contents. (Pressing Esc has the same effect.) Clicking the Enter icon completes the editing and enters the modified contents into the cell. (Pressing Enter has the same effect.)

Figure 2.3 When you're editing a cell, the Formula bar enables two new icons: Cancel (X) and Enter (check mark).

When you begin editing a cell, the insertion point appears as a vertical bar, and you can perform the following tasks:

· Add new characters at the location of the insertion point. Move the insertion point by

· Using the navigation keys to move within the cell

· Pressing Home to move the insertion point to the beginning of the cell

· Pressing End to move the insertion point to the end of the cell

· Select multiple characters. Press Shift while you use the navigation keys.

· Select characters while you're editing a cell. Use the mouse. Just click and drag the mouse pointer over the characters that you want to select.

Learning some handy data-entry techniques

You can simplify the process of entering information into your Excel worksheets and make your work go quite a bit faster by using a number of useful tricks, described in the following sections.

Automatically moving the cell pointer after entering data

By default, Excel automatically moves the cell pointer to the next cell down when you press the Enter key after entering data into a cell. To change this setting, choose File Options and click the Advanced tab (see Figure 2.4). The check box that controls this behavior is labeled After Pressing Enter, Move Selection. If you enable this option, you can choose the direction in which the cell pointer moves (down, left, up, or right).

Figure 2.4 You can use the Advanced tab in Excel Options to select a number of helpful input option settings.

Your choice is completely a matter of personal preference. I prefer to keep this option turned off. When entering data, I use the navigation keys rather than the Enter key. (See the next section.)

Using navigation keys instead of pressing Enter

Instead of pressing the Enter key when you're finished making a cell entry, you can use any of the navigation keys to complete the entry. Not surprisingly, these navigation keys send you in the direction that you indicate. For example, if you're entering data in a row, press the right-arrow (→) key rather than Enter. The other arrow keys work as expected, and you can even use PgUp and PgDn.

Selecting a range of input cells before entering data

When a range of cells is selected, Excel automatically moves the cell pointer to the next cell in the range when you press Enter. If the selection consists of multiple rows, Excel moves down the column; when it reaches the end of the selection in the column, it moves to the first selected cell in the next column.

To skip a cell, just press Enter without entering anything. To go backward, press Shift+Enter. If you prefer to enter the data by rows rather than by columns, press Tab rather than Enter. Excel continues to cycle through the selected range until you select a cell outside the range.

Using Ctrl+Enter to place information into multiple cells simultaneously

If you need to enter the same data into multiple cells, Excel offers a handy shortcut. Select all the cells that you want to contain the data, enter the value, text, or formula, and then press Ctrl+Enter. The same information is inserted into each cell in the selection.

Entering decimal points automatically

If you need to enter lots of numbers with a fixed number of decimal places, Excel has a useful tool that works like some old adding machines. Access the Excel Options dialog box and click the Advanced tab. Select the Automatically Insert a Decimal Point check box and make sure that the Places box is set for the correct number of decimal places for the data you need to enter.

When this option is set, Excel supplies the decimal points for you automatically. For example, if you specify two decimal places, entering 12345 into a cell is interpreted as 123.45. To restore things to normal, just clear the Automatically Insert a Decimal Point check box in the Excel Options dialog box. Changing this setting doesn't affect any values that you already entered.

Caution

The fixed decimal places option is a global setting and applies to all workbooks (not just the active workbook). If you forget that this option is turned on, you can easily end up entering incorrect values — or cause some major confusion if someone else uses your computer.

Using AutoFill to enter a series of values

The Excel AutoFill feature makes inserting a series of values or text items in a range of cells easy. It uses the AutoFill handle (the small box at the lower right of the active cell). You can drag the AutoFill handle to copy the cell or automatically complete a series.

Figure 2.5 shows an example. I entered 1 into cell A1 and 3 into cell A2. Then I selected both cells and dragged down the fill handle to create a linear series of odd numbers. The figure also shows an icon that, when clicked, displays some additional AutoFill options. This icon appears only if the Show Paste Option Button When Content Is Pasted option is selected in the Advanced table of the Excel Options dialog box.

Figure 2.5 This series was created by using AutoFill.

Tip

If you drag the AutoFill handle while you press and hold the right mouse button, Excel displays a shortcut menu with additional fill options. You can also use Home Editing Fill for even more control over automatically filling a range.

Using AutoComplete to automate data entry

The Excel AutoComplete feature makes entering the same text into multiple cells easy. With AutoComplete, you type the first few letters of a text entry into a cell, and Excel automatically completes the entry based on other entries that you already made in the column. Besides reducing typing, this feature ensures that your entries are spelled correctly and are consistent.

Here's how it works. Suppose that you're entering product information into a column. One of your products is named Widgets. The first time that you enter Widgets into a cell, Excel remembers it. Later, when you start typing Widgets in that same column, Excel recognizes it by the first few letters and finishes typing it for you. Just press Enter, and you're done. To override the suggestion, just keep typing.

AutoComplete also changes the case of letters for you automatically. If you start entering widgets (with a lowercase w) in the second entry, Excel makes the w uppercase to be consistent with the previous entry in the column.

Tip

You also can access a mouse-oriented version of AutoComplete by right-clicking the cell and choosing Pick from Drop-Down List from the shortcut menu. Excel then displays a drop-down box that has all the text entries in the current column, and you just click the one that you want.

Keep in mind that AutoComplete works only within a contiguous column of cells. If you have a blank row, for example, AutoComplete identifies only the cell contents below the blank row.

If you find the AutoComplete feature distracting, you can turn it off by using the Advanced tab of the Excel Options dialog box. Remove the check mark from the check box labeled Enable AutoComplete for Cell Values.

Forcing text to appear on a new line within a cell

If you have lengthy text in a cell, you can force Excel to display it in multiple lines within the cell: press Alt+Enter to start a new line in a cell.

When you add a line break, Excel automatically changes the cell's format to Wrap Text. But unlike normal text wrap, your manual line break forces Excel to break the text at a specific place within the text, which gives you more precise control over the appearance of the text than if you rely on automatic text wrapping.

Tip

To remove a manual line break, edit the cell and press Delete when the insertion point is located at the end of the line that contains the manual line break. You won't see any symbol to indicate the position of the manual line break, but the text that follows it will move up when the line break is deleted.

Using AutoCorrect for shorthand data entry

You can use the AutoCorrect feature to create shortcuts for commonly used words or phrases. For example, if you work for a company named Consolidated Data Processing Corporation, you can create an AutoCorrect entry for an abbreviation, such as cdp. Then, whenever you type cdp, Excel automatically changes the text to Consolidated Data Processing Corporation.

Excel includes quite a few built-in AutoCorrect terms (mostly to correct common misspellings), and you can add your own. To set up your custom AutoCorrect entries, access the Excel Options dialog box (choose File Options) and click the Proofing tab. Then click the AutoCorrect Options button to display the AutoCorrect dialog box. In the dialog box, click the AutoCorrect tab, check the option labeled Replace Text as You Type, and then enter your custom entries. (Figure 2.6 shows an example.) You can set up as many custom entries as you like. Just be careful not to use an abbreviation that might appear normally in your text.

Figure 2.6 AutoCorrect allows you to create shorthand abbreviations for text you enter often.

Tip

Excel shares your AutoCorrect list with other Microsoft Office applications. For example, any AutoCorrect entries you created in Word also work in Excel.

Entering numbers with fractions

Most of the time, you'll want noninteger values to be displayed with decimal points. But Excel can also display values with fractions. To enter a fractional value into a cell, leave a space between the whole number and the fraction. For example, to enter 6 ⁷/₈, enter 6 7/8 and then press Enter. When you select the cell, 6.875 appears in the Formula bar, and the cell entry appears as a fraction. If you have a fraction only (for example, ¹/₈), you must enter a zero first, like this — 0 1/8 — or Excel will likely assume that you're entering a date. When you select the cell and look at the Formula bar, you see 0.125. In the cell, you see ¹/₈.

Using a form for data entry

Many people use Excel to manage lists in which the information is arranged in rows. Excel offers a simple way to work with this type of data through the use of a data entry form that Excel can create automatically. This data form works with either a normal range of data or with a range that has been designated as a table. (Choose Insert Tables Table.) Figure 2.7 shows an example.

Figure 2.7 Excel's built-in data form can simplify many data-entry tasks.

Unfortunately, the command to access the data form is not on the Ribbon. To use the data form, you must add it to your Quick Access toolbar or add it to the Ribbon. Here's how to add this command to your Quick Access toolbar:

1. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar. The Quick Access Toolbar panel of the Excel Options dialog box appears.

2. In the Choose Commands From drop-down list, choose Commands Not in the Ribbon.

3. In the list box on the left, select Form.

4. Click the Add button to add the selected command to your Quick Access toolbar.

5. Click OK to close the Excel Options dialog box.

After you perform these steps, a new icon appears on your Quick Access toolbar.

To use a data entry form, follow these steps:

1. Arrange your data so that Excel can recognize it as a table by entering headings for the columns into the first row of your data entry range.

2. Select any cell in the table and click the Form button on your Quick Access toolbar. Excel displays a dialog box customized to your data (refer to Figure 2.7).

3. Fill in the information. Press Tab to move between the text boxes. If a cell contains a formula, the formula result appears as text (not as an edit box). In other words, you can't modify formulas using the data entry form.

4. When you complete the data form, click the New button. Excel enters the data into a row in the worksheet and clears the dialog box for the next row of data.

You can also use the form to edit existing data.

Entering the current date or time into a cell

If you need to date-stamp or time-stamp your worksheet, Excel provides two shortcut keys that do this task for you:

· Current date: Ctrl+; (semicolon)

· Current time: Ctrl+Shift+; (semicolon)

The date and time are from the system time in your computer. If the date or time isn't correct in Excel, use the Windows Control Panel to make the adjustment.

Note

When you use either of these shortcuts to enter a date or time into your worksheet, Excel enters a static value into the worksheet. In other words, the date or time entered doesn't change when the worksheet is recalculated. In most cases, this setup is probably what you want, but you should be aware of this limitation. If you want the date or time display to update, use one of these formulas:

1. =TODAY()

2. =NOW()

Applying Number Formatting

Number formatting refers to the process of changing the appearance of values contained in cells. Excel provides a variety of number formatting options. In the following sections, you see how to use many of Excel's formatting options to quickly improve the appearance and readability of your worksheets.

Tip

The formatting that you apply works with the selected cell or cells. Therefore, you need to select the cell (or range of cells) before applying the formatting. Also remember that changing the number format does not affect the underlying value. Number formatting affects only the appearance

Values that you enter into cells normally are unformatted. In other words, they simply consist of a string of numerals. Typically, you want to format the numbers so that they're easier to read or are more consistent in terms of the number of decimal places shown.

Figure 2.8 shows a worksheet that has two columns of values. The first column consists of unformatted values. The cells in the second column are formatted to make the values easier to read. The third column describes the type of formatting applied.

Figure 2.8 Use numeric formatting to make it easier to understand what the values in the worksheet represent.

This workbook is available on this book's website at www.wiley.com/go/excel2016bible. The file is named number formatting.xlsx.

Tip

If you move the cell pointer to a cell that has a formatted value, the Formula bar displays the value in its unformatted state because the formatting affects only the way the value appears in the cell — not the actual value contained in the cell. There are a few exceptions, however. When you enter a date or a time, Excel always displays the value as a date or a time, even though it's stored internally as a value. Also, values that use the Percentage format display with a percent sign in the Formula bar.

Using automatic number formatting

Excel is able to perform some formatting for you automatically. For example, if you enter 12.2% into a cell, Excel knows that you want to use a percentage format and applies it for you automatically. If you use commas to separate thousands (such as 123,456), Excel applies comma formatting for you. And if you precede your value with a dollar sign, the cell is formatted for currency (assuming that the dollar sign is your system currency symbol).

Tip

A handy default feature in Excel makes entering percentage values into cells easier. If a cell is formatted to display as a percent, you can simply enter a normal value (for example, 12.5 for 12.5%). To enter values less than 1%, precede the value with a zero (for example, 0.52 for 0.52%). If this automatic percent entry feature isn't working (or if you prefer to enter the actual value for percents), access the Excel Options dialog box and click the Advanced tab. In the Editing Options section, locate the Enable Automatic Percent Entry check box and add or remove the check mark.

Formatting numbers by using the Ribbon

The Home Number group in the Ribbon contains controls that let you quickly apply common number formats.

The Number Format drop-down list contains 11 common number formats (see Figure 2.9). Additional options in the Home Number group include an Accounting Number Format drop-down list (to select a currency format), a Percent Style button, and a Comma Style button. The group also contains a button to increase the number of decimal places and another to decrease the number of decimal places.

Figure 2.9 You can find number formatting commands in the Number group of the Home tab.

When you select one of these controls, the active cell takes on the specified number format. You also can select a range of cells (or even entire rows or columns) before clicking these buttons. If you select more than one cell, Excel applies the number format to all the selected cells.

Using shortcut keys to format numbers

Another way to apply number formatting is to use shortcut keys. Table 2.1 summarizes the shortcut-key combinations that you can use to apply common number formatting to the selected cells or range. Notice that these Ctrl+Shift characters are located together, in the upper left of your keyboard.

Table 2.1Number Formatting Keyboard Shortcuts

Formatting numbers by using the Format Cells dialog box

In most cases, the number formats that are accessible from the Number group on the Home tab are just fine. Sometimes, however, you want more control over how your values appear. Excel offers a great deal of control over number formats through the use of the Format Cells dialog box, shown in Figure 2.10. For formatting numbers, you need to use the Number tab.

Figure 2.10 When you need more control over number formats, use the Number tab of the Format Cells dialog box.

You can bring up the Format Cells dialog box in several ways. Start by selecting the cell or cells that you want to format and then do one of the following:

· Choose Home Number and click the small dialog box launcher icon (in the lower-right corner of the Number group).

· Choose Home Number, click the Number Format drop-down list, and choose More Number Formats from the drop-down list.

· Right-click the cell and choose Format Cells from the shortcut menu.

· Press Ctrl+1.

The Number tab of the Format Cells dialog box displays 12 categories of number formats. When you select a category from the list box, the right side of the tab changes to display options appropriate to that category.

The Number category has three options that you can control: the number of decimal places displayed, whether to use a thousands separator, and how you want negative numbers displayed. The Negative Numbers list box has four choices (two of which display negative values in red), and the choices change depending on the number of decimal places and whether you choose to separate thousands.

The top of the tab displays a sample of how the active cell will appear with the selected number format (visible only if a cell with a value is selected). After you make your choices, click OK to apply the number format to all the selected cells.

When Numbers Appear to Add Incorrectly

Applying a number format to a cell doesn't change the value — it only changes how the value appears in the worksheet. For example, if a cell contains 0.874543, you may format it to appear as 87%. If that cell is used in a formula, the formula uses the full value (0.874543), not the displayed value (87%).

In some situations, formatting may cause Excel to display calculation results that appear incorrect, such as when totaling numbers with decimal places. For example, if values are formatted to display two decimal places, you may not see the actual numbers used in the calculations. But because Excel uses the full precision of the values in its formula, the sum of the two values may appear to be incorrect.

Several solutions to this problem are available. You can format the cells to display more decimal places. You can use the ROUND function on individual numbers and specify the number of decimal places Excel should round to. Or you can instruct Excel to change the worksheet values to match their displayed format. To do so, access the Excel Options dialog box and click the Advanced tab. Check the Set Precision as Displayed check box (located in the When Calculating This Workbook section).

Caution

Selecting the Precision as Displayed option changes the numbers in your worksheets to permanently match their appearance onscreen. This setting applies to all sheets in the active workbook. Most of the time, this option is not what you want. Make sure that you understand the consequences of using the Set Precision as Displayed option.

Chapter 10, “Introducing Formulas and Functions,” discusses ROUND and other built-in functions.

The following are the number format categories, along with some general comments:

· General: The default format; it displays numbers as integers, as decimals, or in scientific notation if the value is too wide to fit in the cell.

· Number: Enables you to specify the number of decimal places, whether to use a comma to separate thousands, and how to display negative numbers (with a minus sign, in red, in parentheses, or in red and in parentheses).

· Currency: Enables you to specify the number of decimal places, choose a currency symbol, and specify how to display negative numbers (with a minus sign, in red, in parentheses, or in red and in parentheses). This format always uses a comma to separate thousands.

· Accounting: Differs from the Currency format in that the currency symbols always align vertically.

· Date: Enables you to choose from several different date formats.

· Time: Enables you to choose from several different time formats.

· Percentage: Enables you to choose the number of decimal places and always displays a percent sign.

· Fraction: Enables you to choose from among nine fraction formats.

· Scientific: Displays numbers in exponential notation (with an E): 2.00E+05 = 200,000; 2.05E+05 = 205,000. You can choose the number of decimal places to display to the left of E. The second example can be read as “2.05 times 10 to the fifth.”

· Text: When applied to a value, causes Excel to treat the value as text (even if it looks like a number). This feature is useful for such items as part numbers and credit card numbers.

· Special: Contains additional number formats. In the U.S. version of Excel, the additional number formats are Zip Code, Zip Code +4, Phone Number, and Social Security Number.

· Custom: Enables you to define custom number formats that aren't included in any other category.

Tip

If a cell displays a series of hash marks (such as #########), it usually means that the column isn't wide enough to display the value in the number format that you selected. Either make the column wider or change the number format. Hashmarks also indicate a negative time value or an invalid date (that is, a date prior to January 1, 1900).

Adding your own custom number formats

Sometimes you may want to display numerical values in a format that isn't included in any of the other categories. If so, the answer is to create your own custom format.

Excel provides you with a great deal of flexibility in creating number formats — so much so that I've devoted an entire chapter (Chapter 25, “Using Custom Number Formats”) to this topic .