Objective 5. Format Data and Cells

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.

1.

Click cell A1, which contains the merged and centered text Tableware Purchases Dallas. On the Formatting toolbar, click the Font button arrow , and then compare your screen with Figure 1.49.

Figure 1.49.

2.

From the displayed list, press to move to fonts whose names begin with C, and then scroll as necessary to locate and then click Century Schoolbook. If Century Schoolbook is not available on your Font list, select Times New Roman.

The text Tableware Purchases - Dallas is formatted in the Century Schoolbook font. Century Schoolbook is a serif fonta font that includes small line extensions on the ends of the letters to guide the eye in reading from left to right.

3.

With cell A1 still selected, on the Formatting toolbar click the Font Size button arrow , and then from the displayed list, click 16.

Recall that fonts are measured in points, and that there are 72 points per inch.

4.

With cell A1 selected, on the Formatting toolbar click the Bold button .

The Bold font style is applied to your text. Font styles are used to emphasize text using bold, italic, and underline.

5.

With cell A1 still selected, display the Format menu, and then click Cells to display the Format Cells dialog box. Click the Font tab. Under Font, notice that Century Schoolbook is selected and that a preview of the font is also displayed under Preview.

6.

Under Font style, click Bold Italic, and notice that the Preview changes to reflect your selection. Click the Color arrow. From the displayed color palette, in the first row, click the sixth colorDark Blue. Click OK.

From the Format Cells dialog box, you can apply multiple formats at one time.

7.

With cell A1 still selected, on the Formatting toolbar click the Underline button .

8.

With cell A1 selected, on the Formatting toolbar click the Underline button again.

The Underline font style is removed from the text, but the Bold and Italic font styles remain.

9.

With cell A1 selected, on the Formatting toolbar click the Fill Color button arrow to display the color palette, and then compare your screen with Figure 1.50.

Figure 1.50.

10.

On the displayed color palette, in the last row, click the fifth colorLight Turquoise.

The background of the cellits fill colorchanges to Light Turquoise. If your printer does not print in color, the background colors will print as shades of gray, so select light colors to provide better contrast.

11.

Select the range B8:G8. On the Formatting toolbar, click the Borders button arrow to display commonly used border designs. Compare your screen with Figure 1.51.

Figure 1.51.

12.

On the displayed table of border options, in the second row, click the fourth border designTop and Double Bottom Border. Click any empty cell to deselect the range, and then compare your screen with Figure 1.52.

Figure 1.52.

This is a common way to apply borders to financial information. The single border indicates that calculations were performed on the numbers above, and the double border indicates that the information is complete.

13.

On the Standard toolbar, click the Save button .

Objective 6 Chart Data