# Objective 5. Format Data and Cells

Excel has many options for displaying numbersthink of percentages, fractions, or money. Recall that Excel refers to the various ways to write numbers as number formats. Some common number formats are those used for reporting financial information like the purchases for a restaurant.

Formatting is the process of determining the appearance of cells and the overall layout of a worksheet. Formatting is accomplished through commands on both the Formatting toolbar and in the Format Cells dialog box.

Activity 1.10. Formatting Financial Numbers

The General format is the default format for a number that you type in a cell. Unless you apply a different number format to a cell, Excel will use the General format. The General format displays a number exactly as you type itwith three exceptions, as noted in the table in Figure 1.47.

Figure 1.47. Excel Number Formats

Number Format

Description

General

The General format is the default format for a number that you type in a cell. The General format displays a number exactly as you type itwith three exceptions:

1. Extremely long numbers might be abbreviated to a shorthand version of numbers called scientific notation, and long decimal values may be rounded. Even if this happens, Excel will still use the underlying value, not the displayed value, in any calculations.
2. Trailing zeros will not display in the General format. For example, if you type the number 456.0 the cell will display 456 with no zero or decimal point.
3. A decimal fraction entered without a number to the left of the decimal point will display with a zero. For example, if you type .456 you will see 0.456 displayed in the cell.

Number

Number format is used for the general display of noncurrency numbers. The default format has two decimal places, and you may choose to check the option for using a comma as a thousand separator. Negative numbers can display in red, be preceded by a minus sign, be enclosed in parentheses, or display both in red and in parentheses.

Currency

Currency format is used for general monetary values, and you can select from a list of worldwide currency symbolsthe U.S. dollar sign is the default symbol. When you click the Currency Style button on the Formatting toolbar, you apply the default Accounting format, which uses the U.S. dollar sign.

Accounting

Accounting format is similar to Currency format with two differencesthe dollar sign (or other currency symbol) always displays at the left edge of the cell, rather than flush against the first number. Thus, both dollar signs and numbers are vertically aligned in the same column. Also, Accounting format adds a blank space equal to the width of a close parenthesis on the right side of positive values to ensure that decimal points align if a column has both positive and negative numbers. This may cause misalignment with other formats that use different methods to indicate negative numbers.

Date

Date format provides many common ways to display dates. The default format in the Format Cells dialog box is month, day, and year, separated by a slash. The year displays as four digits by default, but may be changed in the Control Panel to a two-digit display.

Time

Time format provides many common ways to display time. The default format in the Format Cells dialog box is the hour and minute.

Percentage

Percentage format multiplies the cell value by 100 and displays the result with a percent sign. The default is two decimal places.

Fraction

Fraction format displays fractional amounts as actual fractions rather than as decimal values. The first three formats use single-digit, double-digit, and triple-digit numerators and denominators. For example, the single-digit format rounds to the nearest value that can be represented as a single-digit fraction.

Scientific

Scientific format displays numbers in scientific (exponential) notation. This is useful for extremely large numbers.

Text

Text format treats a number as if it were text. The number is left aligned like text.

Special

Special formats are used primarily with database functions. You can type postal codes, telephone numbers, and taxpayer ID numbers quickly without having to enter the punctuation.

Custom

Custom format is used to create your own number format. For example, perhaps your organization has a special format for invoice numbers.

 1. From the Format menu, click Cells to display the Format Cells dialog box, and then click the Number tab. Click each format under Category, and as you do so, take a moment to study the information about each Number format in the table in Figure 1.47. 2. In the Format Cells dialog box, click Cancel. Select the range B3:G3. On the Formatting toolbar, click the Currency Style button . The Currency Style button formats the number with the default Accounting format using the U.S. dollar sign. That is, it applies a thousand comma separator where appropriate, inserts a fixed U.S. dollar sign aligned at the left edge of the cell, applies two decimal places, and leaves a small amount of space at the right edge of the cell to accommodate a parenthesis for negative numbers. The default Accounting format, using the U.S. dollar sign, is easily accessible with one click using the Currency Style button. 3. Select the range B4:G7, and then on the Formatting toolbar, click the Comma Style button . The Comma Style inserts thousand comma separators where appropriate and applies two decimal places. This combination of formats is easily accessible with one click using the Comma Style button. Comma Style also leaves space at the right to accommodate a parenthesis for negative numbers. 4. Click cell B3. On the Standard toolbar, click the Format Painter button . With the Format Painter pointera small paint brush attached to the mouse pointerselect the range B8:G8. Compare your screen with Figure 1.48.   Figure 1.48. (This item is displayed on page 627 in the print version) The Currency Style is applied to the totals. When preparing worksheets with financial information, the first row of dollar amounts and the total rows of dollar amounts are formatted in the Currency Style; that is, with thousand comma separators, dollar signs, two decimal places, and space at the right to accommodate a parenthesis for negative numbers, if any. Rows that are not the first row or the total row should be formatted with the Comma Style. 5. On the Standard toolbar, click the Save button .   NoteUsing Negative Numbers in a Worksheet You can see a small amount of space to the right of each of the formatted number cells. The formats you applied allow this space in the event parentheses are needed to indicate negative numbers. If your worksheet contains negative numbers, display the Format Cells dialog box and select from among various formats to accommodate negative numbers. As you progress in your study of Excel, you will practice formatting negative numbers.

Activity 1.11. Formatting Text from the Dialog Box or the Toolbar

Format text in an Excel worksheet using techniques similar to those you use in other Microsoft Office applications such as Word. For example, you can change fonts; add emphasis by using bold, italic, and underline; and align text in the center, or at the left or right edge of a cell. Applying borders to cells is also useful to visually separate cells. In this activity, you will format the title to increase its visibility and inform the user of the worksheet's purpose as soon as the worksheet is displayed.

### Objective 6 Chart Data

GO! with Microsoft Office 2003 Brief (2nd Edition)
ISBN: 0131878646
EAN: 2147483647
Year: 2004
Pages: 448