Question on your modeling practices…

So I've been working on a massive model over the past year, and recently recorded a webcast on modeling practices. In the course I cover some of my philosophies for making sure that the model lends itself to attracting valid data, namely I colour all input cells green and tell my users that green means go. This is a practice that I've followed for many years, and it's served us very well at work.

When working on my model though, just coding the input cells isn't enough. I need to enforce different looks for different cell types, and for this I reached to styles to help me manage consistency across the workbook. My custom styles gallery looks like this:

All the DE_ styles are data entry cells. The Head_ styles are for headers, and the SYS_ styles are for formulas and text that I don't want the users to change.

For a long time this worked out really well for me, but then I had to implement a change… I had to link to data in an external file. I try not to do that, and I need to be able to see this data in my model, but I didn't have a style to do this. Excel 2010's gallery has some default styles shown here:

As an accountant, I just can't have a block of Linked Cells with double underlines all the way through. Double underlines are for totals, not accents. So I ended up building my own style for this too:

I think it's equally ugly, but it does draw your attention to the fact that they're pretty dangerous.

I'm just curious as to what conventions you use. Do you highlight links in any way? Do you draw attention to internal links (to other sheets) or only external? What other things do you try to draw attention to?

13 thoughts on “Question on your modeling practices…

  1. I used to use blue text to indicate user entry cells, but the new version (2007) removed the nice blue I used to use and I don't think any of the new colours work as well. I like the idea of the light green though, so I'll try that.

    I'd avoid the pink/red combination, though...looks more like an error than just an external link.

    Do you have a sample worksheet you could show that contains all of the various highlights? I'm curious if it looks usable or just too busy.

    Rob

  2. Hi Rob,

    Thanks for your comment. I don't have a sample at the moment, but I could probably mock one up. In some respects it could probably be argued that I might have some overkill on the formats, but the ones above are the most common that I use.

    Re the links one, I agree. I'm not a big fan of the pink/red combo. I just tried orange/brown, and that looks a little more digestable. May still have to play with others.

    Re the green I'm using, it's actually the green from Excel 2003 and earlier. I'm not I big fan of the new ones either. This particular one is:
    -ColorIndex 35 (if you know VBA)
    -In Fill --> More Colors it's the hex one position left of dead centre
    -The RGB value is 204, 255, 204 (go to fill, click more colours, custom)

  3. Rob,

    External links ... they are errors!

    If you are like me, the blue that you like in Excel 2003 is Colorindex 37, which has an RGB value of 153, 204, 255, or you can set in VBA with

    ActiveCell.Interior.ColorIndex = 37
    or

    ActiveCell.Interior.Color = RGB(&H99, &HCC, &HFF)

    and then use that colour elsewhere.

  4. We use styles extensively throughout the model, for two purposes:
    1 - to clarify the 'intent' of a cell
    2 - to highlight the dataflow within the model

    So our styles include the following:
    Input - cells designed for user input
    WorkbookLink - pulling figures from another workbook
    Check - integrity check
    Flag - logical flag

    For dataflow we use a broadly modular system, in which all of the 'inputs' to a calculation section are in a From style, the intermediate calculations in a Calculation style, and the results in a To style. This means that From cells should only have precedents that are To, Input or WorkbookLink, Calculation cells should have no dependents outside the module, and To cells will have dependents. calculation and To will also only have precedents within the module.

    Each of these headline styles will be appended with the number style, so there will be InputCurrency, InputPercent etc.

    We use Output styles to allow easy formatting of the outputs, so you can easily change the outputs from £'000 to £m by altering the OutputCurrency style, for example.

    There are Heading styles that are used to separate the modules, and these also drive a simple UDF that automatically numbers the headings.

    Finally there are a few other sundry styles, such as various totals, explanatory text, label, range name label, macro pasted cells and so on.

    The styles are listed on a worksheet, which is used to programatically create or amend the styles. This makes it nice and easy to change the colours and formats as well.

    We've only just started to introduce Excel 2010 (previously 2003), so I think we'll need to look at how it works in 2010 - as things stand I am tempted to just get rid of the terrible default styles, but we'll see.

  5. In my personal add-in, I currently run a macro that automatically colors all inputs blue, internal links green, external links pink, and #REF! errors red.

    I am eventually going to migrate this macro to update the style scheme in a workbook to reflect this formatting (so it will presumably be saved in the workbook). Then, when I want to print or paste in powerpoint, I will run and additional macro that changes the style scheme to be simply black text for each kind of cell. When I'm finished, another macro will convert the styles back to the colors.

  6. Ken,

    Funny...I've just been suffering with the 'new' colours. I should have clicked 'More Colors...'. I see the green you are using...much better than 2007's greens.

    Would the colour appear unchanged if opened in 2003?

    Bob Phillips-- that isn't the blue I was thinking of...more like 0,0,255. A real bright blue for text.

    Rob

  7. Rob... I think it should go okay back to 2003, but I haven't tested it. The way Excel's colour mapping works, I wouldn't necessarily guarantee it though...

  8. Thanks for this post, I am working on some big models myself at the moment, and this is very helpful.

    For some reason, I've always used light yellow for input cells. I may have to change to green...

    Something that I often use on large models is a sheet that documents named ranges. If I need real name power I go here http://www.jkp-ads.com/officemarketplacenm-en.asp. But I usually just need to see a simple list, so I add a blank sheet and add this code to the sheet.

    Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    ' make sure the ListNames is up to date
    Me.UsedRange.ClearContents
    ' add a list of all the range mnames in the workbook
    Me.Range("A3").ListNames

    ' have an up to date list of Sheet Names in a drop down
    BuildSheetNamesValidation
    Application.ScreenUpdating = True
    End Sub

    Private Sub BuildSheetNamesValidation()
    ' builds a delimited list of all sheets in the workbook
    ' puts it in the data validation list at range("SheetNames")
    Dim sht As Worksheet
    Dim strArr As String
    Dim nrSheets As Excel.Name
    Dim oFC As FormatCondition

    For Each sht In ActiveWorkbook.Worksheets
    strArr = strArr & sht.Name & ","
    Next

    ' get rid of last comma
    strArr = Left(strArr, Len(strArr) - 1)

    ' make sure we have a SheetNames range
    ActiveWorkbook.Names.Add Name:="SheetNames", RefersToR1C1:="=RangeNames!R1C3"
    ' add a description
    Me.Range("A1").Value = "Range Names"
    Me.Range("B1").Value = "Choose a sheet name to see the ranges referring to that sheet"

    With Range("SheetNames").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=strArr
    .ShowInput = True
    .ShowError = True
    End With

    ' put a value in the SheetNames range
    Range("SheetNames").Value = Left(strArr, InStr(1, strArr, ",") - 1)

    ' get rid of any existing conditional formats
    Cells.FormatConditions.Delete

    ' add some conditional formatting to help find range names
    With ActiveSheet.UsedRange.Offset(0, 1).Resize(ActiveSheet.UsedRange.Rows.count, 2)
    ' set fill background if range name definition contains selected sheet name
    Set oFC = .FormatConditions.Add(Type:=xlExpression, Formula1:="=ISNUMBER(SEARCH($C$1,B1,1))")
    oFC.SetFirstPriority
    With oFC.Interior
    .ColorIndex = 42
    .PatternColorIndex = xlAutomatic
    End With

    ' set fill background if range name definition contains #REF error
    Set oFC = .FormatConditions.Add(Type:=xlExpression, Formula1:="=ISNUMBER(SEARCH(""#REF"",B1,1))")
    With oFC.Interior
    .Color = 5296274
    .PatternColorIndex = xlAutomatic
    End With
    End With
    End Sub

  9. Andy Wiggins has sent me the following file that shows the styles he uses

    It's interesting to me that, while the colour schemes are different, we seem to have some similar thoughts on what to set up. (Data entry for dates, numbers, percent, etc...)

  10. >Do you draw attention to internal links (to other sheets) or only external?

    Yep. Our work modelling practices distinguish amongst other thing cell types
    - inputs
    - formulae
    - sheet links
    - workbook links

    For anyone interested, my Mappit! addin in my name link identifes these cells, plus potential inconsistencies. I use the same code in a second addin to format actual live files rather than map them

    Cheers

    Dave (brettdj)

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *