Styles in Excel

Content

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:

Styles in the Excel 2010 ribbon
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):

Styles dialog of Excel

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.

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.

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.

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

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:

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
                Stop
            End If
        Next
    Next
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
        Next
    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 may lose 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
            Next
        Next
    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
                Next
            Next
        End If
        Set oSourceCell = oSourceCell.Offset(1)
    Wend
End Sub

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:

Sub RemoveFormattingOfTable()
    Dim oStNormalNoNum As Style
    On Error Resume Next
    Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
    On Error GoTo 0
    If oStNormalNoNum Is Nothing Then
        ActiveWorkbook.Styles.Add "NormalNoNum"
        Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
        oStNormalNoNum.IncludeNumber = False
    End If
    With ActiveSheet.ListObjects(1)
        .Range.Style = "NormalNoNum"
        'Now apply tablestyle:
        .TableStyle = "TableStyleLight1"
    End With
    ActiveWorkbook.Styles("NormalNoNum").Delete
End Sub

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:

    ActiveCell.Style = "Heading 1"
    

Or perhaps like this:

    ActiveCell.Style = ActiveWorkbook.Styles("Heading 1")    

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:

Table with styles in a worksheet

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:

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

 

 


Comments

Showing last 8 comments of 130 in total (Show All Comments):

 


Comment by: Thaddeus Lesnik (9-8-2019 16:11:00) deeplink to this comment

I’d love to have a user menu box with two drop down lists, each populated with the list of styles.
The purpose of having two drop down boxes would be to select one style from the list then in listbox 1 then choose a different style from the list to replace it with using listbox 2. This would be useful if, for example, I have a style which got duplicated when I copy a sheet and the style intent is the same, but two slightly different name exist.


Comment by: Jan Karel Pieterse (9-8-2019 17:34:00) deeplink to this comment

Hi Thaddeus,

You can use the routine called FixStyles to find out how to implement a part of what you need.


Comment by: Thaddeus Lesnik (12-8-2019 21:08:00) deeplink to this comment

For what it's worth, here's a clean summary of what the code became (can be placed completely in the Userform code or broken into modules and forms). Note to webmaster, I can share the modules and forms if that would be useful.

Sub UserForm_Initialize()
    Dim oSt As Style
    For Each oSt In ThisWorkbook.Styles
        lstbxSource.AddItem oSt.Name
        lstbxDest.AddItem oSt.Name
    Next oSt
End Sub

Sub cmdChange_Click() 'this is the userform button.
    Dim strOldSt As String
    Dim strNewSt As String
        strOldSt = frmStyles.lstbxSource.Text
        strNewSt = frmStyles.lstbxDest.Text
     Call FixStyles(strOldSt, strNewSt)
     'Report on the Status of the Completion of the Process
    MsgBox "Cell Style has been remapped!", vbInformation
End Sub

Sub FixStyles(strOldSt As String, strNewSt As String)
'-------------------------------------------------------------------------
' Procedure : FixStyles
' Purpose : Replaces styles with the replacement style as defined by listboxes in a userform.
'             Listbox 1 should contain the existing style, Listbox 2 the replacing style
'-------------------------------------------------------------------------
    Dim oWs As Worksheet
    Dim oCell As Range
     If strNewSt = "" Then Exit Sub
        If strNewSt <> "" And strNewSt <> strOldSt Then
        For Each oWs In ThisWorkbook.Worksheets
            For Each oCell In oWs.UsedRange
                If oCell.Style = strOldSt Then
                    Application.GoTo oCell
                    On Error Resume Next
                    oCell.Style = strNewSt
                End If
            Next
        Next
    End If
End Sub


Comment by: Jan Karel Pieterse (27-8-2019 10:41:00) deeplink to this comment

Hi Thaddeus,

Thanks!


Comment by: PiecevCake (28-12-2019 23:48:00) deeplink to this comment

Hi Thaddeus,
Beginner users like me plagued with styles would hugely appreciate instructions how to use your code! )I tried pasting it in a module, returned "error-object required, tried to past in a form nothing happened?
Many thanks!


Comment by: Jan Karel Pieterse (6-1-2020 11:46:00) deeplink to this comment

Hi PiecevCake,

I've published your comment, but please note that the site does not motify previous commenters about your comment. This means it is not likely Thaddeus will respond to your request.


Comment by: Md. Ismail Hosen (19-1-2024 15:04:00) deeplink to this comment

Is there any way through vba to check if Number format checkbox is unchecked or checked?


Comment by: Jan Karel Pieterse (19-1-2024 16:13:00) deeplink to this comment

Hi Ismail,

Of course!

This returns True:

activeworkbook.Styles("Comma").IncludeNumber


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].