Formatting Quickly with Table Formatting and Styles

To save you time with formatting, Excel provides preset formatting that you can apply to a table to give it an overall look. And to save you the effort of applying many different types of formatting over and over again to different cells, Excel includes styles—collections of formatting you can apply all at once.

Formatting with Table Formatting

When you need to format a table quickly, see if Excel's preset table formatting will do the trick. Select the table, choose Tables Table Styles Table Styles, and then click the style you want.

Formatting with Styles

As you've seen earlier in this chapter, you can give any cell exactly the formatting you want by using the controls in the Font group, Alignment group, and Number group of the Home tab of the Ribbon, by using the controls on the Formatting toolbar, or by opening the Format Cells dialog box and working on its six tabs. But applying formatting one aspect at a time—font, font size, alignment, and so on—is slow work, and it's easy to apply formatting inconsistently.

To save time and ensure your formatting is consistent, you can use Excel's styles. Each style is a collection of formatting that you can apply to one or more cells. The style contains six types of formatting, one for each tab of the Format Cells dialog box:

• Number. For example, General, Currency, or Percentage.
• Alignment. Horizontal alignment (for example, General, Center, or Justify), vertical alignment (for example, Top, Center, or Bottom), and any trimmings (such as wrapping the text to the window).
• Font. The font, font size, font color, and so on.
• Border. Any borders you've applied to the style, or No Borders if it has no borders.
• Fill. Any fill you've applied to the style, or No Shading if it's plain.
• Protection. Locked, Hidden, both, or No Protection.

Most Excel templates contain plenty of styles to get you started, but you can create your own custom styles as well if you need to.

Meeting Excel's Styles

To see which styles are available in a workbook, choose Home Format Styles and look at the Cell Styles panel (see Figure 4–22). This panel lists the styles in the following categories:

• Custom. This category appears only when you have created one or more custom styles in the workbook.

Figure 4–22. The Styles panel displays your custom styles at the top and Excel's built-in styles in different categories.

• Comparisons. This category has Good, Bad, and Neutral styles that you can use to apply color coding to cells. Here is also where you will find the Normal style that Excel applies to any cell that doesn't have another style.
• Data and Model. This category contains the Calculation, Check Cell, Explanatory, Followed Hyperlink, Hyperlink, Input, Linked Cell, Note, Output, and Warning Text styles. Most of these styles are used for data modeling. Excel automatically applies the Hyperlink style to cells containing hyperlinks you have not clicked yet, changing their style to Followed Hyperlink once you have clicked them.

• Titles and Headings. This category contains four styles for descending levels of headings (Heading 1, Heading 2, Heading 3, and Heading 4), the Title style for giving a worksheet a title, and the Total style for easily formatting cells that contain totals.
• Themed Cell Styles. This category contains six Accent styles (Accent 1 through Accent 6) featuring six of the theme colors, with four degrees of shading for each. This category appears only if the workbook's template contains themed cell styles.

Applying a Style

To apply a style, choose Home Format Styles, and then click the style on the Styles panel. You can also apply the various Number styles (such as the Currency styles, the Percent style, and the Comma style) from the Number group on the Home tab of the Ribbon.

Creating Custom Styles

If none of Excel's styles meets your needs, you can create your own. To create a style, follow these steps:

1. Format a cell with the formatting you want the style to have.

   TIP: To jump-start your formatting, apply the existing style that's nearest to the look and formatting you want. Then change the formatting so it looks the way you want.

2. Select the cell you've formatted.
3. Choose Home Format Styles New Cell Style to display the New Cell Style dialog box (shown in Figure 4–23 with settings chosen).
   

   Figure 4–23. In the New Cell Style dialog box, you can quickly create a new style based on the formatting of the selected cell. You can change the formatting as needed by clicking the Format button and working in the Format Cells dialog box.

4. In the Style name box, type the name you want to give the style.
5. In the “The selected style has formatting for the following properties” area, clear the check boxes for any formatting the cell has that you want to omit from the style. For example, clear the Protection check box to leave out the Locked or Hidden formatting.

   NOTE: If you need to alter the formatting, click the Format button to display the Format Cells dialog box. Make the changes needed, and then click the OK button to return to the New Cell Style dialog box.

6. Click the OK button to close the New Cell Style dialog box and create the style. Excel adds the style to the Custom area at the top of the Styles panel.

Copying Styles from One Workbook to Another

If you have styles in one workbook that you want to use in another workbook, you can copy the styles across. Excel calls this importing styles. When you import the styles, the destination workbook receives all the styles from the source workbook—you can't pick and choose (but see the nearby Tip).

To import the styles, follow these steps:

1. Open the source workbook (the workbook that contains the styles) and the destination workbook.
2. Switch to the destination workbook by clicking in it.
3. Choose Home Format Styles Import Cell Styles to display the Import Cell Styles dialog box (see Figure 4–24).
   

   Figure 4–24. Use the Import Cell Styles dialog box to copy all the styles from one workbook into another workbook.

4. In the Copy cell styles from an open workbook list box, click the source workbook.
5. Click the OK button to close the import Cell Styles dialog box. Excel copies the styles into the destination workbook and you can then start using them.

Deleting Styles You Don't Need

If you no longer need a style, you can delete it. Choose Home Format Styles, Ctrl+click or right-click the style in the Cell Styles panel, and then click Delete on |the context menu.