This is a really useful function in Excel where you can format cells dependent on their content. if you are new to this feature then this is the best place to start, once you master the basic conditional formatting in Excel you can really turn up the volumes with more fantastic stuff.

Let’s get the bascis done and dusted first.

What is Conditional Formatting?

This is the way you can tell Excel to format its cells if they meet certain criteria which you specify.

For example you may want to format any cells that have a value over over 100, and you want them to turn BOLD. When the cell meets the criteria of the specified format then the condition is applied, if it does not meet the criteria then the default formatting applies to the cells.

Why Use Conditional Formatting?

One reason- efficiency. you can always go ahead and manaully format all the cells you want to on your worksheet, why should you when you can et Excel to do it automatically with a few tweaks.

You can have up to 3 formatting conditions, each with it’s own formats, as well as the default value of no formatting. This allows you to have different formats depending on the value of a cell.

For example, if the value of a cells is greater than 100 then we want to display text in blue, but if the value is between 50 and 100 display the text in purple .

Let’s go ahead and try this.

To apply your conditional formatting, select the cell or range of cells and open the conditional formatting box from the FORMAT menu.

It is really important to put your conditions in the correct order for conditional formatting to work correctly. It is important to remember that conditions are not combined, when one of the conditions is met then that formatting is applied.

For example

1. Bold Text when the value is greater than 50
2. Red Text when the value is greater than 100
3. Grey Background when the value is greater than 150

In this case a cell A2 which contains 151 will be BOLD as the first condition has been met. So, you need to be a bit clever when you are ordering your formatting conditions.

1. Gray Background when the value is greater than 150
2. Red Text when the value is greater than 100
3. Bold Text when the value is greater than 50

By re-writing these conditions in the above order cell A2 with a value of 151 will have a GREY background.

As you can see conditional formatting is useful and does have it’s place. It is always good to bear in mind that if this formatting is applied across vast numbers of cells then it can affect Excels performance due to it being the equivalent of performing a formula in each cells it’s applied to.