Conditional Formatting

Have you ever tried to change formatting of a cell or a range of cells based on a condition? If so, conditional formatting is the tool you need to use.

Conditional formatting is one of the most loved features of excel. It allows users to format cells and/or ranges depending on one or more conditions. And there is a truck load of preset conditions for users who doesn’t need complex conditions for their conditional formatting needs. Formatting based on cell value or contents, based on another cells value, top1o values, duplicates are some of the examples of these preset values.

If those preset conditions doesn’t cover your needs, you can manually create a new rule with a formula or whatever you need. You’ll be presented with a formula bar in this case and you can specify your condition manually from here.

Since using preset conditions for conditional formatting is pretty self explanatory, I will give an example for conditional formatting based on a formula:

Lest assume that you are tracking sales for your customers and you want to highlight any monthly sales figure that is below 75% of average. See example data below:

To highlight sales that are less than 75% of average, we need to write a condition. Select cell C3 and open conditional formatting menu from ribbon. Click on new rule and select “use formula to determine which cells to format” option. Write following formula in formula space:

Hit enter and customize your formatting. When you hit ok, you will see nothing is changed. That is because cell C3 is not less than 75.000. Now select cell C3 and copy this cell. Select cells thorough C3 to E5 and paste special (paste formatting – little brush icon in paste menu).  Alternatively, you can go to conditional formatting menu and select “manage rules” option. From this window, you can specify which cells will be effected by this conditional formatting rule.

After you apply your rule, you will see that only C4 cell which meets given condition will be re-formatted. Rest of the cells will stay same. And if you manually decrease any cell below 75% of related average value, you will see its color also changes. Go ahead and try it…