Visit Applied Office's Website Contact  

August 2006 : excel


  dressing with style(s)!

If you find yourself applying the same formatting options to cells over and over again, you could probably save a lot of time by using a style. A style is a collection of formats—such as font size, color, patterns, number format and alignment—that you can define and save as a group. Once you have defined and created a style, you can apply its formatting elements with a single click. (If you have used Excel’s AutoFormat feature, you are familiar with this concept. In fact, an AutoFormat is really a group of styles that are applied at once.)

An additional advantage to using styles is that when you modify a style, say from Times New Roman to Arial font type, everything that is formatted with that style is updated to reflect the formatting changes. That’s a lot easier than using the Format Painter to fix everything, don’t you think?

In Excel, a style can contain any, or all, of the following formatting attributes:

  • Number

  • Font (type, size, and color)

  • Borders

  • Alignment

  • Pattern

  • Protection (locked and hidden)

In this article, you will learn how to create, apply, and modify a style.

Creating a style by example

The easiest way to create a style is by example. This means you format a cell or cell range and then create a style based on the formatting of that cell or cell range.

First, apply the formatting you want to use. Take the example below: the "Price Per Unit" column could use some currency number formatting. While we’re at it, let’s create a new style for any data that has to do with money to make it stand out.

Select the cell or cell range and apply the formatting you want. In the example, cell D2:D14 is selected.

Select Format > Cells from the menu to open the Format Cells dialog box. Apply all the formatting you want to the text. In our example, we applied Currency number formatting using the Number tab. We also applied different font formatting to make the data stand out using the Font tab. Once all the formatting you want to use is applied, click OK.

When the formatting you want to use as a style is selected, you’re ready to create the style. Select Format > Style from the menu. The Style dialog box appears. Notice that the style currently being applied to the selected text appears in the Style name list box.

To create a new style, select the text in the Style name box, and enter the name of the new style. In our example, we named the style "Money". Click OK.

Now the style is created, and you can apply it to other cells in the worksheet. Let’s try that next.

Applying a style

First, select the cells to which you want to apply the style. Then, select Format > Style from the menu to open the Style dialog box. To apply the style, click the Style name list arrow and select the style you want to apply. Then click OK. As you can see, styles make it easy to use the same formatting properties in several places on a worksheet.

Modifying a style

Last, modifying a style is one of the coolest attributes of styles. When a style is modified, all cells that have the style are changed with the style.

Open the Style dialog box (you should know how to do this by now). Make sure the style you want to modify appears in the Style name list box. Then click the Modify button. The Format cells dialog box appears. Make formatting modifications in the dialog box and then click OK to apply the changes to the style.

What a snap! All the cells that use the style are modified and updated with the style’s formatting changes.


  quick jump

Jump to other stories through these links:

[ contents.htm]

  upcoming classes on Excel

Applied Office is offering a class on Pivot Tables on August 28.  Sign up early because seating is very limited.  Find out more.

Another class, Microsoft Excel Shortcuts, is scheduled for November 13.  Find out more.

  quick reference card

Get the Quick Reference Card on Microsoft Excel!  Download it for free and print it on your own printer.  You might even want to laminate it.

  screen shots

The following screen shots illustrate the articles on the left.  Click on one for a larger view.

Creating a Style


Applying a Style


The Finished Product