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