How to use conditional formatting in Excel

Posted on January 28, 2009 at 5:35 am

Conditional formatting is a very nice functionality in Excel wherein you can use up to three rules to automatically set a format of a cell. If you have cells in Excel that you want to be color coded or shaded depending on the rules you set then this is post is for you.

For the example below I will use below sheet with 2 columns:

To use conditional formatting on Column B labeled “PERCENTAGE” just click the column “B” to highlight the entire column:

Then click Format > Conditional Formatting on the menu:

Enter the rules on the Conditional Formatting dialog window:

On the example above I choose to create a rule for cell values less than 80. Click “Format…” to set the format of this rule. In the example below I chose to use the Pattern tab to set the cell shading to red:

You may also choose to set formats using fonts and borders:

Click OK to go back to the Conditional Formatting window. You should see the sample preview of the format you set for this rule:

Click OK to see the actual rule in action:

As you can see all of the values less than 80 are filled with red. We can add up to three rules by using the Add button. Deleting a rule can be done by the delete button: