Question on your modeling practices…

2011-02-22
Ken Puls
Categories: Excel

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…”

Rob on February 22, 2011 at 9:48 pm said:

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
Ken Puls on February 22, 2011 at 10:30 pm said:

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)
Bob Phillips on February 23, 2011 at 1:03 am said:

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.
Andy Wiggins on February 23, 2011 at 1:50 am said:

I have been using my own "Style Guide" for some time.

Let me know how to post an extract (.xls format) here.

Andy
Adam Slim on February 23, 2011 at 2:30 am said:

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.
Squash on February 23, 2011 at 2:36 am said:

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.
Rob Gale on February 23, 2011 at 12:19 pm said:

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
Ken Puls on February 23, 2011 at 1:06 pm said:

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...
Ed Ferrero on February 23, 2011 at 7:24 pm said:

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
Ken Puls on February 24, 2011 at 10:03 pm said:

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...)
Ken Puls on February 24, 2011 at 10:05 pm said:

Ed, thanks for that as well! More considerations of what to add... 🙂
Ken Puls on February 24, 2011 at 10:58 pm said:

And here's a document that contains the most recent version of the styles that I have in place. (And yes, I re-did the external links one.)
Dave on March 9, 2011 at 3:16 am said:

>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)