Using Styles to Dress Up Your Worksheets in Excel 2007

Summary: Learn how to create professional, easily maintainable workbooks in Microsoft Office Excel 2007 by taking advantage of styles. See how to modify existing styles, create your own styles, and use sample VBA subroutines to use styles to their fullest advantage. Helpful tips to working with styles are also presented. (8 printed pages)

Jan Karel Pieterse, Excel Most Valuable Professional

Frank Rice, Microsoft Corporation

March 2009

Applies to: Microsoft Office Excel

Contents

• Introduction to Excel Styles

• How Cell Formatting Styles Work

• Creating Styles

• Applying Styles to a Cell

• Deviating from a Style

• Tips for Using Styles

• Working with Styles in VBA

• Conclusion

• Additional Resources

• About the Authors

Introduction to Excel Styles

Microsoft has made it very easy to polish your Microsoft Office Excel 2007 worksheets with various fill patterns, borders, and other options. Because the formatting of cells is often done in an ad-hoc manner, many spreadsheet pages look less than professional.

By consistently using cell styles, instead of changing parts of the cell's formatting, you are 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.

In this article, you see how you can use styles to ease the design and maintenance of your spreadsheet models.

How Cell Formatting Styles Work

A style is a set of cell formatting settings which is given a name. All cells with a particular set of styles look the same. When you change a part of a style, all cells with that style change their formatting accordingly.

The use of styles takes some getting accustomed to but brings you great advantages. Imagine showing a nicely formatted sheet to your boss. Further imagine your boss asking you to change all input cells having a dark-yellow fill to a light-yellow background fill. For a large model, this might require a huge amount of work. However by using consistent styles, you can update all cells in a matter of seconds.

Note
Styles are specific to a workbook. Thus if you assign a style, it stays with the workbook. However, you can copy a style from one workbook to another by simply copying a cell with that style to the other workbook. Additionally, there is a Merge button on the Style dialog box (see Figure 1) that lets you merge the styles from one workbook into another workbook.

Styles are additive. Cell formatting is the sum of the applied style and all modifications to individual formatting elements on top of that style. Which parts of the available formatting options are included in a style is determined during the definition of the style (see the Style dialog box in Figure 1). In Microsoft Excel 97, Microsoft Excel 2000, Microsoft Excel 2002, and Microsoft Office Excel 2003, you display the Style dialog by clicking the Format menu and then clicking Style. In Excel 2007, you access the Style dialog box from the Home tab, in the Styles group, by clicking the Cell Styles button.

Figure 1. Styles dialog box in previous versions of Excel

Excel 2007 has a slightly different Style dialog box as seen in Figure 2.

Figure 2. Styles dialog box in Excel 2007

When you simultaneously apply multiple styles to a cell, the end result is the addition of both styles. Theoretically, you might think that this would enable cascading styles; unfortunately, Excel does not retain the order of applied styles. Only the order of the last style selected is retained. Additionally, a style cannot be derived from other styles while maintaining a link to the parent style. Changes to the original style are not reflected in the child styles.

Creating Styles

A convenient way to create a style is by selecting a cell that has all of the formatting that you want, before opening the Style dialog screen.

To create a new style

1. Select the cell and do one of the following:

   • In Excel 97 through 2003: Click the Format menu and then click Style.

   • In Excel 2007: Click the Home tab and in the Styles group, click the Cell Styles button.

2. In the Style Name box, type a name for the new style.

3. By default, all formatting elements are checked. Clear the checkboxes for the formatting elements you want to omit from the style you are creating.

4. Click the Modify button. The Format Cells dialog box appears as shown in Figure 3. Notice that the elements in the Style dialog box (see Figure 1 or Figure 2) are identical to the tabs on the Format Cells dialog box.

   Figure 3. Format Cells dialog box

   

   Note
   When you change a formatting element on a tab in the Format Cells dialog box that was not selected on the Style dialog box, Excel automatically checks that element for you; it becomes part of that style.

5. Select any format options you want for the new style and then click OK to return to the Style dialog box.

   In Excel 2003, after modifying the style settings, you have two options to save the modifications:

   • By clicking the OK button, the selected style is modified and the currently selected cells have that style applied.

   • By clicking the Add button and then clicking Close, the style is updated but not applied to the currently selected cells. The cells that currently have this style applied are updated regardless of which method you use. In other words, if you want to modify a style and apply it to the current selection, click OK. Otherwise, click Add and then click Close.

   Excel 2007 defaults to the Add option so that the style dialog is updated or added, but the style is not be applied to the selected cells.

Applying Styles to a Cell

There are two methods to apply a style to a cell depending on which version of Excel you are using. In Excel 97, Excel 2000, Excel 2002, and Excel 2003, you click the Format menu, click Style, and then select the style you want from the dropdown box. In Excel 2007, you click the Home tab, and in the Styles group, expand the Cell Styles gallery, and then click a style.

In Excel 97, Excel 2000, Excel 2002, and Excel 2003, you can save yourself some time by adding the Style box to a toolbar.

To add the Style dropdown to a toolbar

1. Right-click any toolbar or menu and then click Customize. Click the Commands tab as shown in Figure 4.

   Figure 4. Customize dialog box

   

2. In the Categories box, select Format.

3. Next, drag the Style control from the Commands box to a toolbar as shown in Figure 5.

   Figure 5. Drag the Style control to a toolbar

   

You are now ready to apply a style to cells from the new Style box.

In Excel 97, Excel 2000, Excel 2002, and Excel 2003, if the format of the selected cells deviates from the selected style, a dialog box is displayed that ask you whether or not to update the style to match the formatting of the selected cells. Excel 2007 does not display this box.

Figure 6. Confirmation is needed to apply the style

This confirmation causes one of the following actions to occur:

• If you click Yes, Excel checks all selected cells and only updates the styles that apply to all cells. For example, if you change a font characteristic for one of the selected cells, Excel removes the Font settings from the styles for all of those cells. You can reset the style using the Format, Style menu and modify the style to include that setting again. However, the original cell with the changed font remains different than the other cells.

• If you click No, the selected style is applied to all cells. Any formatting in those cells is replaced with the style you chose. Any deviations from the style are removed. In Excel 2007, this is the default behavior when you select a style from the Styles gallery.

Deviating from a Style

If you apply a style to a range of cells and then change a formatting element for one of those cells, any further modifications to that particular style element is not applied to the modified cell. For example, after changing a font attribute such as removing the bolding for a single cell, changing the font attributes of the style updates all cells in the range except the one you previously modified (see Figure 7).

Figure 7. A range of cells with one style where one cell deviates from that style

You can restore the style of the deviating cell by selecting the cell and choosing the style from the Style list and then clicking No to the confirmation dialog (in Excel 97, Excel 2000, Excel 2002, and Excel 2003). Note that in Excel 2007, the confirmation box is never displayed and the default action is the same as if you clicked No to the confirmation.

Tips for Using Styles

Managing Styles. To assist you in adjusting existing styles or creating styles for a workbook, you might consider adding a blank worksheet to your workbook. In the new worksheet, put the names of the styles you are using in the workbook into column A and an example of that style in column B such as seen in Figure 8.

Figure 8. 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. This gives you a preview of the proposed change without actually modifying the style in your working worksheets.

Creating a style based on an existing one is just as easy. Make a copy the applicable row and insert it in any blank area of the worksheet. Select the cell in column B of the newly inserted row and click the Format menu, and then the Style menu. Type the name of the new style and click Add. Next click Modify to change the style details. And finally, update the name in column A.

Caution
In Excel 2007, all styles which are not used anywhere in your workbook are not saved with your file. This only applies if you use the new Excel 2007 Open XML Format files (.xlsx, .xslm and .xlst) when you save your file. To prevent this from happening, using a styles worksheet (as mentioned earlier on) is a good idea.

Using styles. You should be judicious in modifying existing styles. Avoid modifying one formatting element of a cell with a style. Instead, consider if it is worth the effort to add a new style. For example, if you have a style for percentage with two decimal places and you have a cell which requires three, then add a style for that purpose instead of updating the existing style or just changing the cell’s number format on top of its style. Adapting this method likely forces you to consider what cell styles your document needs, improving the consistency of your worksheets, and helping you to avoid ad-hoc style formatting.

Use functional sets of styles. In most workbook models, cells fall into various categories:

• Input cells: Cells that are the main data inputs to your model

• Parameter cells: Cells that contain constants for your model, such as data boundaries

• Output cells: Cells meant for output, such as printing or presenting the results of a calculation on screen

• Calculation cells: The cells where calculations are performed

• Boundary cells: Shading otherwise empty cells that make areas with differing functions stand out from other areas

Consider creating styles for each of these cell functions, such as giving each range of cells their own fill color. This is also a good time to consider whether a style’s Locked property needs to be set to off or on. By using a system such as this, it becomes very easy to maintain your workbooks. For example, imagine how easy it now becomes to change a cell from an input to an output cell; just change its style and you are finished.

Working with Styles in VBA

The following Microsoft Visual Basic for Applications (VBA) subroutines greatly ease your work with styles and demonstrate how to work with the Style object.

Find cells with a certain style. The following subroutine find cells with a style containing demo in its name.

Sub FindaStyle()
'-------------------------------------------------------------------------
' Procedure : FindaStyle
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 4-10-2007
' Purpose   : Searches for a style by name.
'-------------------------------------------------------------------------
    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
             Stop
          End If
       Next
    Next
End Sub

When a cell is found with a style that matches that name filter, the code stops running, letting you examine the cell in detail. You can continue running the code by pressing the F5 key.

Creating a list of styles. The following subroutine adds a table of your styles on a worksheet named Config – Styles.

Sub ListStyles()
'-------------------------------------------------------------------------
' Procedure : ListStyles
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 4-10-2007
' Purpose   : Creates a table of styles that are used in a workbook.
'-------------------------------------------------------------------------
    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
        Next
    End With
End Sub

Clear all formatting of cells and re-apply their styles. The following subroutine removes all of the formatting of all cells and then subsequently reapplies the style.

Caution
If you have not adhered to using styles strictly, you will lose all formatting in your file.

Sub ReApplyStyles()
'-------------------------------------------------------------------------
' Procedure : ReApplyStyles
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 4-10-2007
' Purpose   : Removes the formatting from all cells and then reapplies the style.
'-------------------------------------------------------------------------
'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
            Next
        Next
    End If
End Sub

Replace one style with another.The following subroutine uses a list with two columns. The column on the left contains the names of existing styles. The column on the right contains the names of the style you want to replace them with. The code moves through the selected cells in the left column and checks if the style name in the column to its right differs. If so, you are prompted with the alternative name. Clicking OK causes the code to update ALL cells with the old style to the new style. Before running this subroutine, 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
                Next
            Next
        End If
        Set oSourceCell = oSourceCell.Offset(1)
    Wend
End Sub

Conclusion

There is a lot to be gained by using styles in your Excel workbooks. For example, consider the following:

• Consistent formatting of your models

• Ease of maintenance

• A strict use of styles leads to a structured way of working

• Fewer problems with your file. For example, there is a limit on how many different cell formats Excel can handle. Managing how you use styles helps you avoid this and other boundaries.

Additional Resources

For more information about using styles in Excel, see the following resources:

• Style Object [Excel 2003 VBA Language Reference]

• How to: Apply Styles to Ranges in Workbooks

• Understanding the Excel Object Model from a .NET Developer's Perspective

About the Authors

Jan Karel Pieterse founded JKP Application Development Services in November 2003. JKP Application Development Services develops custom-made applications using Microsoft Office Excel and Microsoft Office Access, and VBA to its full extent.

Frank Rice is a programming writer for the Microsoft Office Developer Center. He is a frequent contributor to the Office Talk column and to the Microsoft Developer Network.