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