Styles in Excel

Content

• Introduction
• How styles work
• Creating styles
• Applying styles
• Deviate from a style
• Tips for using styles
• VBA examples and tools
• International issues
• Conclusion

Introduction

This article has also been published on Microsoft's MSDN site:

Using Styles to Dress Up Your Worksheets in Excel 2007

This article explains how you can use styles to ease maintenance of your spreadsheet models.

Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. Because formatting of cells is often done in an ad-hoc manner, many spreadsheet pages look messy.

By consistently using cell styles (instead of changing parts of the cell's formatting) you will be forced to think about the structure of your work. Religiously using styles may even force you to reconsider the overall structure of the entire spreadsheet model: The quality of the computational model itself may be positively affected.

I therefore consider Styles as being underused, underestimated and under exposed.

How styles work

A style is just a set of cell formatting settings which has been given a name. All cells to which a style has been applied look the same formatting-wise. When you change a part of a style, all cells to which that style has been applied change their formatting accordingly.

Use of styles takes some getting accustomed to, but may bring you great advantage. Imagine showing your nicely formatted sheet to your boss. Then your boss asks you if you could please change all input cells to having a light-yellow background fill, instead of a dark yellow one. For a large model, this may imply a huge amount of work. Would you have used styles, then it would have been a matter of seconds.

Styles are in fact an addition. Cell formatting is the sum of the applied style and all modifications to individual formatting elements on top of that style. What parts of the formatting options are included in a style is determined during the definition of the style (See screenshot below).

You access the style dialog from the Home Tab, Styles group, Cell Styles button:

Excel enables you to access the styles by clicking the dropdown next to the styles gallery. The screen to create a new style looks like this (if you click the New Cell Style option in the style gallery):

When you apply a style to a cell followed by another style, the end result will be an addition of the selected parts of both styles. What the end result of such an addition of styles will be, depends on which elements of both styles have been selected as being part of the style (this will be discussed later). Theoretically, this would have enabled us to use cascading styles, but unfortunately Excel does not keep a record of the order of applied styles. Only the last style is remembered. Also, styles can not be derived from other styles whilst maintaining a link to the parent style. Changes to the "original" style are not reflected in the "child" styles.

Creating styles

A convenient method to create a new style is by selecting a cell which has all formatting options in place which you want to incorporate in the new style. Select the cell and click the Home tab, click the New Cell Style button at the bottom of the Styles gallery.

The Styles dialog screen.

To create a new style, simply type its name in the box "Style name". By default, all formatting elements are checked. Remove the checkmarks for the formatting elements you want to omit from the style you are creating (The dialog shown above has the Number and Alignment elements turned off).

Use the "Modify..." button to adjust the elements to your needs. Excel will show the standard "Format cells " dialog screen:

The format cells dialog screen, as shown after clicking Modify... on the Style dialog.

Note, that the elements in the Style dialog are identical to the tabs on the Format Cells dialog.

Note: As soon as you change a formatting element on a tab that was not selected on the Style dialog, Excel will automatically check that element for you; it will become part of that style.

Note that the style dialog will update/add the style, but the style will not be applied to the selected cells.

Applying styles

To apply a style to a cell, you simply click the Home tab and in the Styles group you Expand the Cell Styles gallery and click a style.

Deviate from a style

If you have applied a style to a set of cells and you change a formatting element of one of those cells, then modifications to that particular element of the style will no longer be applied to the modified cell.

So after changing a font attribute (like Bold) of a cell, changing the font attributes of the style will update all cells, except the one you just modified:

Series of cells with one style, 1 cell deviates from that style.

You can restore the style of a cell simply by selecting the cell and choosing the style from the style gallery.

Tips for using styles

Managing styles

If you like to keep an overview of what styles are available in your file I'd advise you to add a special worksheet to your workbook. Put the names of the styles in column A and an example output in column B:

Table with styles in a worksheet

If you need to adjust a style, select the cell in column B and adjust the style settings from there.

Creating a new style based on an existing one is easy now: Just copy the applicable row and insert it anywhere in the table. Select the cell in column B of the newly inserted row and choose Home, Cell styles gallery, New Cell Style. Enter the name of the new style and click the Format button to change the style details. Don't forget to update the Style name in column A too.

Using styles

I advise you to use styles as strictly as you can. Avoid modifying one formatting element of a cell with a style. Instead, consider if it is worth the effort to add a new style. If for instance you have a style for percentage with 2 decimal places and you have a cell which requires three, then add a style for that purpose. You can thank me later.

Adapting this method will likely trigger you to think about what cell styles your document will need. By doing this your Excel models will gradually improve. You'll gain in consistency and loose the ad-hoc (often messy) formatting jungle.

Use functional sets of styles

By looking at your Excel model you will likely be able to categorise your workbook cells into various categories:

• Input cells
  Cells that are the main input to your model
• Parameter cells
  Cells that contain constants for your model, such as boundaries.
• Output cells
  Cells in an area that is meant for output, such as printing or presenting the results of a calculation on screen.
• Calculation cells
  The cells where the actual calculation work is performed
• Boundary cells
  By shading otherwise empty cells you can easily make areas with differing functions stand out from other areas.

Consider creating styles for each of these cell functions, each (e.g.) having its own fill color. Don't forget to make decisions on whether or not a style's locked property needs to be on or off. If you use a system like this, it becomes very easy for you to maintain your file. Imagine how easy it now becomes to change a cell from an input to an output cell: you change its style. Done.

VBA examples and tools

The little VBA routines shown below will greatly easy your work with styles. As an important side effect, these also show you how the style object works in VBA.

Find cells with a certain style

This routine find cells with a style containing "demo" in its name:

As soon as a cell is encountered with a style that matches that name filter, the code stops (Stop) and you can check out the cell in detail.

Creating a list of styles

This sub adds a table of your styles on a worksheet named "Config - Styles":

Clear all formatting of cells and re-apply their styles

The code below removes all formatting of all cells and subsequently re-applies their style to them.

Watch out: if you have not adhered to using styles strictly, you may lose all formatting in your file!!!

Replace one style with another

The code below uses a list with two columns. The column on the left contains the names of existing styles. The column to its immediate right contains the names of the style you want to replace them with.

The code will run through the selected cells in the left column and check if the style name in the column to its right differs. If so, it will prompt you with the alternative name. Clicking OK will cause the code to update ALL cells to which the old style was applied to the new style. Before running this sub you need to select the cells in the left hand column.

Removing formating from an Excel Table

Suppose you have just converted a range to a table (see this article), but the range had some formatting set up such as background fills and borders. Tables allow you to format things like that automatically, but now your preexisting formatting messes up the table formatting. One way to overcome this is by changing the style of the cells in the table back to the Normal style. This however removes your number formats too. The little macro below fixes that by first making a copy of the normal style, setting its Number checkbox to false and then applying the new style without number format to the table. Finally it applies the tablestyle and deletes the temporary style:

International issues

The VBA Style object offers both a Name and a NameLocal property. Which might give you the impression that if your Excel is set to Dutch, you can apply the Heading 1 style to a cell using this syntax:

Or perhaps like this:

But if my Excel is set to Dutch, trying to run that code yields this error:

Error 450; "Onjuist aantal argumenten of ongeldige eigenschappentoewijzing"

This is because the Heading 1 style's NameLocal property is the same as its Name property: Kop 1

Even worse, if you run through the Styles collection, the index of the Kop 1 style differs from the index of the Heading 1 style in an English Excel. They are sorted by their localized names, in alphabetical order. Moreover, if you haven't clicked any hyperlinks yet in your current Excel session, the Hyperlink and Followed Hyperlink styles are absent from the Styles collection altogether.

This is what the English and Dutch lists look like:

Notice how the Heading styles misalign with the Kop styles.

For workbooks (and add-ins) which need to work in several different language versions of Excel, my work-around for this issue is to have some cells in a (hidden) sheet somewhere (or inside the add-in), which have been formatted as Heading 1 to 4. The add-in then reads those cell styles to get the localized style name.

Conclusion

There is a lot to gain by using styles in your Excel work. To name but a few:

• Consistent formatting of your models
• Ease of maintenance
• A strict use of styles leads to a structured way of working
• Less problems with your file (There is a limit on how many different cell formats Excel can handle).

With this article I have tried to give insight in the use of styles in Excel. If you have comments, suggestions or questions, please don't hesitate to use the comment form below each page!

Download a pdf version of this article