Styles in Excel - Special Movies and Comedy


Special Movies and Comedy

Saturday, February 26, 2011

Styles in Excel

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). Access the style dialog by choosing Format, Style... from Excel 97-2003's menu. In Excel 2007 you may access the style dialog from the Home Tab, Styles group, Cell Styles button.
Dialoogvenster opmaakprofiel

The Styles dialog screen for Excel 97-2003
Excel 2007 has a slightly different screen:
Opmaakprofielen venster voor Excel 2007
Style window for Excel 2007
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 choose Format, Style... from the menu (for Excel 2007: Click the Home tab, click the Cell Styles button in the Styles group).
Styles dialog
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:
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.
In Excel 2003, after modifying all settings you have two options to "save" the modifications to the style:
  • By clicking "OK"; The currently selected (or added) style will be modified/created and the currently selected cells will have that style applied.
  • By clicking "Add" and subsequently clicking Close: The style will be updated/added, but not applied to the current selection.
The cells that already have this style applied will of course be updated regardless whether method 1 or 2 is used.In short: If you want to modify a style and apply it to the current selection, click OK. Otherwise, click Add and then Close.
Excel 2007 defaults to the second option: the style dialog will update/add the style, but the style will not be applied to the selected cells.

Applying styles

There are two methods to apply a style to a cell.
The first one is by using the menu: "Format", "Style…". Select the style you need from the dropdown list and click OK. Or in Excel 2007: Home tab, Styles group, Expand the Cell Styles gallery and click a style.
In Excel 97-2003 you can save yourself some time (in future) by adding the Style dropdown to a toolbar, like this:

TIP: Adding the style dropdown to a toolbar.

  • Rightclick any toolbar or menu and select "Customize...". The dialog screen shown below will appear (after you click the "Commands" tab):
The Customize dialog screen
The Customize dialog screen.
  • Below "Categories", click the item called "Format". Now drag the Style dropdown to one of your toolbars (see below)
Drag the Style control to a toolbar and let go
Dragging the Style control to a toolbar and let go.
Applying a style to some cells now takes nothing more than picking the right one from your new style dropdown. If the format of any of the selected cells deviates from the selected style then Excel (Excel 97 - 2003) will ask you whether or not to update the style to match the formatting of the selected cells (Excel 2007 does not ask you this question!):
Confirm to Excel how it must apply the style
Confirm to Excel how it must apply the style
This dialog needs some explaining to understand what happens when you click a button.

  • When you click Yes:
    Excel will check all selected cells and add only those elements to the style which coincide for all cells. So if you for example changed a font characteristic for one cell, then Excel will remove the Font settings from the style. Of course you can go back to the style using the menu (format, styles) and modify the style to include that setting again. Note however, that the one cell you changed a font thing to will remain being different from the others.
  • If you click No:
    The selected style will be applied to all cells. Any formatting in those cells will be replaced with the selected formatting elements if the style you chose. Any deviations from a style will be removed. This is the choice Excel 2007 makes for you if you select a style from the styles gallery.

<script type="text/javascript"><!--
google_ad_client = "pub-8760324284433281";
/* 300x250, created 2/25/11 */
google_ad_slot = "9332447767";
google_ad_width = 300;
google_ad_height = 250;
<script type="text/javascript"

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.
Series of cells with one style, 1 cell deviates from that style.
You can restore the style to a deviating cell by selecting the cell and choosing the style from the style dropdown and clicking "No" on the subsequent question Excel 97 - 2003 will ask you (See below) . Note that Excel 2007 never asks you this question and performs the same action as when you click "No" . See the previous page to find out what clicking Yes or no causes Excel to do.
Confirm to Excel how it must apply the style
Confirm to Excel how it must apply the style

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
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 "Format", "Style...". Enter the name of the new style and click Add. Then click Modify to change the style details. Don't forget to update the 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:
Sub FindaStyle()
    Dim oSh As Worksheet
    Dim oCell As Range
    For Each oSh In ThisWorkbook.Worksheets
        For Each oCell In oSh.UsedRange.Cells
            If oCell.Style Like "*demo*" Then
                Application.GoTo oCell
            End If
End Sub
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":
Sub ListStyles()
    Dim oSt As Style
    Dim oCell As Range
    Dim lCount As Long
    Dim oStylesh As Worksheet
    Set oStylesh = ThisWorkbook.Worksheets("Config - Styles")
    With oStylesh
        lCount = oStylesh.UsedRange.Rows.Count + 1
        For Each oSt In ThisWorkbook.Styles
            On Error Resume Next
            Set oCell = Nothing
            Set oCell = Intersect(oStylesh.UsedRange, oStylesh.Range("A:A")).Find(oSt.Name, _
                oStylesh.Range("A1"), xlValues, xlWhole, , , False)
            If oCell Is Nothing Then
            lCount = lCount + 1
            .Cells(lCount, 1).Style = oSt.Name
            .Cells(lCount, 1).Value = oSt.NameLocal
            .Cells(lCount, 2).Style = oSt.Name
            End If
    End With
End Sub

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 will loose all formatting in your file!!!
Sub ReApplyStyles()
'Resets styles of cells to their original style (resets all formatting done on top of ANY style)
    Dim oCell As Range
    Dim oSh As Worksheet
    If MsgBox("Proceed with care:" & vbNewLine & vbNewLine & _
              "This routine will erase all formatting done on top of the existing cell styles." & vbNewLine & _
              "Continue?", vbCritical + vbOKCancel + vbDefaultButton2, GSAPPNAME) = vbOK Then
        For Each oSh In ActiveWindow.SelectedSheets
            For Each oCell In oSh.UsedRange.Cells
                If oCell.MergeArea.Cells.Count = 1 Then
                    oCell.Style = CStr(oCell.Style)
                End If
    End If
End Sub

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.
Sub FixStyles()
' Procedure : FixStyles
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 4-10-2007
' Purpose   : Replaces styles with the replacement style as defined by a two column list.
'             column 1 should contain the existing style, col 2 the replacing style
    Dim sOldSt As String
    Dim sNewSt As String
    Dim oSh As Worksheet
    Dim oCell As Range
    Dim oSourceCell As Range
    Set oSourceCell = ActiveCell
    While oSourceCell.Value <> ""
        sOldSt = oSourceCell.Value
        sNewSt = InputBox("Please enter replacement style for:" & sOldSt, "Style changer", oSourceCell.Offset(, 1).Value)
        If sNewSt = "" Then Exit Sub
        If sNewSt <> "" And sNewSt <> sOldSt Then
            For Each oSh In ThisWorkbook.Worksheets
                For Each oCell In oSh.UsedRange
                    If oCell.Style = sOldSt Then
                        Application.GoTo oCell
                        On Error Resume Next
                        oCell.Style = sNewSt
                    End If
        End If
        Set oSourceCell = oSourceCell.Offset(1)
End Sub


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

No comments:

Post a Comment