Conditional formats is a wonderful feature that incrementally adds format to a cell based on some criterias. You could use it to highlight cells by adding a color fill, changing borders or modifying any other format element.
In it’s basic form, you can use it with a simple condition depending on the cell value. However, a more interesting yet underused way of working with conditional formats is with a formula condition. Let’s see how we can use both.
Conditionally format a cell based on its value
Let’s start with an example where a model user needs to input a product mix (e.g. overall sales to be broken down into the different categories). Obviously, the total mix should be 100%. The next picture shows how the model could be layed out.
I have added a conditonal format rule that will change total cells to red when the value is different than 100%. To do so, follow these steps:
- Select the cells on which you want “Conditional Formatting”. (In this case the cells with the totals)
- In the “Style” section of the “Home” tab, click “Conditional Formatting” then “New Rule…”. (In Excel 97-2003, you will find it in the format menu)
- Select “Use a formula to determine which cell to format”
- In the edit rule description, set it to cell value not equal to 1.
Conditionally format a cell based on a formula
Following on our previous example, suppose we want to highlight the input cells like in the picture below.
To add the conditional formatting, simply follow these steps:
- Select the cells on which you want “Conditional Formatting” (I.e.: the input cells).
- In the “Style” section of the “Home” tab, click “Conditional Formatting” then “New Rule…”. (In Excel 97-2003, you will find it in the format menu)
- Select “Use a formula to determine which cells to format”.
- Type the formula to be evaluated in the “Format values where this formula is true:” section. In our example, the formula looks like this: =SUM(E$23:E$26)<>1
Conditional Format gives you a quick glance to verify the inputs and might even highlight errors that would have normally been missed. As an example, look closely at the second picture, you’ll notice that the total seems to be 100% when in fact it’s not.
Note that, in some instance, you might want to add rounding to your conditions when your totals are close but not quite equal to 100%.
You can download the companion file used for the examples here: Conditional Formats - The Warm-Up
The Financial Modeler
Conditional Formats Control – The Warm-Up
In it’s basic form, you can use it with a simple condition depending on the cell value. However, a more interesting yet underused way of working with conditional formats is with a formula condition. Let’s see how we can use both.
Conditionally format a cell based on its value
Let’s start with an example where a model user needs to input a product mix (e.g. overall sales to be broken down into the different categories). Obviously, the total mix should be 100%. The next picture shows how the model could be layed out.
I have added a conditonal format rule that will change total cells to red when the value is different than 100%. To do so, follow these steps:
Conditionally format a cell based on a formula
Following on our previous example, suppose we want to highlight the input cells like in the picture below.
To add the conditional formatting, simply follow these steps:
Conditional Format gives you a quick glance to verify the inputs and might even highlight errors that would have normally been missed. As an example, look closely at the second picture, you’ll notice that the total seems to be 100% when in fact it’s not.
Note that, in some instance, you might want to add rounding to your conditions when your totals are close but not quite equal to 100%.
You can download the companion file used for the examples here: Conditional Formats - The Warm-Up