Leading the way in Microsoft Office Development

What is a Style ?

A style lets you apply a name to a combination of formatting attributes and are workbook specific.

These attributes can then be quickly applied by selecting the name of the style from a list.

A style is a group of commands that change the appearance of the cells in your worksheet.

You can use styles to help your worksheets and workbooks contain consistent formatting.

Styles are useful when you want all the headings in your workbook to look the same. If you change the style of one, then they will all change.

Styles also let you change the formatting characteristics of many cells at once.

Styles only apply to an individual workbook and are not easily transferable.

Advantages of using Styles

Styles can save you a lot of time when you need to apply the same format to different parts of your worksheet.

The advantage of using styles is that you can apply more than one formatting enhancement to a range of cells in one go.

Styles are not suitable for ranges of cells with different outline borders.

(Format > Style) dialog box

Styles have 6 attributes and these correspond to the six tabs of the (Format > Cells) dialog box.

 (Format > Style) dialog box

Built-in Styles

Every new workbook contains 6 built-in pre-defined styles

Comma - Two decimal places and a thousand separator (number formatting only)

Comma [0] - Same as above, rounded to the nearest integer

Currency - Currency format with two decimal places, thousand separator and currency sign (number format attribute only).

Currency [0] - Same as above, rounded to the nearest integer

Normal - Includes defaults for all formatting attributes

Percent - Percentage format with no decimal places (number formatting attribute only).

Note: If your workbook contains hyperlinks, then there may be extra styles "Hyperlink" and "Hyperlink Active"

In Styles there is an option to specify a style for the text after your style (example in the case of a caption with a (1x1) table below it

The standard Excel font and size is Arial 10. I personally would not change this. If you copy data (i.e. charts or objects) between workbooks that have different fonts and sizes it will create problems !!

Try to keep your style names short and use names that describe how the style is used and not the attributes.

The easiest way to create styles is to manually format a cell as required. (Tools > Styles) Select Add and type in the name.

You can easily create new styles. To create a style that flashes cells red and white. (Format > Styles) (Type Flash and Add). Add the following code to a module and run the procedure Flash from Auto_Open.

Changing the Default Style

The default style is used for both text and numbers and is also used to determine the font used for the row and column headings.

This can be changed from the (Tools > Options)(General tab).

The default font is Arial, 10

Any changes you make to the default font will not take affect untill Excel is closed and reopened.

Changing the Normal styles within a workbook will only affect the font in that particular workbook.

Defining a new Style

This can be done in two ways.

1) Providing an example of the style in a cell on a worksheet. Select the individual cell and select (Format > Styles).

2) Defining the attributes in the (Format > Styles) dialog box. When you select the modify button the (Format > Calls) dialog box will be displayed, allowing you to change the attributes.

Select (Format > Style)

Removing a Style

Any cells that are formatted with a style that is deleted are changed back to the "Normal" style.

Any cells that were formatted with that style but have since had some additional formatting added to them will remain the same.

Copying Styles between Workbooks

You can copy your styles to different workbooks ??

It is possible to copy styles between workbooks.

Open both the workbooks and activate the destination workbook. Select (Format > Styles) and select the Merge button.

This dialog box will display a list of all open workbooks. Select the name of the workbook you want to copy the styles from and press OK.

Be aware that any styles with the same name will be overwritten. You will only receive a single prompt regardless of how many styles may be overwritten.

Things to Remember

All the pre-defined styles except Normal only have a number attribute defined.

You can quickly change the formatting attributes of all the cells within a workbook by changing any of the "Normal" attributes.

It is always a good idea to remove any unwanted styles.

If you are using a lot of styles in your workbook, then it may be worth adding the Style drop-down list to the Formatting toolbar. For more details on this see the Customising your Toolbars and Menus section [Link]. This will also enable to create a new style by selecting the cell and typing the new name directly into the drop-down box.