Formatting

 [ LiB ]

So far we've just been letting Excel decide how to display the information that we enter. But Excel allows you to choose many options that affect the format of the display. You need to know about a few of these to pass the exam:

• Controlling the display of numbers , dates, and percentages

• Changing fonts

• Changing colors

Numbers/Dates

In many cases, worksheets consist largely of numbers and dates: think of a financial statement, for example. So it only makes sense that Excel offers flexible ways to format this information.

Formatting Numbers

Format cells to display numbers to a specific number of decimal places, to display numbers with, without commas to indicate thousands.

Let's start with some numbers. Enter 12345.6789 in a worksheet cell . Excel displays it rounded off as 12345.68 , but all the digits are still there. You can use the Increase Decimal and Decrease Decimal buttons on the Formatting toolbar to change how many decimal places are displayed. By using these buttons , you can display the number in a variety of ways:

` 12346 12345.7 12345.68 12345.679 12345.6789 12345.67890 `

You can also choose whether or not to include commas in your numbers as separators for thousands. Click the Comma Style button on the Formatting toolbar to add commas (displaying the number as, for example, 12,345.68 ).

Excel also lets you fine-tune numeric formats using the Format Cells dialog box. To display this dialog box, right-click on the cell and select Format Cells, select Cells from the Format menu, or press Ctrl+1. This dialog box, shown in Figure 5.20, lets you fine-tune every aspect of numeric display. It gives you a handy preview of the results in the Sample area. Click OK to apply your changes.

Formatting Dates
 Format cells to display a date style.

When you enter dates into Excel, it automatically knows to display them as dates. For example, if you type 4/5/2003 into a cell, that's exactly what will be displayed. But there are many alternative date formats available. To see them, select a cell containing a date and open the Format Cells dialog box. You see that you can get anything from 5-Apr-03 to A out of the same date. Whatever format you choose for display, Excel still stores the entire date in your worksheet.

Formatting Currency
 Format cells to display a currency symbol.

Many worksheets contain numbers that should be displayed as currency: income, expenses, asset values, and so on. To display a number as currency, select the cell and click the Currency Style toolbar button on the Formatting toolbar, or select Style from the Format menu, choose the Currency style, and click OK.

 The currency style uses the appropriate symbol for the version of Windows you have installed. For example, if you're working with Japanese Windows, currency is displayed with a yen symbol instead of a dollar sign.

Formatting Percentages
 Format cells to display numbers as percentages.

It's also easy to format cells to display numbers as percentages. For example, enter .35 in a cell, and then click the Percent Style toolbar button on the Formatting toolbar. Excel displays the cell as 35% . You can also select the Percent style in the Format Cells dialog box or the Style dialog box.

Contents

So far we've been changing the values displayed but not the way that they look. Excel offers you complete control over the visual aspect of the value as well.

Changing Fonts
 Change cell content appearance: font sizes, font types. Apply formatting to cell contents such as: bold, italic, underline, double underline.

To change the font type or font size of any cell, open the Format Cells dialog box and click on the Font tab. This action displays the choices shown in Figure 5.21. As you change the font and size, the Preview window shows you the appearance of your chosen font. Click OK to apply your changes to the cell.

Figure 5.21. Setting the font for a cell.

You can also set font attributes such as bold, italic, underline, and double underline in this dialog box, but there are alternatives for most of these choices as well. For bold, click the Bold toolbar button on the Formatting toolbar, or press Ctrl+B. For italic, click the Italic toolbar button on the Formatting toolbar, or press Ctrl+I. For underline, click the Underline toolbar button on the Formatting toolbar, or press Ctrl+U.

Changing Colors
 Apply different colors to cell content, cell background.

If you'd like, it's easy to dress up your spreadsheets with a little color. To change the color of the font in a cell, click on the drop-down arrow next to the Font Color toolbar button on the Formatting toolbar. This action opens a palette of color choices. Click on the one you want to change the font color. To change the color of the cell's background, follow the same procedure with the Fill Color toolbar button on the Formatting toolbar.

Font and background color choices are also available in the Cell Format dialog box. You can change the font color on the Font tab or the background color on the Patterns tab.

Copying Formatting
 Copy the formatting from a cell, cell range to another cell, cell range.

Setting the format, font, and color options for a single cell can take quite a while. Fortunately, you don't have to repeat this process for every similar cell. Instead, you can copy all the formatting from a cell or range to another cell or range. Just follow these steps:

1. Select the cell or cell range that already has the formatting that you want to copy.

2. Click the Format Painter toolbar button on the Standard toolbar. (Click twice to lock the tool.)

3. Click on the cell that you want to have the same format, or click and drag across a range of cells.

Wrapping Text
 Apply text wrapping to contents within a cell.

When you enter a long piece of text in a cell, Excel displays it across several cells. If you prefer, you can make the text wrap in the same cell. To do so, select the cell and open the Format Cells dialog box. Select the Alignment tab and check the Wrap Text check box. Click OK. Figure 5.22 shows the result. Note how the nonwrapped text overflows its cell into the adjacent cells.

Alignment, Effects

For the last formatting section, we cover alignment and border customization.

Aligning Cell Contents
 Align contents in a cell, cell range: left, center, right, top, bottom.

The Alignment tab in the Format Cells dialog box also lets you specify where in a cell to place the text. You can use the Horizontal combo box to select left, center, or right alignment (as well as some other choices) and the Vertical combo box to choose whether to print text at the top or bottom of the cell.

You can also adjust the horizontal alignment with the Align Left, Center, and Align Right toolbar buttons on the Formatting toolbar.

Figure 5.23 shows the effects of various alignment choices.

Figure 5.23. Excel offers a variety of alignment choices.

 Center a title over a cell range.

You can also center text across an entire range of cells, rather than in a single cell. To do so, first type your text in a single cell. Then select a horizontal range of cells that includes the cell containing your text. Open the Format Cells dialog box and select the Alignment tab. In the Horizontal combo box, select Center Across Selection. Click OK. The dividing lines between the cells vanish , and your text is centered in the entire range.

Setting Text Orientation

You can even print cell text at any angle you like. Select a cell, open the Format Cells dialog box, and select the Alignment tab. You can use the Orientation control to set the angle of the text: sloping up, sloping down, and even vertical.

 Add border effects to a cell, cell range.

You can also fine-tune the borders for each cell in your worksheet. Select a cell or a range and then click the drop-down arrow next to the Borders toolbar button on the Formatting toolbar. This action gives you a palette with a number of built-in choices, such as a border only to the left or a border all the way around the cell. For even more choices, open the Format Cells dialog box and choose the Borders tab. The controls on this tab allow you to select which borders to draw, how thick they should be, and even which color to use for each individual border.

 [ LiB ]

ICDL Exam Cram 2
ISBN: 0789730928
EAN: 2147483647
Year: 2006
Pages: 142