Conditional formatting

Conditional formatting is useful for highlighting certain results in an Excel financial model.  For example, breaches in financial ratios or bank covenants.  If a financial ratio in the model is breached, then Excel formatting automatically changes so that the key result is highlighted in a bright colour.  The instructions below will get you started if you have one of the later versions of Excel (2007 and beyond).

An example: conditional formatting in Excel

In this example we’re going to modify our nested If function example, imagining that if a key financial ratio (e.g. Debt: EBITDA) is > 3.0, then a bank covenant is breached, and we want the result at cell B7 highlighted in red.

Balancing scorecard with nested If formula

Accessing and creating conditional formatting in your financial model

From the “Home” tab, under “Styles” select “Conditional Formatting”.  To get yourself started, select “New Rule”, and the “New Formatting Rule” box pops up.  Select the second item: “Format only cells that contain”.

Financial modelling & conditional formatting: setting the condition

Next set the condition that triggers the covenant breach.  Under the “Format only cells with” section, select “Cell Value” “equal to”.  In the next box type this text: =”Code Red”.

Conditional formatting: set the format

Next, set the format that applies should the breach occur.  Towards the bottom of the “New Formatting Rule” box, click on the “Format” button.  Should the breach occur, we’re going to highlight the background of the result cell bright red, with bold white text.  After you have clicked on the “Format” button, on the “Fill” tab, select the colour red.  On the “Font” tab, select a white and bold font.  Click on “OK”.  The format you have selected is now previewed in the bottom of the “New Formatting Rule” box.  Click on “OK”.

Image showing how to apply conditional formatting in Excel financial modelling

Type a high value (e.g. 3.5) into cell B5, and formatting appears red. Type a low value (e.g. 1.0) into the cell.  The condition is no longer triggered, and the red formatting disappears.

Financial modelling course tip: why white text on red fill?

People often photocopy and print financial models in black and white.  You still want your breach to be obvious even if the model has not been printed in colour.  White text on a bright fill colour will still show up (as a big block of black) if the model is not printed in colour.  This makes the breach even more obvious in a black and white financial modelling print out.

Financial modelling course challenge

Here is a harder conditional formatting course challenge: can you add multiple conditional formats to implement the green, amber, red traffic light system we were talking about when we looked at If functions? Want to see how it’s done?  Have a look at the download: conditional formatting challenges spreadsheet.  Click on cell B7.  Click on “Home”, “Styles”, “Conditional Formatting”, “Manage Rules” to see the formatting applied.

Applying Excel's conditional formats

Financial modelling course tips for conditional formatting

Here are our tips for conditional formatting:

  • Use bright solid colours and white bold text.  This will ensure that the formatting is as obvious and visible as possible.
  • Get key breach or error results returning text messages e.g. “breach” or “error” or “code red” in a line in the financial model, so that they are completely obvious, including when the Excel spreadsheet is printed out in black and white.
  • Build your logic into the spreadsheet where it is visible (= the If formula in cell B7 in our example), with the key inputs changeable (cells B3 and B4). The alternative is to ‘hide’ the logic in the background of the conditional formatting (you can build conditionality in where it says “Format only cells with:”). The first alternative makes your logic more clearly visible and easier to modify.
    Building logic tests into Excel conditional formatting

What’s next in the Excel financial modelling course extract?

In the next lesson we look at how to structure scenarios in financial modelling using Excel’s Choose function.

About this online financial modelling course material

You’re looking at a free online financial modelling course extract covering Excel functions.



1 comment

...join the conversation...

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

  1. Very good stuff