Formatting with Styles

You can spend hours formatting a worksheet, applying multiple formats to various cells and tweaking the appearance of entries until your worksheet looks just right. Fortunately, Excel provides a feature that enables you to apply several formatting attributes with a single command or keystroke: styles .

A style is a group of cell formatting options that you can apply to a cell or cell block. If you change the style's definition later, that change affects the formatting of all cells formatted with that style. Each style contains specifications for one or more of the following options:

• Number Format controls the appearance of values, such as dollar values and dates.

• Font specifies the typestyle, type size, and any attributes for text contained in the cell.

• Alignment aligns entries in relation to the left, right, top, and bottom borders inside the cell.

• Border specifies the border placement and line style options for the cell.

• Pattern adds specified shading to the cell.

• Protection allows you to protect or unprotect a cell. See Chapter 10 for details about protecting cells.

Excel provides the following six default styles:

Normal: The default style. Number is set to 0, Font to Arial, Size to 10 point, Alignment of numbers is right, and Alignment of text is left, No Border, No Pattern, and Protection is set to locked.

Comma: Commas are used to separate thousands, millions, and so on, and two decimal places are displayed.

Comma [0]: Commas are used to separate thousands, millions, and so on, but no decimal places are displayed.

Currency: Dollar sign is added to value; commas are used to separate thousands, millions, and so on; and two decimal places are displayed. Negative numbers are displayed in parentheses.

Currency [0]: Dollar sign is added to value; commas are used to separate thousands, millions, and so on; but no decimal places are displayed. Negative numbers are displayed in parentheses.

Percent: Percentage sign (%) is tacked on to the end of the value.

The following sections show you how to apply these styles to selected cells, create your own styles, and save styles in a worksheet template.

Applying Existing Styles

You can apply most of Excel's default styles by selecting the cell or cell range and then clicking the appropriate button in the Formatting toolbar. For example, to apply the Currency format, select the cells, and then click the Currency Style button (the button with the dollar sign on it). To apply the Percentage format, click the Percent Style button. To apply the Comma format, click the Comma Style button.

To apply an Excel style that does not have a button or to apply a style that you will create in the next section, perform the following steps:

1. Select the cell or range to which you want to apply the style.

2. Open the Format menu and choose Style . The Style dialog box appears, as shown in Figure 7.10.

   Figure 7.10. Use the Style dialog box to apply styles.

   

3. Click the down arrow to the right of the Style Name drop-down list, and click the desired style. Excel applies the style to the selected cell or range.

If you make much use of styles, consider placing the Styles drop-down list on the Formatting toolbar. Here's what you do:

1. Open the Tools menu and choose Customize . The Customize dialog box appears.

2. Click the Commands tab.

3. In the Categories list, click Format . The Format options appear in the Commands list.

4. Scroll down the Commands list, and click the Style entry that has a drop-down list next to it. See Figure 7.11.

   Figure 7.11. You can add a Style drop-down list to any toolbar.

   

5. Drag the Style entry you just selected up to a blank area on the Formatting toolbar, and release the mouse button to drop it in place. The Style drop-down list appears on the toolbar.

Now, whenever you want to apply a style to a cell or range, simply select the cell or range, open the Style list, and click the desired style.

Creating Your Own Styles

Because Excel features such a limited number of styles, you probably will want to create your own styles. The easiest way to create a style is by example; you format a cell or range to suit your tastes and then create a style that saves the formatting applied to the cell(s). To create a new style by example, follow these steps:

1. Select a cell whose formatting you want to use.

2. Open the Format menu and choose Style . The Style dialog box appears, as shown in Figure 7.12.

   Figure 7.12. Type a name for the style and click Add.

   

3. Type a name for the style in the Style Name box.

4. Click the Add button. The named style is added to the Style Name box.

You can also create a style from scratch by using the Style dialog box, though the process is a little step-heavy:

1. Open the Format menu and choose Style . The Style dialog box appears, as you saw it in Figure 7.12.

2. Type a name for the style in the Style Name box, and click the Add button. The style is added to the list, and you can now modify it.

3. Remove the check mark from any check box whose attribute you do not want to include in the style.

4. To change any of the format settings for the attributes in the list, click the Modify button. Excel displays the Format Cells dialog box.

5. Use the Format Cells dialog box, as explained earlier in this chapter, to enter your formatting preferences for this style.

6. Click OK or press Enter . You are returned to the Style dialog box.

7. Click OK or press Enter . The style is created and saved.

Tip

If you added the Style drop-down list to the Formatting toolbar, as explained in the preceding section, creating a style by example is even easier. Simply select the cell that has the desired formatting and then click in the Style box, type a name for the style, and press Enter .

Editing Styles

If you apply a style to one or more cells and then modify the style later, any changes you enter are immediately applied to all cells you formatted with that style. You don't need to reapply the style to the cells you formatted with that style.

To edit a style, simply select the style in the Style dialog box, click the Modify button, and use the Format Cells dialog box to enter your preferences. Click OK to close the Format Cells dialog box, and then click OK to save your changes.

Saving Styles in a Template

Whenever you save a workbook, the styles you created while formatting that workbook stay with the workbook. You can also save the workbook as a template. When you create a new workbook, you can then choose one of the New from Template options to open a new workbook that contains the styles in that template. To learn how to save a workbook as a template, see the section "Saving a Workbook as a Template" in Chapter 3, "Working with Workbook Files."

Copying Styles from Another Workbook

If you already created a workbook and want to use styles you created in a different workbook, you can copy the styles from one workbook to the other. To copy styles, follow these steps:

1. Open both workbooks.

2. Switch to the workbook that will be receiving the styles.

3. Open the Format menu and choose Style . The Style dialog box appears.

4. Click the Merge button. The Merge Styles dialog box appears, as shown in Figure 7.13.

   Figure 7.13. You can copy the styles from one workbook to another.

   

5. Select the name of the worksheet to copy from.

6. Click OK or press Enter to close the Merge dialog box.

7. Click OK or press Enter .

The Absolute Minimum

A worksheet without formatting is a dreary site, but now that you have completed this chapter, you know everything you need to know to enhance your worksheets with color , lines, and better-looking text. Now you're able to

Give your worksheets a major overhaul with AutoFormat.

Change the most common text attributes by clicking a button in the Formatting toolbar.

Use the Format Cells dialog box to change number formats, control the way entries align in cells, and add borders and shading.

Make Excel flag data entries when they meet certain specified conditions.

Use the Format Painter to copy formatting from one cell and apply it to other cells.

Use styles to apply several formats to a cell or range with a single command.

With the skills you acquired in this chapter, you can liven up the most dreary worksheet and save loads of time by using styles. In the next chapter, we go beyond the mere appearance of numbers by transforming the numbers into something more meaningful: charts (graphs).