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:

Excel: Format Cells

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:

Excel: Tabs in the "Format Cells" Dialogue

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.

One Reply to “Using Colours and Formats”

  1. I have found it useful to define colours and designs globally - preferably on a company level, but at least at the beginning of a project for all team members to agree upon.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.