Using Colours and Formats

Financial models are complex constructs with sometimes gigantic sheets. Using cell formatting within Excel helps you to keep an overview.

It is considered an industry best practice to pre-define cell formats and then use then for the different types of cells. In Excel, you can predefine easily. Just right-click on a cell to get to the Format Cells dialogue:

In the dialogue, you can set the number format (Number Formats), the alignment of the cell contents, the font style, cell borders, a background colour and cell protection:

It is recommended to define formats for the following general cases:

• Input cells: A typical and widely accepted format for this is a pale yellow background, sometimes with a blue font colour. Some practitioners like to add a dashed border to further increase user awareness of input cells.
• Calculated formulas in general: Most cells within your model will be calculated formulas. Therefore, you should stick to a simple formatting that is eye- and printer-friendly. Usually, this will be black on white - or sometimes blue on white, if input cells do not use a blue font already.
• Negative numbers: Typically, negative numbers as results of calculated formulas are represented in red. You can define this in the number format like this:
  #,##0.0_);[Red](#,##0.0);" - "_);@_)
  (A detailed explanation can be found in the article Number Formats.)
  
• Percentages: Commonly, percentages (both calculated and input) are put in italics.
• Sums: It is very common, not only in financial modelling, to represent sums in bold, sometimes with lines below and/or above the row.
• Checks: The checks included in your model should be highlighted in some form, especially when they indicate errors. The article "Excel: Using Checks" provides some more guidance on checks.

Of course, you can define more specific formats. For example, you could show historic and budgeted/forecast figures differently. However, you should be careful not to use too many formats in order to reduce complexity.