Using Cell Styles for Financial Modelling

When developing a financial model, standards will often communicate the importance of developing a style guide to enhance the presentation and readability of a financial model for those who are external to its initial development. The use of a distinct style should help to enhance the presentation, simplify ongoing model audit, and add a level of consistency and professionalism to the overall build of the model.
Standardising styles will always be a difficult, as each model developer will have his/her own preferences with regards to the various style components. However, to engage in best practice, the most important thing is to be consistent within your model stakeholders, that is ensuring consistency in approach between those who are commonly affected, or can be affected by your financial modelling.
The SMART Financial Modelling Methodology, is one of the most widely recognised best practice financial modelling guides, and details a set of principles of presentation that include the following key elements:

  • Visually identify inputs
  • State dimensions and labels concisely
  • Develop customised style guides
  • Worksheets labelled, grouped and colour-coded
  • Include integrity checks
  • Use conditional formatting
  • Use data grouping and a clear heading hierarchy
  • Make good use of the screen with freeze panes
  • Use line summaries
  • Summarise output using plots

The role of presentation within the financial model building process should not be underestimated, as the most complex and well-designed models can lose their impact if they are difficult to interpret, present and audit. As indicated above, an integral part of the principles of presentation is the role of developing a customised style guide.
If the generic cell style set doesn’t suit your taste, you’re left with creating a custom cell style set, and in this article we look at simplifying the process of creating a customised financial modelling cell style set to improve the adoption of a standardised style by model developers. Currently Excel has one built in pre-defined set of ‘Data and Model’ cell styles shown in Figure 1 below:

Figure 1: The Cell Style Sets that are pre-existing within Excel.

It is generally considered good practice, when you are creating a model, to include a style guide whereby someone reviewing your model for the first time can use this sheet to develop an understanding of the flow of the model in terms of its components. To encourage the use of cell styles, the aim is to make the development and use of cell styles an efficient process whereby the application of styles to the financial model is not a burden. With this in mind, the example below, introduces an approach to developing custom financial modelling cell style set, which is easy to set-up and apply as the model is being constructed.

Step 1. – Develop a Style Template

By establishing a style template, this sets a clear guide as to the format of the model for anyone who is required to use it. Cell styles are also workbook specific, i.e. if you open a new workbook the custom cell styles will not be available, so if you create custom cell styles, make sure these are applied to the style template which can be copied across to any workbook to ensure the custom styles you have created will be available.
For the purpose of this exercise, the name you intend to call each cell style should be placed within the cell style you have chosen. Not only will this text function as the name of your cell style, but it also visually displays the text settings you have applied to each style.
In addition, each cell style name MUST be different to the default cell style names or it can’t be created.

Figure 2: Creating your Template

Step 2 – Setting up the VBA Macro

Once the template has been set up, the idea is to have a way to efficiently apply the newly created formats to the custom cell styles menu. The VBA macro that has been created automates and simplifies this otherwise manual process.
The VBA macro can be copied to your Personal Macro Workbook so that it is available at all times. If your Personal Macro Workbook does not yet exist, the easiest way to create it is to record a macro to it (Figure 3).

Figure 3: Creating a Personal Macro Workbook

Once the personal macro workbook has been created, we can add the macro to it by doing the following:

  • Alt-F11 (Open the Visual Basic Editor)
  • Click the + next to the VBAProject (PERSONAL.XLS)
  • Click the + next to Modules
  • Click on Module1
  • Click on the View Code icon at the top of the Project Explorer

Add the code below:

Sub customStyleAdd()

'Add custom styles to your workbook by setting up cell styles.
'Also calls customStyleDelete if the user wants to delete all
'preexisting custom cell styles.

    Dim cellStyles As Range
    Dim rngCell As Range
    Dim strMsg As String
    Dim answer As Variant

    strMsg = "The following cell styles have been created:" & vbCrLf

'Delete pre-existing custom cell styles

    answer = MsgBox _
        (Prompt:="Do you wish to delete existing custom cell styles?", _
         Buttons:=vbYesNo + vbQuestion, _
         Title:="Delete Existing Cell Styles?")

        If answer = vbYes Then
            Call customStyleDelete
        End If

    On Error Resume Next

'Select range (or use preselected range) as the source of the
'preformatted cell styles

    Set cellStyles = Application.InputBox _
        (Prompt:="Select the custom cell styles you wish to create.", _
        Title:="Cell Style Range", _
        Default:=Selection.Address, _
        Type:=8)

        If IsNull(cellStyles.FormulaArray) = False Then
        On Error GoTo 0
            MsgBox "Please ensure your selection contains:" _
            & vbCrLf & vbCrLf & _
            "- More than one cell" _
            & vbCrLf & vbCrLf & _
            "- Includes preformatted cell styles with labels"
            Exit Sub
        End If

        If cellStyles Is Nothing Then
            On Error GoTo 0
            MsgBox "No range selected."
            Exit Sub
        End If

    For Each rngCell In cellStyles

        If rngCell.Value = "" Then
            GoTo nextcell
        Else
            ActiveWorkbook.Styles.Add Name:=rngCell.Value, _
            BasedOn:=rngCell
            strMsg = strMsg + vbCrLf + vbCrLf + Chr(149) + _
            " " + rngCell.Value
        End If

nextcell:

    Next

    MsgBox strMsg

End Sub

Sub customStyleDelete()

'Deletes all preexisting custom cell styles.

Dim xStyle As Style

    For Each xStyle In ActiveWorkbook.Styles
        If xStyle.BuiltIn = False Then
            xStyle.Delete
        End If
    Next

End Sub

Figure 4: VBA Code that automatically applies the Custom Cell Styles

Step 4 – Running the Macro

Once the template and macro have been initially set up, we can run the macro which will create custom cell styles based on the formatting and names that you have used in your template. This can be done without the macro manually, however it has to be done for each style repeatedly, and the macro also provides the option to delete pre-existing custom cell styles if so desired.

To run the macro:
Click on the ‘Developer’ tab → ‘Macros’ → ‘PERSONAL.XLSB!customStyleAdd’ → ‘Run’.

Figure 5: Running the macro

You will be prompted to select whether you wish to delete the existing custom cell styles, and then more importantly to select the range of cells from which your Custom Cell Style Set will be generated. Cells with no label will be excluded as part of the Custom Set.

Figure 6: Selecting the cell style range.

Once the macro has been successfully run, we can go to Home → Styles → Cell Styles to view the newly created custom cell style set.

Figure 7: The custom cell style set.

Step 5 – Adding to the Quick Access Toolbar

The final step involves adding the ‘Cell Styles’ button to the quick access toolbar. If you click on ‘Cell Styles button’ to display the various styles and then subsequently right click towards the bottom of the open area you have the option to ‘Add to the Quick Access Toolbar’ (see Figure 8). By selecting this option we enable a shortcut icon to be place on the quick access toolbar which is always available to simplify the process of applying the customised cell styles during the model creation process.

Ultimately, the idea is to create a process whereby users can effectively facilitate the set-up of customised cell styles and improve the efficiency related to their application during the model creation process. The use of cell styles is crucial to presentation and model transparency, both of which are key components to widely accepted financial modelling guidelines worldwide.

Figure 8: Adding to the Quick Access Toolbar

Comments for “Using Cell Styles for Financial Modelling”

  1. Using ‘Styles’ in Excel is critical for professional formatting and we use them extensively in our SMART financial modelling methodology developed jointly with Navigator Project Finance:

    http://www.navigatorpf.com/smart

    My preference is to use the native styles in Excel but with the increased speed through the use of Corality’s Styles Shortcut add-in. The add-in brings back the exceptional speed of Excel 2003 Style to Excel 2007 (which without the add-in is pretty painful)

    http://www.corality.com/training/tutorials/excel-2007-styles-shortcut-tool

    Great article, keep them coming!

    Regards,
    Rickard
    Corality
    http://www.corality.com/

  2. Gerald Strever says:

    Very helpful article! Thank you, Damian

  3. Daniel Ferry says:

    Very nice article, Damian.

    You mention under Step 1 that the cell styles created by your routine must have names different from the default cell styles. But this is not the case. In VBA, the Add method of the workbook Styles collection will happily overwrite an existing cell style with the definition being added. If the name being specified for the new Style duplicates the name of an existing style (custom or built-in), the Add method cares not, and will replace the existing style.

    In most situations, one never needs the GoTo command in VBA, as it fosters spaghetti code. The code is more readable if you do this instead where you used the GoTo:

    For Each rngCell In cellStyles
    If Len(rngCell) Then
    ThisWorkbook.Styles.Add …
    End If
    Next

    Cell styles are additive between each other. You should provide an interface for the user to select which formatting categories a custom cell style applies to, i.e. Number Formats, Alignment, Font, Border, Fill, and Protection. Otherwise, the cell styles added by your procedure will by default include all six, thereby reducing their additive potential with other styles.

    I get it that this routine will likely be used on a relatively new (or brand new) workbook, but I’d like to point out that there are many workbooks out there that have suffered from Style Explosion. Some of these have thousands and even tens of thousands of cell styles defined.

    Your code fragment for removing existing styles will be slow in these circumstances. This is because Excel must internally rebuild the Style collection after each is deleted in your loop, and then navigate to the now correct index point in that collection for the For / Each loop to continue operating.

    It is much more efficient to use a For / Next loop and iterate BACKWARDS, like so:

    Public Sub DeleteCustomStyles(wb As Workbook)

    Dim i As Long
    Dim s As Style

    i = wb.Styles.Count
    For i = i To 1 Step -1
    Set s = wb.Styles(i)
    If Not s.BuiltIn Then
    s.Delete
    End If
    Next

    Set s = Nothing

    End Sub

  4. Fazil Gadzhiev says:

    Very interesting article! Many thanks, Damian!

    Fazil

  5. Oren Marnin says:

    I would also like to express my impression from this macro. I believe it will be very handy.

    One question though:
    To the best of my knowledge, the activation of macros disables to option of ‘undo’ in excel. However, I found that this ws not the case for this macro – after I activated it I was still able to undo actions I preformed previous to the activation.

    Any idea how this is so?

    Thank you,
    Oren

  6. Hi Oren,

    The use of macros does not always result in the Undo stack being deleted. For example, most Application level procedures do not effect the undo stack (Application.ScreenUpdating, Application.Calculate, etc). Data manipulation, or anything that may affect calculations, will wipe out the undo stack (copy paste, cell/sheet deletion, etc.). As it turns out; modifying, adding, or deleting is one of those codes that do not affect the undo stack.

    I don’t have, or know of any website that has a list of all macro that do and do not affect the undo stack. Hence, it’s always prudent to assume that that undo stack will be cleared every time you run a macro.

    Regards
    Bing

  7. Jon says:

    Very nice article Damian! Also learned a lot from Daniel’s comment. I’ve also developed an add-in that allows you to create keyboard shortcuts to apply cells styles and retain the full Undo History. It would probably work well in unison with your macro. After adding the styles to your workbook, you will be able to create keyboard shortcuts to apply those styles.

    http://www.excelcampus.com/keyboard-shortcuts/font-fill-color-style-excel-add-in/

  8. I’ll definitely read the whole article when I get the chance, although I’ve been using Excel Styles for quite some time, and I have no current reason to use my own custom styles.

    Regarding the use of Undo, perhaps Oren was discussing the problem that when you run a macro that makes changes to a cell or range of cells, those changes cannot be “undone” using Excel’s Undo feature.

Comment on this Article