Add a new dimension to your model with conditional formatting.

By Anna Kim on April 29 2009

A recent project has reiterated to me how useful conditional formatting is in financial models, the amount of information that can be relayed through dynamic formatting and how it increases the efficiency of updating a model is really under-utilised and so I thought I might highlight when you could think about using standard and easy to use feature.

What is Conditional Formatting?

Conditional Formatting is an in-built function, which allows the user to format a cell a certain way, based on up to 3 criteria. Criteria can be controlled by either

the value of a cell
the result of a formula

It is an excellent way to visually represent what the underlying cell values mean, so that even a user with basic Excel skills can understand the model a lot quicker. Some examples might be

Unused areas of a model can be shown with gray formatting (just like in Windows)
Error alerts could use red formatting to strengthen the message.

6 reasons to use conditional formatting

Enhances the presentation of a model
Increases the user-friendliness of a model
Allows for a visual representational of the underlying numbers
Reduces the time it takes to understand a model by keeping the focus on the cells with significance
Quickly identify whether the model integrity is at risk
Creates a dynamic model

Here are some more great examples of when you could use conditional formatting

Binary (Timing) flags

Flags are used constantly in our models to simplify formulas. By shading the cells that equal 1 to a colour, it is quickly recognised when the flag is active. An example is when creating binary flags for the different phases in a project. The screenshot below shows 3 different phases, Construction, Delay & Operations, with each phase being shaded when there is a 1, to represent each active phase.

Idle sections in a model

Unused sections in a model can be found in multi-asset projects with not all assets active, or sections within the calculations that are not in the project phase. In these cases, grey formatting can be a powerful way of taking focus off the sections that are not used, shown in the screenshot below.

Identify cells that are unique

“Unique” in this case means cells that are different to a specificied value. For example, you may want to highlight assumptions that are not equal to the base case, or cells that are different to the expected value.

Highlight model integrity issues

Many models have cells that check whether certain areas of the model is correct, such as a balance sheet balances, or sources equal uses. These cells can be conditionally formatted to highlight as a warning when these checks are failing to ensure that issues can be fixed promptly.

There are some great web resources out there which cover this in more detail, Chip Pearson’s site in particular is worth a read if you want to learn more.